Solved

Please, Very Urgent - Merge statement - dblink - between 2 databases

Posted on 2004-04-14
12
2,097 Views
Last Modified: 2007-12-19
Hi Guys,
            I have db1 and db2.
db1 has a package.proc1, which uses a merge statement
the object (table1) that it tries to merge is in db 2
i have a public synonym for table1 that points to table1@dblink2db2

when i use merge, i am getting the follwing error...
when i have teh table one in the same db but under different schema i dont have that issue...in that cse there is no dblink involved.
please advice. this could be a sho stopper for my project.
 
ERROR at line 1:
ORA-01008: not all variables bound
ORA-02063: preceding line from PRST
ORA-06512: at "P_ATE.ACTION_UPDATE", line 6
ORA-06512: at line 2

ks
0
Comment
Question by:kshathrya
12 Comments
 
LVL 8

Expert Comment

by:annamalai77
ID: 10829642
hi

drop the db link and try creating it again. syntax is

CREATE PUBLIC DATABASE LINK <LINK_NAME> CONNECT TO <USERNAME> IDENTIFIED BY <PASSWORD> USING 'CONNECT STRING';

THE USERNAME AND PASSWORD belongs to the remote database and the connect string is the string that has been created in the tnsnames.ora for the remote databse. remember that u put the single quote in the connect string.

i think the problem is with the remote DB link and also the field name. check the field names ur trying to update, . there could be some unhandled exceptions also.

regards
annamalai
0
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10830148
kshathrya,
When you ran the test where table1@dblink2db2 was created on db1, did you also create a public synonym on the new table1 table?

I'm thinking that there is a conflict between table1 (in the current schema on db1) and the public synonym called table1.

Have you tried the MERGE using schema.table1@dblink2db2 instead of the public synonym?


>>> DBMS_CHU
0
 
LVL 5

Expert Comment

by:fmonroy
ID: 10830332
what version ar you using?

this is a reported bug for ora 8.1.7
======================
Bug No. 1485853
Fixed in Product Version 9.0.2
======================
try installing the latest patch in both databases
0
 

Author Comment

by:kshathrya
ID: 10830431
well, i am not sure whether this is a dblink issue, as i am able to do a select or update on test1 whcih is the synonym for test@dblink2db2

so the dblink works fine

i even gave 'all ' on test in db2 to public

i am using 10g for both databases....

initially i used same name for object and synonym
then i tried with a diff name for synonym

btw, the test table is not there in db1 and is only in db2

0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10831624
Can you post the merge statement, and the table structures of the two tables.

I have a feeling that you don't have a one-to-one mapping of columns, due to which you are getting "not all variables bound" error. I am not sure, but if you can check it, it might help.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:kshathrya
ID: 10837370
pratikroy:
i dont think that is the issue...the reason is, i just recreated the tabel in db1 (without any changes) and referred to the local table name instead and everything works fine.
i moved the table to schema2 and referred it from schema1's procedure that has the merge statement and it works fine.

this occurs only when i use a diff database & when i use dblink...
both db's are in same m/c

env: redhat linux 9
both are oracle 10g
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10837557
@kshathrya,

OK, as I said, I am not sure why you might be facing this problem. If I look at oerr ora 1008, It just gives me a message "ORA-01008: not all variables bound" which obviously does'nt help. Since I dont have the code, and the two databases, I can only make guesses.

Try and check these :
Have you created a synonym in DB1 for the table2@DB2 ?
Can you describe, select, insert into table2 from the SQL*Plus prompt ? If you can, can you try and run your merge statement from the command prompt. Check for both synonym name, and table@DBLink.
Can you check if you have more than one object with table1 name (probably a few synonyms and a table)
Are you running your procedure from the same schema which owns the table1 ?

We will have to narrow down our focus as close as possible to actual problem. There are so many questions to be answered. Again, if you could post the merge statement as it is, it might help. When you say "i moved the table to schema2 and referred it from schema1's procedure that has the merge statement and it works fine", did you change the procedure for it, or just changed the synonym of table2 ?

Did anything happen on your stored procedures that you wanted to call based on file arrival :) The question http://www.experts-exchange.com/Databases/Oracle/Q_20870520.html still is open :)
0
 

Author Comment

by:kshathrya
ID: 10837703
hey can u post yar email id
i am not comfortable posting the merge statement and table def in public area.
i can send u an email and u can answer the q here

i did create a synonym first (for (table1@db2) and it gave same error
then i removed the synonym and substituted it with table1@db2...same error

i can send u merge statement and table def in email

regarding that open q, i just got to this whole piece and that is related to this one too.
i will be getting to it tonight or tomorrow oonce i figure this out...both are of the same project that i am trying to finish. i will definetely award the points asap sorry bout that as i didnt try that yet and was pulled to a diff proj and this was in hold.

tnx
ks
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10837865
OK, send it to me on Prat05@aol.com
0
 
LVL 9

Accepted Solution

by:
pratikroy earned 250 total points
ID: 10838365
Check if you have a data problem in your table1. Your table1 has all nullable columns, that you wish to insert into table2. Try selecting default values (like you have done for a couple of columns) wherever you have null values - in the "USING". Do the same while setting the values during the "UPDATE". And same for "INSERT". Make sure that you dont try to update/insert NULL columns into the table2.

If this does'nt work or you have already checked it and there are no problems with data, then try replacing the MERGE statement with age-old PLSQL block with Update and Insert, like :

BEGIN
UPDATE TABLE2
SET (COL1, COL2, ...) =
     SELECT (COL1, COL2, ... FROM TABLE1 WHERE TABLE1.COLX = TABLE2.COLY)
AND COL5 = 22334;

IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO TABLE2 (COL1, COL2, ...)
    (SELECT COL1, COL2, ...
     FROM TABLE1
     WHERE COL5 = 22334)
END IF;
END;

Let me know if this works.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now