UNIX for Dummies Questions & Answers

Hi,

I am calling an oracle function that returns a number (either 0 or 2), how do I pass that pass to the wrapping shell script as I would like to do other things based on the value returned by the oracle function.

Your help will be appreciated.

--------------------------
sqlplus / << ENDPROG
declare
return_code integer;
begin
return_code := kf_pkg_load.df_validate_file('SAS','20050612',null,null,null,null,null,null,null,null);
commit;
end;
/
exit;
ENDPROG
---------------------------

Assign a variable to the sqlplus call. I belive you do not need to assign the
return code.

Code:

result=sqlplus / << ENDPROG
declare
return_code integer;
begin
return_code := kf_pkg_load.df_validate_file('SAS','20050612',null,null,null,null,null,null,null,null);
commit;                         
end;
/

This is how I would do it:

Code:

part_info=`sqlplus -s ${DB_USER:}/${DB_PASSWORD:}@${ORACLE_SID} <<_EOF_

      set feedback off
      set linesize 100
      whenever sqlerror exit failure;
      set lines 150
      set pages 0
      set head off
      set serveroutput on size 10000
  
      kf_pkg_load.df_validate_file 
      'SAS','20050612',null,null,null,null,null,null,null,null);
                    
      exit;
      _EOF_`

Then I evaluate the variable to get the results or see if an error occured. You should see what the df_validate_file returns or an error if one occured.

Hope this helps

Thanks VIGSGB for responding, I tried to run this (I had to make some changes to make it run):

----------------------------------
part_info=`sqlplus sars2d/oracle@kfdmlc <<_EOF_
set serveroutput on size 10000
declare
abc integer;
begin
abc:= kf_pkg_load.df_validate_file(
'SAS','20050612',null,null,null,null,null,null,null,null);
dbms_output.put_line('return code is:'||abc) ;
end;
/
exit;
_EOF_`
------------------------------

However, when I do an echo on part_info, it returns this:

------------------------------------
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jul 11 12:50:24 2005 Copyright (
c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i E
nterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning opt
ion JServer Release 9.2.0.1.0 - Production SQL> SQL> 2 3 4 5 6 7 8 start date is
: current load instance id is : last load instance id is : 122 return code is:0
PL/SQL procedure successfully completed. SQL> Disconnected from Oracle9i Enterpr
ise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning option JS
erver Release 9.2.0.1.0 - Production
-----------------------------------

What am I doing wrong?

Thanks in advance.

JTrinh

You need to run sqlplus with the "-S" parameter to keep the sqlplus version text from muddying up your process.