Re: trigger Problem

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 08 Jul 1999 18:01:37 GMT
Message-ID: <3787e43b.19052736@inet16.us.oracle.com>

On Thu, 08 Jul 1999 09:27:19 -0700, Glen Upreti <Glen.Upreti_at_nau.edu> wrote:

>Thanks for yoru response Mark, I looked at all my other stored procs
>that use dbms_sql and I don't use execute in any of them! and they all
>work!
>
>I looked up the comments on dbms_sql.execute and it says:

<snip>

But if you read the description of parse then you would see that...

<quote>

• Currently, the deferred parsing feature of the Oracle Call Interface
• is not used. As a result, statements are parsed immediately. In addition,
• DDL statements are executed immediately when parsed. However,
• the behavior may
• change in the future so that the actual parsing (and execution of DDL
• statement) do not occur until the cursor is executed with "execute".
• DO NOT RELY ON THE CURRENT TIMING OF THE ACTUAL PARSING!

</quote>

So it works today, but it may not work tomorrow. Always use the execute. It can't hurt.

Of course in 8i this is all mute since you could just issue the command

  execute immediate <my statement>;

No cursors, no parsing, no mess.

>I put the sys.dbms_sql.execute(cnum); where you recommended, but now I
>get the following error:
>PLS-00221: 'EXECUTE' is not a procedure or is undefined
>what's up?

Execute is a function, so you need to call it like

return_status := dbms_sql.execute ( my_cursor );

>Again, thanks for your response
>enjoy,
>--glen
>

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 08 1999 - 13:01:37 CDT