Executing Oracle Stored Procedure after load.
Moderators: chulett, rschirm, roy
Executing Oracle Stored Procedure after load.
Hi,
I want to execute a stored procedure as soon as I load the data in a table thru Oracle Enterprise Stage thru Load-Append method. I tried using the close command and after sub-routine options also. None of them is working. I tried using EXEC and CALL options in Close command.
Any help on this is appreciated.
I want to execute a stored procedure as soon as I load the data in a table thru Oracle Enterprise Stage thru Load-Append method. I tried using the close command and after sub-routine options also. None of them is working. I tried using EXEC and CALL options in Close command.
Any help on this is appreciated.
Thanks !!!
Sambit
Sambit
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"Not working" is unhelpful to us, because you have not described precisely what you tried. It's one thing to say you used EXEC, but how can we know that you used EXEC correctly?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Re: Executing Oracle Stored Procedure after load.
[quote="sambit"]Hi,
I want to execute a stored procedure as soon as I load the data in a table thru Oracle Enterprise Stage thru Load-Append method. I tried using the close command and after sub-routine options also. None of them is working. I tried using EXEC and CALL options in Close command.
Any help on this is appreciated.[/quote]
did you try this
in the "CLOSE COMMAND"
BEGIN
EXEC STORED PROCEDURE
END;
I want to execute a stored procedure as soon as I load the data in a table thru Oracle Enterprise Stage thru Load-Append method. I tried using the close command and after sub-routine options also. None of them is working. I tried using EXEC and CALL options in Close command.
Any help on this is appreciated.[/quote]
did you try this
in the "CLOSE COMMAND"
BEGIN
EXEC STORED PROCEDURE
END;
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The stored procedure must exist in the database. You can not create stored procedure code in the CLOSE command. You invoke the stored procedure from the CLOSE command (or from a Stored Procedure stage or wherever).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
One of the options I can suggest is that you can put trigger on your table which is going to load..
And that trigger in turn can call the procedure. You can have trigger at trancation level or as row level.
However the limitation with this apporach is that you can not have commit statements in the trigger you are using ! you can use only the DML statments.
hope this helps
----------------------------------
MK
And that trigger in turn can call the procedure. You can have trigger at trancation level or as row level.
However the limitation with this apporach is that you can not have commit statements in the trigger you are using ! you can use only the DML statments.
hope this helps
----------------------------------
MK
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Thanks for your help. The command which I mentioned in the last post is successfully executing now. There was an access issue for that.
I was wondering if anyone can help me in executing the same command thru a Command Activity Stage thru sequencer.
Command:
Parameters:
What do I put in above 2 cols? The commands and the parameters that I am trying execute and use are -
Command: begin schema.stored_procedure_name(parameter1); end;
Parameter: DB server name,DB user id, DB user pwd, schema name, parameter1
I was wondering if anyone can help me in executing the same command thru a Command Activity Stage thru sequencer.
Command:
Parameters:
What do I put in above 2 cols? The commands and the parameters that I am trying execute and use are -
Command: begin schema.stored_procedure_name(parameter1); end;
Parameter: DB server name,DB user id, DB user pwd, schema name, parameter1
Thanks !!!
Sambit
Sambit
Tryexecuting the same command thru a Command Activity Stage thru sequencer.
Code: Select all
sh -c
Code: Select all
echo "EXEC STORED PROCEDURE" | sqlplus -s dbUser/'dbPwd'@dbDSN