Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-00904 with database link

I've created a database link between a 9i and 8i database. It works but the same SQL in a PL/SQL block fails with "ORA-00904 : COL4 : identificateur non valide."

I've created a database link between a 9i and 8i database. This works:
 update TAB1 set COL1 = 
       (select distinct COL2 from TAB2@b8  
       where COL3=COL4 )
       where COL5  like '200512' ;
(b8 is a dblink from 9i to 8i.) But the same SQL in a PL/SQL block fails:
begin
 update TAB1 set COL1 = 
       (select distinct COL2 from TAB2@b8  
       where COL3=COL4 )
       where COL5  like '200512' ;
end;
I get "ORA-00904 : COL4 : identificateur non valide" (COL4 is a column of TAB2, which is in the 8i instance). What can I do?
If you are using a PL/SQL block, then you can recode to get around this error. Simply select the value from TAB2 into a variable as follows:
SELECT distinct COL2 INTO col2_value FROM tab2@b8 WHERE col3=col4;
Now that you have stored the value, you can use it in the update statement:
UPDATE tab1 SET col1=col2_value WHERE col5 LIKE '200512';
The first SELECT requires that only one row is returned, but then so does your original UPDATE statement.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close