Executing Oracle Stored Procedure after load.

[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.

[ray.wurlod]

"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?

[betterthanever]

[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;

[sambit]

Yeah. I tried the following,


begin schema.stored_procedure_name(parameter); end;

Error- Fatal Error: Invalid close command

[ray.wurlod]

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).

[mk_ds09]

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

[betterthanever]

[quote="sambit"]Yeah. I tried the following,


begin schema.stored_procedure_name(parameter); end;

Error- Fatal Error: Invalid close command[/quote]

try
begin
exec stored procedure
end;

[sambit]

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

[rameshrr3]

executing the same command thru a Command Activity Stage thru sequencer.

Try

Code: Select all

sh -c

with argument

Code: Select all

echo "EXEC STORED PROCEDURE" | sqlplus -s dbUser/'dbPwd'@dbDSN

Assuming unix and the oracle bin path is in your $PATH when you run teh job sequencer