Re: PL/SQL Variables

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1995/10/19
Message-ID: <464c6l$afi_at_inet-nntp-gw-1.us.oracle.com>#1/1

c0imijka_at_mrd61.mrd-wc.usace.army.mil (James K. Anderson) wrote:

>I'm trying to use a pl/sql variable in place of a table name in an update. The code looks something like:

> PROCEDURE update_multiple_values (
> tssids IN IntArrayTyp,
> err_num OUT INTEGER) IS
> table_name VARCHAR2(18);
> BEGIN
> table_name := 'TIME_SERIES_VALUES';
> UPDATE table_name
> SET ...
 
>When I try to compile the package I get:
 

>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>143/7 PL/SQL: SQL Statement ignored
>143/14 PLS-00356: 'TABLE_NAME' must name a table to which the user has access
 

>Is what I am trying to do legal? It so, any ideas as to what is wrong?
 

>Thanks,
>Jim Anderson
>anderson_at_mrd62.mrd.usace.army.mil

If you are using Oracle7.1 or above you can:

create or replace function execute_immediate( stmt in varchar2 ) return number
as

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

    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

    return rows_processed;
exception

    when others then

          dbms_output.put_line( sqlcode );
          dbms_output.put_line( sqlerrm );
          dbms_output.put_line( substr( stmt, 1, 200 ) );
      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;

> PROCEDURE update_multiple_values (
> tssids IN IntArrayTyp,
> err_num OUT INTEGER) IS
> table_name VARCHAR2(18);
  rows_proc number;
> BEGIN
> table_name := 'TIME_SERIES_VALUES';

     rows_proc := execute_immediate( 'Update ' || table_name || 
                                     'set ....' );

If not, you cannot do what you want.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu Oct 19 1995 - 00:00:00 CET