Calling Oracle stored procedure from ksh script

Shell Programming and Scripting
1

Friends,

I'm newbie with ksh so wanting some help....

  1. I'm trying to call oracle stored procedure from ksh script by taking variable value from runtime, feed into script and execute procedure.
  2. Put name1 and name2 value from script run replacing $3 & $4

I'm trying to put name1 in vNam1 and name2 in vNam2 in procedure.

*** Script run

script.ksh /@dblogin name1 name2

*** Ksh script

#!/bin/ksh
...
...
$ORACLE_HOME/bin/sqlplus $1 execute pkg.proc1 $3 $4

end

*** Stored procedure

Create or Replace pkg

proc1(vNam1 IN varchar2, vNam2 IN varchar2)
select name from employees
where name = 'vNam1';

...
end;
/

Thanks and appreciate your answers.

2

Couple of questions:

  1. are you using ksh shell to create or replace a procedure? This would be considered an oracle DDL statement BTW.
  2. You call a procedure using pl/sql not usually straight sqlplus. Do you mean to use just sqlplus? Or pl/sql? (you can access PL/SQL via sqlplus).

Why did I ask? - most of what you show seems to me not to run without error. Did you really try any of this?

3

Jim,

I have got package created in the database which has got couple of procedures.
I'm using ksh shell script to login into database and call that store procedure which runs some "Alter table....".

I confirm that my db login works but somehow not able to execute procedure.
Procedure excepts couple of input value which I'm trying to use $3 & $4 value from shell run. i.e. $3 here is name1 , $4 is name2

script.ksh /@dblogin name1 name2

There is no error but values from run doesn't get feeded to procedure

4

Try something like

$ORACLE_HOME/bin/sqlplus  << EOSQL
$1
begin
pkg.proc1('$2','$3') 
end;
/
EOSQL
5

How to close this thread??

I plan to run procedure manually after db connection.