Re: calling stored procedures dymanic

Just a note: I believe you'll not be able to create a stored proc from the trigger 'cause creating the procedure will cause a commit and you can't commit in a trigger...???

Alton

"C. Ferguson" wrote:

> 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 - 15:20:09 CDT