using the script below I want to pass a parameters thorugh my sql
call(@/unixsxxx/xxxx/helpenv.sql emptab ) as input into an oracle procedure xxxx_package.proc1(%1,emptab); . I tried %1 but it does not work. Any suggestions.
#!bin/ksh
set -x
#
export ORACLE_HOME=/xxxx/apps/oracle/dist/10.2.0
export ORACLE_SID=xxxxx
export ORA_USERNAME=xxxxxx
export ORA_PASSWORD=xxxxxxxx
export logfileTMP=/xxxxxxx/xxx/xxxxx/lewis.log
export TNS_ADMIN=/usr/local/bin/oracle
export PATH=$PATH:$ORACLE_HOME/bin
export emptab=abcde
sqlplus -S ${ORA_USERNAME}\/${ORA_PASSWORD}@${ORACLE_SID} <<-!2>/dev/null 1>>$logfileTMP
@/unixsxxx/xxxx/helpenv.sql emptab
exit
/
helpenv.sql
set serveroutput on size 1000000
SET ECHO on;
declare
type rcursor is ref cursor;
emptab rcursor;
emprec PS_xxxx_ENV_VARS%rowtype;
begin
xxxx_package.proc1(%1,emptab);
loop
fetch emptab into emprec;
exit when emptab%notfound;
IF emprec.COL_WIDTH = 2 then
dbms_output.put_line('export' || emprec.LABEL_ID || '= ' || emprec.STRING_TEXT );
else
dbms_output.put_line('export' || emprec.LABEL_ID || '= ' || emprec.STRING_TEXT || emprec.STRING_ID );
end if;
end loop;
end;
/