Hi Tom
I am trying to get the f_name value to FILE_VAR.
My proc is having 2nd and 3rd parameter as OUT parameter.
I want to take its "f_name" variable's value to Shell script variable "FILE_VAR"
I tried below but its not working.
FILE_VAR="test"
runforfile()
{
echo -n "Select 1 for Interim FX and 2 for Final FX :"
read File_Optn
}
#/* Log into ORACLE to execute command */
runSql()
{
FILE_VAR=`sqlplus -s ufad00/$ORA_UFAD00@$INSTANCE << EOF | tee -a ${CurrentLogFile}
set serveroutput on
declare
exception_str VARCHAR2(32767);
f_name varchar2(20);
begin
IF ($File_Optn > 2 OR $File_Optn < 1) THEN
dbms_output.put_line('Entered value $File_Optn is incorrect option.');
return;
END IF;
UFAD00.sp_IFX_ForexData($File_Optn, f_name, exception_str);
dbms_output.put_line(f_name);
dbms_output.put_line(exception_str);
end;
/
EOF`
}
Thanks in advance for your help.
Here's an example of a simple ksh script which captures all output and then greps accorrdingly, eg
[oracle@vbgeneric ~]$ cat x
#!/bin/ksh
print "
set serveroutput on
set feedback off
begin
if '$1' = 'ok' then
dbms_output.put_line('RESULT');
else
raise_application_error(-20000,'Aggghhhhh');
end if;
end;
/
" | sqlplus -s mcdonac/alicat1 1>/tmp/output 2>&1
if [ "`grep 'ORA-' /tmp/output`" ] ; then
echo SOMETHING WENT WRONG:
cat /tmp/output
else
FVAR=`cat /tmp/output`
echo "FVAR=$FVAR"
fi
[oracle@vbgeneric ~]$ ./x ok
FVAR=RESULT
[oracle@vbgeneric ~]$ ./x bad
SOMETHING WENT WRONG:
begin
*
ERROR at line 1:
ORA-20000: Aggghhhhh
ORA-06512: at line 5