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

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