Oracle PL/SQL :ORA-04052 with Database link

i have created a database link in my producation database as following


CREATE PUBLIC DATABASE LINK <NAME>
CONNECT TO <USER>
IDENTIFIED BY <P/W>
USING <CONNECT STRING OF TARGET DATABASE

oracle version 9.0.2.8
OS : Windows 2003 EE R2 SP2
Using oracle failsafe 3.3.4 with windows cluster

Problem Description :

whenever i run my pl/sql procecdure to pull the data from the target database i get the following error

ERROR at line 3
ORA-06550:line 3 column 2
PL/SQL : ORA-04052: error occured when looking up remote object <SCHEMA>.<TABLE>@<DATABASE LINKE NAME>
ORA-00604 : ERROR occured at recursive SQL level 1
ORA-03106 : fatal tow-task communication protocol error
ORA-02063: preceding line from <DATABASE LINK NAME>

I have tested the same way in the test environment the same oracle version as well the OS but without oracle failsafe and windows cluster
the script goes smoothly with out any errors.

i have google and checked the OTN for all available solutions but still nothing its not getting throw

i will be very thankful for your replys and solutions
any farther clarification i am ready

Oracle PL/SQL over a database link

When I try to run this script over a database link called calprod I get the following error:________________________________________________ORA-06550: line 7, column 9:PLS-00352: Unable to access another database 'CALPROD.PERSON_ID'ORA-06550: line 7, column 9:PLS-00201: identifier 'SIS_OWNER@CALPROD ...

Oracle can we create pl/sql code over database link?

We are using Oracle 9i database.I want to know if we can create or modify pl/sql code (procedue, package, etc) over the database link? That means - can we create a procedure in remote database using the db link? ...

Oracle getting error pl/sql .ORA 04052

I am getting error when i am trying to excute a query which is using dblinks.pl/sql : ORA 04052 Error occured when looking up a remote objectDBAMON.Send@Mailer.WORLDORA-00604:Error at recursive SQL1ORA-12154 : TNS could not resolve the service name. Can anybody regarding this..its a triggerCREATE OR ...

Oracle How to catch PL/SQL: ORA-04052 error?

I need to execute a procedure that select remote objects but the remote db is a RAC DB and I only have access to one of the nodes, the remote RAC have load balancing enabled and the remote DBA can’t create a DB Service for my connection, so, I have to re-run the execution of my procedure until it ...

Oracle PL/SQL: ORA-00942: table or view does not exist

i have a table like table_emp, there is no data in the table.when use just query select * from table_emp; its returnin empty record properly.when i use the same query like select emp_no into v_emo_no from table_emp . its showing erros like PL/SQL: ORA-00942: table or view does not exist.how to handl ...

Oracle PL/SQL: ORA-01031: insufficient privileges in dbms_sql.parse

