Re: Sending table as an object to procedure

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 18 Jul 2002 10:53:09 -0700
Message-ID: <92eeeff0.0207180953.7d538b37@posting.google.com>

"Stjepan Brbot" <stjepan.brbot_at_zg.hinet.hr> wrote in message news:<ah5lhm$7qi4$2_at_as201.hinet.hr>...
> In my Oracle database I have a lot of tables with the same columns
> USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED for tracking
> the processes of creation and modification of rows. Each table has it's
> own trigger raising before insert and update and fulfilling already
> mentioned columns:
>
> BEGIN
> IF (inserting) THEN
> :NEW.user_created:=user;
> :NEW.date_created:=TRUNC(sysdate);
> END IF;
> :NEW.user_modified:=user;
> :NEW.date_modified:=TRUNC(sysdate);
> END;
>
> Each table has this same trigger. OK, this works but question is: can I
> create a generic procedure that will accept the name of table as an
> input argument and that will fulfill columns USER_CREATED, DATE_CREATED,
> USER_MODIFIED, DATE_MODIFIED of that given table with appropriate
> values.
>
> If I have this generic procedure I do not have to change every trigger
> for each table if I want to change any functionality of this trigger,
> then I can change only generic procedure.
>
> Finally, my problem is that I do not know how to send the table (as an
> object) to procedure!

Yes you can by using dynamic plsql. EXECUTE IMMEDIATE can do the trick.

PROCEDURE Your_Proc (

   table_name_ IN VARCHAR2,

   pk_cols_       IN ..... -- Need Rowid or PK columns
   user_          IN VARCHAR2 )

IS

   stmt_ VARCHAR2(500);
BEGIN
   stmt_ := 'UPDATE ' || table_name_ || ' SET .....';    EXECUTE IMMEDIATE stmt_;
   EXCEPTION

      WHEN OTHERS THEN
         ...

END Your_Proc;
/

You can also use DBMS_SQL package instead of EXECUTE IMMEDIATE.

//Rauf Sarwar Received on Thu Jul 18 2002 - 12:53:09 CDT