Oracle query.

[saby]

I have a SP which stores data in a table, What I want is that whenever data is stored, another SP fires and collects data. But the first SP should not blocked.

I cannot use Trigger on that table and call the second SP from it.

Is there any means in oracle to have this?

Thanks.

[amitpatel66]

I assume what you are looking at is when the secound procedure is called from the first, then first should continue its operation without waiting for second procedure to complete?

In this case, make use of PRAGMA AUTONOMOUS TRANSACTION in the second procedure. This should help.

[saby]

I assume what you are looking at is when the secound procedure is called from the first, then first should continue its operation without waiting for second procedure to complete?

In this case, make use of PRAGMA AUTONOMOUS TRANSACTION in the second procedure. This should help.

Thanks for the reply.
What I want to do is, the 1st procedure will keep a data in a table. And this data whenever comes to the table, acts as an event to fire the second procedure. But the two procedure runs independently.
I will try your advice.

[amitpatel66]

Thanks for the reply.
What I want to do is, the 1st procedure will keep a data in a table. And this data whenever comes to the table, acts as an event to fire the second procedure. But the two procedure runs independently.
I will try your advice.

Yes, in that case, just insert the data in the table in proc1, and commit and then call the second proc, and as i said include the PRAGMA AUTONOMOUS TRANSACTION in the second procedure

[saby]

Yes, in that case, just insert the data in the table in proc1, and commit and then call the second proc, and as i said include the PRAGMA AUTONOMOUS TRANSACTION in the second procedure

well, I want the second proc to be called automatically when data goes to the table. can I use trigger or in that case the 1st proc will be blocked.