Re: PL/SQL: How use cmd-line param in stored proc. before DECLARE section?

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Thu, 21 Jan 1999 16:30:15 +0800
Message-ID: <36A6E597.2C4@bhp.com.au>

John Haskins wrote:
>
> Greetings:
>
> Can anyone tell me how to use a parameter passed to a stored procedure,
> before the DECLARE section? Every example I've seen uses passed parameters
> within the BEGIN section, but I need to use them before that point...and
> can't figure out how. Assistance appreciated!
>
> Here's a sample of the routine:
> ___________________________________________
> create or replace procedure PROC_TEST is
> V_SOURCEROW OUTPUT_TABLE%rowtype;
> cursor TEMP_CURSOR is
> select
> C1,
> C2
> from
> TABLE_100 T1
> where
> T1.C2 = '01-JAN-98'
> ;
> BEGIN
> FOR v_sourcerow IN temp_cursor LOOP
> INSERT INTO OUTPUT_TABLE (
> C1,
> C2
> )
> VALUES (
> V_SOURCEROW.C1,
> V_SOURCEROW.C2
> );
> END LOOP;
> END PROC_TEST;
> ___________________________________________
>
> My goal is to be able to pass the table name to the procedure at runtime.
> So I need to replace the "TABLE_100" with a parameter that is passed into
> the routine. But when I do this, the procedure produces errors when it
> compiles.
>
> Can anyone point me in the right direction?
>
> Thanks.

Two separate things here...

1. You can use parameters in the declare section but they are VARIABLES...

For example:

procedure my_proc(p_current_date date) is   v_local_date date := p_current_date;
begin
 ...
end;

2) PL/SQl resolves it references to the DATABASE at compile time. That means you can't use variable table names etc etc since these are sorted out at compile time. There is a package called "DBMS_SQL" which allows you to build an sql string which can then be parsed/executed etc to achieve what you want to do...

Cheers

--

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad"

• Salvador Dali

Received on Thu Jan 21 1999 - 02:30:15 CST