trying to create a trigger using dbms_sql I got the above msg although being in my own schema.The sequence of stmts iscursor_name := dbms_sql.open_cursor;dbms_output.put_line('1');dbms_sql.parse(cursor_name, trigger_definition, dbms_sql.v7);The content of trigger_definition is (incl cr's):CREATE OR ...

Oracle Pl/SQL tables across database links

Can pl/Sql tables be passed across database links using remote procedure calls? any help appreciated - ...

Oracle PL/SQL: ORA-12714: invalid national character set specified / nested table

I have a strange problem( bug?) in pl/sql with national characters.Im my db, I have some tables with columns defined like nested table. Some nested tables defined like a collection of NVARCHAR2(4000). When I try to select this kind of column with a simple query in toad or sql*plus, there is no probl ...

Oracle PL/SQL: ORA-00905: missing keyword

while i m adding and statement after when matched for the merge command i m getting this error. while the command is working fine without and statemnt when matched then...does it have anything to do with oracle version. currently i m using oracle 9.2. i trind this on diff systems but getting the sam ...

Oracle pl/sql ORA-00942 err

I have created procedure in that i m creating a table and merging it with some other table and after that i fetching the records in the ref cursor and at last i m droping the same table.I m working on Oracle 9i.Sometimes i m able to complie the procedure but sometime i m getting the err ORA-00942 Ta ...

Oracle PL/SQL -- connecting to database

I'm quite new to PL/SQL. I was wondering if it's possible to connect to a database in a pl/sql block? If so, can you give me the exact syntax as I'm not too familiar with pl/sql. ...

Oracle PL/SQL -- connecting to database

I'm quite new to PL/SQL. I was wondering if it's possible to connect to a database in a pl/sql block? If so, can you give me the exact syntax as I'm not too familiar with pl/sql. ...

Oracle PL/SQL: ORA-00934: group function is not allowed here

The following PL/SQL block thows an error "PL/SQL: ORA-00934: group function is not allowed here".The SQL works fine in SQLPLUS.Any clue as to what is happening?DECLARE v_termdt DATE := NULL;v_rehiredt DATE := NULL;BEGINSELECT max(rownum) INTO v_cnt,sum( case when action_code='TERM'then to_number(to ...

Oracle PL/SQL: ORA-03001: unimplemented feature

i am just trying to understand the bulk binding feature of oracle this is the test code that i am trying set serveroutput onset timing on declare src_obj src;beginSELECT srcip BULK COLLECT INTO src_objFROM rawcdr ;insert into rawcdr1(srcip) values src_obj;end;i am using oracle 10 g std edition and i ...

Oracle PL/SQL: ORA-00942: table or view does not exi

I have logged in as a user “username/password” , This is the Query: “SELECT a.tablespace_name AS DATABASE, a.file_name AS file_name, SUM(a.bytes)/1024/1024 AS file_size, SUM(b.bytes)/1024/1024 AS free_space, b.block_id, b.blocksFROM DBA_DATA_FILES a INNER JOIN DBA_FREE_SPACE b ON a.tablespace_nam ...

Oracle PL/SQL ORA-06502 numeric or value error: host bind array too small

We seem to be getting the above error when a parameter declared as VARCHAR2 hits somewhere between 200 and 255 characters. The limit varies between our live environment and using Toad and PLSQL Developer to test the procedure. How do we increase the host bind array limit? ...

Oracle PL/SQL: ORA-00904:

We have custom package which is working fine since long time. But all at once we are getting compilation errors.. The following are the error messages that we are getting233/13 PL/SQL: SQL Statement ignored248/20 PL/SQL: ORA-00904: "SLIP_TYPE": invalid identifier254/13 PL/SQL: SQL Statement ignored2 ...

Oracle PL/SQL: ORA-00942: table or view does not exist

I've a procedure with a cursor who make a selection of DBA_TAB_COLUMNS.If i try the select statement with sqlplus it works fine.I don't know why it doesn't work in the procedure.Here is my procedure:Create or replace procedure analyse_schalter islob_length integer;read_amount integer;read_offset int ...

Oracle PL/SQL: ORA-00942: table or view does not exist works in SQL not in object

I have posted this issue in the database thread, but thought here might have more luck.I have a type created in schema B where I am referencing within a select statement a view view_x found in schema A.I have granted SELECT to a role in schema A on view_x and granted this role to schema B.from SQL*P ...

Oracle PL/SQL: ORA-00984: column not allowed here

I am Trying to compile this procedure, but i get error, could please anybody help me out.Thanks.SQL> create or replace procedure FOF_sport_setup(2 v_model_cd in varchar2, -- model_cd used in many place3 v_portfolio_name in varchar2, -- portfolio name4 v_src_acct_nbr in varchar2, -- source account ...

Oracle PL/SQL:ORA-00980: synonym translation is no longer valid

I have imported the full database (9i) and import terminated succefully.ButI got the followinf errors:ORA-04098: trigger 'UNITE.CAPT_FEE' is invalid and failed re-validation .There are number of triggers where we got the same error.Please help ...

Oracle PL/SQL: ORA-00942:

how can i solve this error ...

Oracle PL/SQL: ORA-00942: table or view does not exist NEED HELP ! ! ! ! !

vMYQUERY := 'CREATE GLOBAL TEMPORARY TABLE ' ||vTBL_TMPREGION1 || '( ACCESS_CODE VARCHAR2(50),LOGIN_ID VARCHAR2(50)) ON COMMIT PRESERVE ROWS ';EXECUTE IMMEDIATE vMYQUERY ;AFTER CREATING TEMPORARY GLOBAL TABLE (QUERY GIVEN ABOVE )WHEN I ISSUE FOLLOWING QUERYSELECT COUNT(LOGIN_ID) INTO vTOCOUNT FROM v ...

Oracle PL/SQL: ORA-00904: "LAST_UPDATE_LOGIN": invalid identifier

I have the error PL/SQL: ORA-00904: "LAST_UPDATE_LOGIN": invalid identifier in the code below where I bold it. Can someone point to why I encounter this error? I have checked the tables and they all have the LAST_UPDATE_LOGIN column.Thank you for your time.insert /*+ append parallel(plan_i) */ into ...

Oracle PL/SQL: ORA-01744: inappropriate INTO

I have this part of the stored procedure, trying to use one select query to fill in 2 variables, got error 'PL/SQL: ORA-01744: inappropriate INTO', please help, thanks in advance --print summary of subsectionselect t.* from (select count(membership.id) into v_tot_mem_by_trainer,sum((membership_plan. ...

Oracle PL/SQL over a DB Link

I have a package that performs very well locally but very slowly over a db link, could anyone point me in the direction of the reason and/or possible solutions/improvements?The package consists of a proc that iterates on a cursor (c1) of about 120k rows executing between 5 and 10 other procs per row ...

Oracle PL/SQL : ORA-06502 Numeric or value error

The database is 8i.There is this update statement which returns the error : UPDATE t_comsis_support suSET num_support_anfr = decode(pVeSU( iComptSU ).nL_Support_mut,1,decode(vOldNumSuppAnfr,null,pVeSU( iComptSU ).nL_Num_support_ANFR,vOldNumSuppAnfr),pVeSU( iComptSU ).nL_Num_support_ANFR),desi_suppor ...

Oracle PL/SQL: ORA-00904: "B"."RECORDYEAR": invalid identifier

SELECT B.*,A.ID MACCOUNTID,NVL((SELECT X.CASENUMFROM BOE_CASE XINNER JOIN BOE_MCASEPARCELS YON X.ID = Y.CASEIDAND Y.PARCELNO = B.PARCELNOAND Y.RECORDYEAR = B.RECORDYEARAND Y.RECORDYEAR = SPBOE_MACCOUNTPARCELSMQ.RECORDYEAR),'') OTHERCASENUM,NVL((SELECT X.IDFROM BOE_CASE XINNER JOIN BOE_MCASEPARCELS Y ...

Oracle PL/SQL: ORA-01732: data manipulation operation not legal on this view

I have the following script that gives the above error. The table NFPC_PO_EDI850_IN_LINES is not a view and I can do DML on it using SQL. But when I try to create this script, I get the above error.HELP!!!!A/A ...