Re: Using variables instead of table names

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 25 May 1999 12:50:01 GMT
Message-ID: <374c9b04.88224229@newshost.us.oracle.com>

A copy of this was sent to Olaf Hansen <oha_at_is.informatik.uni-kiel.de> (if that email address didn't require changing) On Tue, 25 May 1999 12:24:18 +0200, you wrote:

>Hi!
>
>I'm working with Oracle 7.x.
>
>1.
>
>Is it possible to use a variable containing the table name in a INSERT
>or FROM clause ?
>
>I created an exception table as follows:
> CREATE TABLE ex.EXC (
> ROW_ID ROWID,
> OWNER VARCHAR2(30),
> TABLE_NAME VARCHAR2(30),
> CONSTRAINT VARCHAR2(30));
>
>When inserting a row into the exception table I want to insert another
>row in the table which is specified by the attribute TABLE_NAME of the
>exception table.
>
>So I tried it with following trigger :
> CREATE TRIGGER ex.TRIG
> AFTER INSERT ON ex.EXC
> FOR EACH ROW
> BEGIN
> INSERT INTO :new.TABLE_NAME
> SELECT a.*
> FROM :new.TABLE_NAME a
> WHERE a.ROWID=:new.ROW_ID;
> END;
> /
>but it doesn't work.
>

you cannot bind IDENTIFIERS. We need to parse and execute a unique statement. Use dbms_sql. For example, say you create a procedure:

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 );

end;
/

your trigger can become:
  CREATE TRIGGER ex.TRIG
  AFTER INSERT ON ex.EXC
  FOR EACH ROW
  BEGIN
   execute_immediate( 'INSERT INTO ' || :new.TABLE_NAME || '

                       SELECT a.*
                         FROM ' || :new.TABLE_NAME || ' a
                       WHERE a.ROWID = ''' || :new.ROW_ID || '''';

  end;

this will *fail* at runtime with a mutating table error. To see how to avoid that, see:

http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=429259315&fmt=text

you'll defer the processing above until an AFTER trigger (not a for each row)

remember also that during the compilation/execution of a trigger -- ROLES are never enabled. the owner of the trigger will need SELECT on the table they are reading from and INSERT on the table they are writing to *DIRECTLY* granted to them -- not via a role.

>Can anyone help me ?
>
>
>
>2.
>
>I want to grant the object privilege REFERENCE for all tables from user
>1 to user 2. In accordance with the manual I must grant the privilege
>for each table separatly. Is it really not possible to grant it in one
>statement (as I can do it for system privileges) ?
>

no, there are no privs like that. must be done at the object level.

>
> Thanks, Olaf
>
>
>
>Olaf Hansen
>oha_at_is.informatik.uni-kiel.de

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue May 25 1999 - 07:50:01 CDT