Settembre 19th, 2017 
Nicola Tuccella Shell Unix – Script Sql PLSql / Argomenti Variabili da Shell Unix a Script Sql (e viceversa)
–> Script Testati su Oracle 11g <–
Metodo 01) Richiamo Script sql da shell Unix:
–>Shell
#!/bin/ksh
sqlplus -S /nolog <<EOF
connect $USER/$PASSWD@$DB
set echo on timing on time on term on;
set serveroutput on;
set serveroutput on;
spool $FILE_TMP;
WHENEVER SQLERROR EXIT 99
@$PIPPO.SQL;
spool off
EXIT
EOF
EOF
#–>Get Ritorno da Shell
rit=$?
–>Alternativa..
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
SET DEFINE OFF;
statement sql…
COMMIT;
SET DEFINE OFF;
statement sql…
COMMIT;
QUIT;
Metodo 02) Passaggio Argomenti allo script sql e passaggio risultato dello Script sql alla shell Unix:
#!/bin/ksh
sqlplus -s /NOLOG << EOF >> ${LOG_FILE}
SET FEEDBACK ON;
SET TERMOUT ON;
SET SERVEROUTPUT ON;
WHENEVER OSERROR EXIT SQLCODE;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
CONNECT ${USER}/${PWD}@${DB_NAME[
PROMPT FILE IN ESECUZIONE: ${PROC}
@${PROC} ${ArgomentoPerScriptSQL1} ${ArgomentoPerScriptSQL1} ${ArgomentoPerScriptSQL1}
— Gli argomenti nello script SQL andranno gestiti con la &1-&2-&3
PROMPT SCRIPT ${PROC} Run OK
COMMIT ;
EXIT success;
/
EOF
EXIT success;
/
EOF
#–>Get Ritorno da Shell
Ret=${?}
Metodo 03) Passare alla shell un risultato della query
#!/bin/ksh
x=`sqlplus -s U_SIE/SIE@RGSSVIL_RAC.ETLS <<EOF
select DUMMY from dual;
exit
EOF`
echo RISULTATO $x
select DUMMY from dual;
exit
EOF`
echo RISULTATO $x
Metodo 04)
–File 1 pr3.ksh
#!/bin/ksh
sqlplus -s U_SIE/SIE@RGSSVIL_RAC.ETLS <<EOF
@pr3.sql
EOF
retcode=$?echo “retcode is ${retcode}”
@pr3.sql
EOF
retcode=$?echo “retcode is ${retcode}”
–Opzione 1 File 2 pr3.sql
set heading off
set feed off
set pagesize 0
set head off
set term off
set VERIFY OFF
select count(*) into :ret_val from dual;
exit :ret_val;
–Opzione 2 File 2 pr3.sql
VARIABLE ret_val NUMBER
begin
select count(*) into :ret_val from dual;
end;
/
exit :ret_val;
begin
select count(*) into :ret_val from dual;
end;
/
exit :ret_val;
Metodo 5) Gestire il ritorno di uno script SQL
sqlplus $usr/$pwd@$sid # Output Oracle
@${path_script}/script.sql ${Argomento}
if [ $? != 0 ] then
msg_txt=”The execution failed. Please investigate.”
echo ${msg_txt}
fi
Posted in 
