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

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.

If you utilise dbms_sql package, you can execute many SQL statements that is not possible to run it in PL/SQL before. You can bind and execute DML statements at runtime and you can also run DDL statements. You only limitation is your imagination.

John Koo Received on Thu Jan 21 1999 - 16:55:57 CST