Using variables instead of table names

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.

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

                  Thanks, Olaf

Olaf Hansen
oha_at_is.informatik.uni-kiel.de Received on Tue May 25 1999 - 05:24:18 CDT