1

I've two documents: doc1.lst and doc 2.lst I want to take the content of each line and put it as parameters for my SQL query.

I tried something like this, please correct me

    file=doc1.lst
    while read line
    do
       p1=$line;

    file=doc2.lst
    while read line
    do
       p2=$line;

        sqlplus64 $User/$Pass@$ORACLE_SID << EOF2
        @update.sql p1 p2

    done < echo "Ok"
    done < echo "Ok"
    EOF2

The thing is that I want to take the value of each line and put it as a parameter (p1 and p2) to be able to update my table as seen in the sqlplus query.

For a better understanding my file doc1.lst looks like :

    AAA
    ABC
    EDF

And my file doc2.lst :

    30
    10
    30

I want to take those values to update my table.

2

As I understand it (the <<EOF2 stuff at the end isn't crystal-clear), the end result you're after is to feed the following into sqlplus64:

@update.sql AAA 30
@update.sql ABC 10
@update.sql EDF 30

To produce this, instead of looping over the contents of both files, you can combine them. Using paste on both files (paste doc1.lst doc2.lst) gives

AAA     30
ABC     10
EDF     30

(paste joins with tabs by default). Changing the delimiter with paste -d ' ' doc1.lst doc2.lst gives

AAA 30
ABC 10
EDF 30

Then we need to add @update.sql as a prefix; this can be done with sed, replacing the start of each line (^) with the prefix:

paste -d ' ' doc1.lst doc2.lst | sed 's/^/@update.sql /'

produces the desired result.

This can then be fed in one shot into sqlplus64:

paste -d ' ' doc1.lst doc2.lst | sed 's/^/@update.sql /' | sqlplus64 $User/$Pass@$ORACLE_SID

If you need exit at the end of the script fed into sqlplus64:

(paste -d ' ' doc1.lst doc2.lst | sed 's/^/@update.sql /'; echo exit) | sqlplus64 $User/$Pass@$ORACLE_SID

If you really want to run things line by line, you can while read each line of the result and feed that to sqlplus64 instead.

  • Could you edit your post and explain what you did please, this is so much different that what I thought, some further explanations would be great. – Julien S Mar 11 '15 at 14:48
  • By the way i already have a list with the two documents merded. But i wanted to seperate them, to be able to use each value as a parameter. – Julien S Mar 11 '15 at 14:51
  • 1
    You're just manipulating text to feed into SQL*Plus, so it shouldn't be necessary to split the parameters. I've added some more detail to my answer. – Stephen Kitt Mar 11 '15 at 14:56
  • The thing that I don't get is that the table where I want to make the update contains ~ one hundred of columns, how does it go in the right column In my sql query? – Julien S Mar 12 '15 at 7:32
  • What does update.sql contain? In SQL*Plus scripts you can use &1, &2 and so on to use parameters as above. This is getting rather off-topic for Unix.SE though! – Stephen Kitt Mar 12 '15 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.