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

John Haskins schrieb:
>
> 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.

Hi,

to have the tablename a beeing parameter, you've to use dynamic SQL(DBMS_SQL-package).

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Thu Jan 21 1999 - 01:01:08 CST