Home » Infrastructure » Unix » How to write a shell script to exec a proc with in and out params and also it has default values
How to write a shell script to exec a proc with in and out params and also it has default values [message #231087] Sat, 14 April 2007 01:30 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

How to write a shell script to execute a procedure which contains in and out parameters and also it has default values.

ex:

procA(name VARCHAR2,city VARCHAR2 DEFAULT NULL,x out number);

variable y nukmer;
exec ProcA(name=>'A', city=>'INDIA', x=>:y);
exec ProcA(name=>'A', x=>:y);

The shell script should execute the procA for the above two types.

thanks in advance
Re: How to write a shell script to exec a proc with in and out params and also it has default valu [message #231095 is a reply to message #231087] Sat, 14 April 2007 02:02 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You cannot save it in a .SQL file and execute it with SQL*Plus
sqlplus -s userid/pwd @script.sql


Ross Leishman
Re: How to write a shell script to exec a proc with in and out params and also it has default valu [message #231353 is a reply to message #231095] Mon, 16 April 2007 12:49 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/bin/ksh
#======================================================
# stored proc with parms
#======================================================
## CREATE OR REPLACE PROCEDURE p1 (i_parm IN NUMBER, o_parm OUT NUMBER)
## IS
## BEGIN
##    o_parm := 5 * i_parm;
## END;
## /
my_in_parm=5
RETVAL=`sqlplus -s scott/tiger@dev <<EOF
set serveroutput on
WHENEVER SQLERROR EXIT sql.sqlcode
declare
  x number := $my_in_parm;
  y number;
begin
  p1(x, y);
  dbms_output.put_line('o_parm from p1 is '||y);
end;
/
exit sql.sqlcode;
EOF`

echo $RETVAL
X=`echo $RETVAL | awk '{print $5}'`
echo $X

[Updated on: Mon, 16 April 2007 12:49]

Report message to a moderator

Previous Topic: Ceck if the variable lies in a particular range
Next Topic: Calculate amount of TPMC in unix knowing mips in mainframe
Goto Forum:
  


Current Time: Fri Mar 29 09:37:48 CDT 2024