Home » SQL & PL/SQL » SQL & PL/SQL » Passing Parameters into a Stored Procedure? (10G)
Passing Parameters into a Stored Procedure? [message #297597] Fri, 01 February 2008 12:05 Go to next message
oranewbie1234
Messages: 1
Registered: February 2008
Junior Member
Hi,

Using the following sqlplus call I am executing a sql script.

sqlplus username/password @test.sql "TEST1" "TEST2"

The test.sql script makes the following call:
execute TABLEOWNER.TEST_SP;

Can anyone tell me how to pass parameters shown in the sqlplus call to the execute command so that they are available to the stored procedure?

And then how do I pick those two parameter values up in the stored procedure?

I have a procedure created using the following syntax, but when I try to create the procedure using this syntax I get prompted for a value for 1, and then for 2, the procedure then creates without any errors but when I check the definition for the procedure I can see that rather then the &1 and &2 parameters, they have been replaced with whatever I enter for 1 and 2 when the procedure was created.

CREATE OR REPLACE PROCEDURE TEST_SP AS
--Declare and initialise variable passed from shell script
lvBATCH_ID VARCHAR2(10) := '&1';
lvCREATED_DATE VARCHAR2(6) := '&2';
.....

Thanks.
Re: Passing Parameters into a Stored Procedure? [message #297598 is a reply to message #297597] Fri, 01 February 2008 12:08 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The answer can be found by Reading This Fine Manual.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Previous Topic: Update Query
Next Topic: Existing Partitions with maxvalue need new partitions
Goto Forum:
  


Current Time: Sun Dec 04 08:35:03 CST 2016

Total time taken to generate the page: 0.12387 seconds