What is the best way to retrieve muliple variables back to a unix script from a record in oracle?? I'm using the Korn Shell on AIX.
You'll have to experiment a bit but in general,
the form would be...
#!/bin/ksh
ORACLE_CONNECT_STRING=user/password@schema
numofrows=`sqlplus -s $ORACLE_CONNECT_STRING << EOF
set heading off
set verify off
set feedback off
set serveroutput on
declare cnt number;
begin
select count(*) into cnt from SOME_TABLE;
dbms_output.put(to_char(cnt));
end;
/
EOF`
echo "Number of rows: $numofrows"
exit 0
...again, I haven't actually tried this example but the sqlplus
part does work. I just am not quite sure if the variable assignemnt
will work this way. You might have to send the output to a file
then "read" the file into a list of variables (assuming more than
one column is selected.