Re: calling stored procedures dymanic

yes.

Alton Ayers wrote:

> 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 - 18:28:36 CDT