Hi ...
Help needed urgently.
Could someone provide a small example of:
Calling a PL/SQL stored procedure and its parameters from UNIX
and/or
Calling a PL/SQL package and its stored procedure and parameters from UNIX.
Thanks,
Printable View
Hi ...
Help needed urgently.
Could someone provide a small example of:
Calling a PL/SQL stored procedure and its parameters from UNIX
and/or
Calling a PL/SQL package and its stored procedure and parameters from UNIX.
Thanks,
Code:#!/bin/ksh
PARAM1=ABC
sqlplus / << EOF
execute YourPackage.YourFunc('$PARAM1');
EOF
Hi Jeff,
I tried what you provided, but did not work.
Could you send me a more specific example.
This is the kind of errors I got:
--------------------------------
create or replace procedure proc1(
name varchar2) as
begin
dbms_output.put_line('The name is : ' || name);
end proc1;
SQL> execute proc1 ('akl');
The name is : akl
PL/SQL procedure successfully completed.
From UNIX:
------------
$ PARAM1=Akl
$ echo $PARAM1
Akl
$ sqlplus / execute proc1('$PARAM1')
/usr/bin/sh: Syntax error: `(' is not expected.
$ sqlpus / execute proc1('$PARAM1');
/usr/bin/sh: Syntax error: `(' is not expected.
$ sqlplus / execute proc1 '$PARAM1'
Usage: SQLPLUS [ [
Try:Quote:
Originally posted by appash
Hi Jeff,
I tried what you provided, but did not work.
Could you send me a more specific example.
This is the kind of errors I got:
--------------------------------
create or replace procedure proc1(
name varchar2) as
begin
dbms_output.put_line('The name is : ' || name);
end proc1;
SQL> execute proc1 ('akl');
The name is : akl
PL/SQL procedure successfully completed.
From UNIX:
------------
$ PARAM1=Akl
$ echo $PARAM1
Akl
$ sqlplus / execute proc1('$PARAM1')
/usr/bin/sh: Syntax error: `(' is not expected.
$ sqlpus / execute proc1('$PARAM1');
/usr/bin/sh: Syntax error: `(' is not expected.
$ sqlplus / execute proc1 '$PARAM1'
Code:$ PARAM1=Akl
$ echo $PARAM1
Akl
$ sqlplus yourusername/yourpassword@yourdb << EOF
execute proc1('$PARAM1');
EOF
Thanks a lot Jeff.. it worked.
Just be careful with that, because if a user on unix does a
/usr/proc/bin/ptree
they potentially could see your username and password running the script.
A better method I think would be
USER1="scott"
PASS1="tiger"
COM2="connect $USER1/$PASS1"
COM3="sqlplus /nologon"
$COM3 <$COM2
set serveroutput on
execute test_package.test_procedure
ESQL
Cheers,
I would prefer to do it with OS authentication, but the poster seemed to get confused.
Actually, in my example I forgot about passing the parameters to oracle also. I'm sure they'll work it out.
Cheers