Multiple Query Results to Variables

Shell Programming and Scripting
1

Hello,
I am very new to shell scripting and I am not sure of how best to handle the following scenario. I need to query a list of values from a table. I need to store those results and use them to selectively delete values in yet another table in a separate database. I do know how to store the result of one value in a variable; however I am not sure if it is best to store multiple results in an array or to a variable and then parse the variable? Any guidance would be appreciative.
thanks

2

You can spool the field values (comma separated) to a file and read it one by one to perform an action:-

sqlplus -s $user@$pass@inst << EOF
set echo off head off feed off pagesize 0 trimspool on linesize 1000 num 15 colsep ,
spool data.csv
select Field_1, Field_2 from table_1;
spool off;
exit;
EOF

while IFS=, read val_1 val_2
do
        sqlplus -s $user@$pass@inst << EOF
        delete from table_2 where Field_1='$val_1' and Field_2='$val_2';
        commit;
        exit;
        EOF
done < data.csv
3

Thank you very much for your reply. Can you tell me if there is a way to do this dynamically without creating a new object such as a file?

4

Here are other options which you can follow:-

1. Export (exp) & Import (imp) table from 1st DB to 2nd DB and join them to perform your task.
2. Spool all the values from table, use SQL Loader (sqlldr) to load them into a table in 2nd DB and join them to perform your task.