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

A copy of this was sent to "John Haskins" <76054.334_at_compuserve.com> (if that email address didn't require changing) On Wed, 20 Jan 1999 18:24:39 -0800, you 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

You can't do this (yet, in 8i this particular function is trivial) with out using the DBMS_SQL package. Given your example, I might have a routine:

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );
    dbms_output.put_line( dbms_sql.last_row_id );

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

and then code:

create or replace procedure proc_test( p_tname in varchar2 ) is
begin

   execute_immediate( 'insert into ouptput_table

                       select c1, c2 
                         from ' || p_tname || ' t1
                        where t1.c2 = ''01-JAN-98'' ' );

(btw: the insert ... select .. will be much faster then for x in select * from t insert will be...)

> ___________________________________________

>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.
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.