Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Sending table as an object to procedure

Re: Sending table as an object to procedure

From: Rauf Sarwar <>
Date: 19 Jul 2002 12:47:07 -0700
Message-ID: <>

"Vladimir M. Zakharychev" <> wrote in message news:<ah8vfo$ht4$>...
> Well, despite the harsh words Sybrand used, his point holds up - approach
> you suggested is incorrect. a) applies for sure if you use EXECUTE IMMEDIATE
> and can be slightly reduced (but not totally avoided) with DBMS_SQL and
> cursor caching; and b) is true unless you call the procedure in an AFTER INSERT
> OR UPDATE trigger (and the question was about BEFORE triggers.)
> Corrections and additions welcome.

Looks like you also missed the whole point...I hope you read the original post in this thread.

He was asking a specific question as to how to pass a table name as an object to a procedure. He *did not* ask whether this procedure *can* be used in a BEFORE trigger or not.

> 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.
> Finally, my problem is that I do not know how to send the table (as an
> object) to procedure!

If he has coded the triggers himself then I assume and *hope* he knows what he is trying to do and whether to use BEFORE trigger or AFTER trigger and what is table mutation. If he had asked whether this is a *good/bad* or *doable* solution...then he would have gotten a different response.

My post answered the basic question posed by him...How to pass table name as an object to a procedure? and obviously my solution is to use DBMS_SQL or EXECUTE IMMEDIATE.

  1. Excessive parsing: Now instead of telling me the downsides or upsides of using dynamic sql...which I am aware of..thank you...if you have another or better way of passing table name as an object to a procedure....then I am all ears. No fun intended...I am here to learn just like everyone else.
  2. Table mutation: My post did not respond to this since it was not originally asked. *may not* respond. This is *ONLY* for people whose IQ is greater then their age!!!

//Rauf Sarwar Received on Fri Jul 19 2002 - 14:47:07 CDT

Original text of this message