2

I've an error that I don't understand, it seems that that the problem is that my parameters into the shell are not of the same type as what they are in my SQL Database.

For a better understanding: I've a file doc.lst where i've three important things on each line seperated by spaces and i want to use this things as parameter for an UPDATE in my database. Every line will be readen, every part will be used as a parameter and will update my database.

I've a SQL query that is :

    UPDATE PE P
    SET P.DATED='&1', P.DATEF='&1', P.CODA='&3', P.CODB='&2'
    WHERE P.G_BAT=1;
    COMMIT;

And a shell that is :

    IFS=$'\n'
    for i in $(cat $LST_HOME/doc.lst)
    do 
    sqlplus64 $User/$Pass@$ORACLE_SID << EOF
    P1=$(echo $i | awk '{print $1}')
    P2=$(echo $i | awk '{print $2}')
    P3=$(echo $i | awk '{print $3}')
    @$SQL_HOME/update_p.sql $P1 $P2 $P3
    commit;
    exit
    /data/jdk1.6.0_XX/bin/java -cp fr.bla.bla.bla 
    EOF

I've got this error :

    SQL> SP2-0734: unknown command at the beggining of "P1=12/03/2..." - end of the line is ignored.
    SQL> SP2-0042: unknown command "P2=0" - end of the line is ignored..
    SQL> SP2-0042: unknown command "P3=FSL" - end of the line is ignored..

I don't really understand where and how i've to make modifications.

| improve this question | | | | |
  • You need to do set -f first before doing all of that $IFS splitting. Anyone of those vars could expand to shell globs. – mikeserv Mar 18 '15 at 11:15
3

As indicated in Shell : while read line nested, you can just do something like

(sed "s|^|@${SQL_HOME}/update_p.sql |" ${LST_HOME}/doc.lst; echo exit) | sqlplus64 ${UserCoribudg}/${PassCoribudg}@${ORACLE_SID}

If you want to run a command after every SQL update, you could loop, although there are a number of caveats (globbing etc.). If the values in your doc.lst are safe, you can do

while read line; do
    (echo @${SQL_HOME}/update_p.sql ${line}; echo exit) | sqlplus64 ${UserCoribudg}/${PassCoribudg}@${ORACLE_SID}
    /data/java_1.6_XX/bin/java -cp fr.bla.bla.bla
done < ${LST_HOME}/doc.lst

without needing to parse each line.

A safer option is to turn the whole file into a shell script:

sed "s|^|(echo @${SQL_HOME}/update_p.sql |" ${LST_HOME}/doc.lst | sed "s/$/; echo exit | sqlplus64/" | sed "s|$| ${UserCoribudg}/${PassCoribudg}@${ORACLE_SID}; /data/java_1.6_XX/bin/java -cp fr.bla.bla.bla|" > doc.script
sh doc.script
| improve this answer | | | | |
  • The solution with the while gives me "SQL> SP2-0734: command not found at the beggining of "/data/to..." (this is my $SQL_HOME) - the end of the line is ignored". But it starts my .xml generation. – Julien S Mar 18 '15 at 15:27
  • Oops, sorry, the @ was in the wrong place. – Stephen Kitt Mar 18 '15 at 15:29
4

setting of shell variable should be made before sqlplus call

IFS=$'\n'
for i in $(cat $LST_HOME/doc.lst)
do 
P1=$(echo $i | awk '{print $1}')
P2=$(echo $i | awk '{print $2}')
P3=$(echo $i | awk '{print $3}')

sqlplus64 $UserCoribudg/$PassCoribudg@$ORACLE_SID << EOF
@$SQL_HOME/update_p.sql $P1 $P2 $P3
commit;
exit
EOF

/data/java_1.6_XX/bin/java -cp fr.bla.bla.bla

done
  • P1=whatever is not a valid sql code.
  • I assume java part make use of updated values.
| improve this answer | | | | |
  • Thank's for the answer, but the problem is that at the end of every line i've to update my table with the datas of the line. I've a java command that creates a .xml that's why I wanted to use a while. At the moment it only update in the data base the last line. – Julien S Mar 18 '15 at 10:25
  • 1
    That's because your WHERE condition is probably wrong; you're applying all the updates to the same lines, so only the last update is kept. – Stephen Kitt Mar 18 '15 at 10:35
  • As you probably have understood, I don't master awk or sed command. So I don't really know how to do because since those command are searching something this is a kind of loop. Can I use wc -l < doc.lst and use the result as a variable (called j), set i to 0 and to do something like while i < j Archemar's answer my command to generate xml i++ ? It sound impossible to me because even if something like this would work with Archemar's solution it will make all the updates and then use the command to generate. So it'll be every time the last line that is uploaded. – Julien S Mar 18 '15 at 11:08
  • Edit your question to show where xml file is generated, however, the WHERE condition seems strange, your basically doing set month=january where year=2015 ; set month=february where year=2015 ; ... set month=december where year=2015 ; the first 11 statement are useless ... – Archemar Mar 18 '15 at 11:55
  • I've edited my question – Julien S Mar 18 '15 at 14:27

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.