Re: calling stored procedures dymanic

Hi Bernard,
  I'm a little bit confused about what you are trying to do, but I'll comment as I go. See interleaved comments.

Bernhard Riehm wrote:

> Hello,
>
> I want to create a trigger for a table.

Ok

>
> This trigger should call a stored procedure,
> which name is generated at runtime.

 Generated at runtime by what? I'm only asking to get a clearer picture of your architecture...Are you trying to get the trigger to generate a stored procedure and then execute it, or is something else generating the stored procedure, and at any given point in time the trigger is supposed to discover this new procedure and execute it?
(And you mean the stored procedure is generated at runtime, not just a name?)

 Look at the table all_source for information on stored procedures that exist in the system.

>
> At designtime of the trigger, I don't know all
> possible stored procedure calls.

That's ok, all_source table has the info.

>
> It is necessary that we can add stored procedures dynamically,
> and they should be called by the trigger.

ok

>
> With DBMS_SQL I can only generate
> dynamic SQL-Statements.
> Is there a way to generate stored procedure calls?

you can use dbms_sql for this also...Are you asking can you create the stored procedure on the fly, via code at runtime? If yes, you can do this via dbms_sql.

If you are asking can you build a dynamic statement that is calling a yet to be determined stored procedure? Yes, you can do that via dbms_sql.

How do you determine the name of the stored procedure that get's called by the trigger?

Once you've determined that, you can write a dbms_sql statement to call that procedure.

> Read the docs on dbms_sql and look at the examples.

Hope that helps,
Cindy

>
>
> Bernhard Riehm,
> perbit Software GmbH.
Received on Thu Aug 05 1999 - 13:49:19 CDT