Problem with Stored procedure-Job Hangs
Posted: Mon Mar 14, 2005 4:08 am
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?
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?