Shell Programming and Scripting

Hi,

I'm writing a Shell script to run a stored procedure through SQL*Plus using the below script and it works well.

Test.sh
$ORACLE_HOME/bin/sqlplus
connect user/pwd@dbname
@Test.sql

Test.sql
call sp_test_procedure()

Now, similarly I want to run the below PL/SQL anonymous block from shell script. I'm not sure how to do this and what is the syntax. Please share your thoughts, thank you.

DECLARE
I_Parm1 VARCHAR2(200);
I_Parm2 VARCHAR2(200);
I_Parm3 VARCHAR2(200);
BEGIN
I_Parm1 := 'Data1';
I_Parm2 := 'Data2';
I_Parm3 := 'Data3';
SP_Test(
I_Parm1 => I_Parm1,
I_Parm2 => I_Parm2,
I_Parm3 => I_Parm3
);
END;
/

Try if the below on works. Or similarly you can place the anonymous block inside file Test.sql and run as you tried in post# 1. Did you try this way..?

Code:

#!/bin/ksh
user="myUser"
passwd="werwe"
DB="myDB"

$ORACLE_HOME/bin/sqlplus -s $user/$passwd@DB << EOF
set verify off
set heading off
set feedback off
DECLARE
I_Parm1 VARCHAR2(200);
I_Parm2 VARCHAR2(200);
I_Parm3 VARCHAR2(200);
BEGIN
I_Parm1 := 'Data1'; 
I_Parm2 := 'Data2';
I_Parm3 := 'Data3';
SP_Test(
I_Parm1 => I_Parm1,
I_Parm2 => I_Parm2,
I_Parm3 => I_Parm3
);
END;
/
EOF