ORA-04054
Bipin Ganar, August 04, 2003 - 9:24 am UTC
Hi Tom,
I have created one package to copy the data from one database to another with databaselink but as one of databaselink is not working it is giving me directly error ORA-04054.
Create or replace package package1 as
procedure p_mv_data(i_data_push_id in varchar2) is
lv_dest_br varchar2(100);
sql_stmnt_pushcntrl varchar2(1000);
sql_stmnt_pushdata varchar2(1000);
lv_data_pushid varchar2(30);
lv_sqlcode number;
lv_sqlerrm varchar2(200);
dblink_not_found exception;
pragma exception_init(dblink_not_found,-04054);
begin
lv_data_pushid :=i_data_push_id;
select '@'||service_name||'.world'
into lv_dest_br
from schema1.push_control,schema1.networked_branches
where to_number(destination_db)=br_cd
and data_push_id=lv_data_pushid;
sql_stmnt_pushdata:='insert
into schema3.push_data'||''||lv_dest_br||''||
' select b.col1,b.col2,b.col3,b.col4,b.col5,b.col6,b.col7,
b.col8,b.col9,b.col10,b.col11,b.col12,b.col13,b.col14,b.col15,b.col16,b.col17,b.col18,b.col19,
b.col20,b.col21,b.col22,b.col23,b.col24,b.col25,b.col26,b.col27,b.col28,b.col29,b.col30
from schema2.push_control a ,schema2.push_data b
where a.data_push_id ='||''''||lv_data_pushid||''''||
' and a.data_push_id=b.data_push_id;
EXECUTE IMMEDIATE sql_stmnt_pushdata;
commit;
exception
when dblink_not_found then
RAISE_APPLICATION_ERROR(-04054,'error');
insert into datacopy.pushstring values(sql_stmnt_pushdata);
when others
lv_sqlcode:=sqlcode;
lv_sqlerrm :=sqlerrm;
dbms_job.isubmit(v_job_no,v_task,sysdate,null);
commit;
end p_mv_data;
end package1;
Can u tell me how to handle such error ?
as procedure is getting successfully completed then i 'm not able to trace which of the link is not working .
Although if it's not working it should go for the next dblinks and insert the data.
August 04, 2003 - 10:17 am UTC
I do not think you've given us the whole story here.
that error is raised upon COMPILATION, not execution of the block of code.
I fail to see how this example would do that. it seems the script that is invoking this is using a dblink and that script is what is failing to compile.
you might see this at runtime IF you were compiling an anonymous block of PLSQL -- which are you not. Then it should be catchable.
sumam seth, August 04, 2003 - 11:43 pm UTC
I've tested the same what you have mentioned in your answer,when iam using a anonymous block it throws the exception ,how do i handle that exception in my program,as if u see the pkg.procedure p_mv_data, when the database link fails it never goes in to the exception part.
My objective here is to handle the pl/sql compilation exceptions thru my program.
August 05, 2003 - 7:10 am UTC
give me a concrete working example to demonstrate with.
Lolita, August 13, 2004 - 9:23 am UTC
What is the maximum number of rows or maximum size of a PL/SQL table that can be pass thru a database link. I have successfully tried passing 145 rows 4 bytes each but more than that I'm getting the catch all error 'eof of communication channel error' How can I work around with this limitation?
August 13, 2004 - 4:51 pm UTC
there is no defined limit, if you are hitting a 3113, that would necessitate a tar.
Lolita, August 13, 2004 - 9:24 am UTC
What is the maximum number of rows or maximum size of a PL/SQL table that can be pass thru a database link. I have successfully tried passing 145 rows 4 bytes each but more than that I'm getting the catch all error 'eof of communication channel error' How can I work around with this limitation?
using PL/SQL in the using clause of Execute Immediate
Lolita, August 13, 2004 - 9:24 am UTC
What is the maximum number of rows or maximum size of a PL/SQL table that can be pass thru a database link. I have successfully tried passing 145 rows 4 bytes each but more than that I'm getting the catch all error 'eof of communication channel error' How can I work around with this limitation?