Problem with Stored procedure-Job Hangs

[suma]

Hi
I have imported a stored procedure which has no arguments.
When i call my procedure in my target database the exection never stops and my job hangs.
I cudnt trace out where exactly the mistake is?

Also when ever i import a sp with arguments i get a warning message
and While executing the Job that calls the sp i get the warning as

DataStage][SQL Client][ODBC][Microsoft][ODBC driver for Oracle][Oracle]ORA-06563: top level procedure/function specified, cannot have subparts
SQLSTATE=37000, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC driver for Oracle]Syntax error or access violation

What can be the reason for the above warnings.

My proc

CREATE OR REPLACE PROCEDURE update_tab AS
TYPE sam_tab is table of sampletab.empno%TYPE;
cursor c1 is select empno from emp;
s_tab sam_tab;
colltype sam_tab;
rows natural :=3;
BEGIN
open c1;
loop
fetch c1 bulk collect into s_tab limit rows;
exit when c1%NOTFOUND;
forall i in 1..s_tab.count
update sampletab set empno = 'EXE' || empno where (empno = s_tab(i) AND
s_tab(i) not like 'EXE%') RETURNING empno BULK COLLECT INTO colltype;
end loop;
close c1;
for i in 1..colltype.count loop
dbms_output.put_line(colltype(i));
End loop;

END update_tab;


Kingly let me kno where i went wrong?

[roy]

Hi,
Just out of curiousity..
is this happening when you invoke the SP from plsql or similar oracle tools?

I guess its one of the following reasons:
1. your peforming something not supported.
2. your having SP issues not related to DS.

in any case I recommend cosulting your DBA and going over the SP material also in the DS end of things.

do you use the SP stage or user defined sql to run a SP?

[suma]

The stored procedure gets executed sucessfully when i am working with oracle. But when i invoke the Sp thro DS i get this problem.
I am calling the SP in the UPDATE ACTION of the Target Database

Are there any limitations that DS dosent support certail PLSQL commands.

[roy]

Hmm,
Last time I used SP on Oracle from DS was done via the befoe/after SQL options or via user defined SQL.
not via the Update action that is SP.
the syntax I used was

Code: Select all

call <SP full name>

in case of a before/after sql ended it with ; or with ;; depending on transaction required of the before/after and the sql itself.

this way was efectively trigerring both SP and plsql pakages which are not SP at all!.

IHTH,