What causes ora-00904 error on valid fields when querying across dblink in a cursor
I have a procedure that contains a cursor that runs in a cursor for loop. The select query runs fine over the dblink all by itself, but when I try to compile the procedure, I get the PL/SQL: ORA-00904: invalid identifier on the column names. I can do a describe on the table over the link and all of the columns are there.
Software/Hardware used:
Software/Hardware used:
ASKED:
June 12, 2008 11:18 PM
UPDATED:
June 20, 2008 11:03 PM
RELATED QUESTIONS
Answer Wiki:
Hello Tom.
I have seen this type of error when one of the databases is non-oracle, or it is Oracle but some other version. Is that the case ?
If so, there are a few things I would suggest to try:
- If the error is detected in the 'where' clause, you might need to be sure that no implicit type conversions are being made in the 'where' conditions (i.e. comparing numeric fields with varchar ones), or you may even need to force this type conversions (ex. t1.numeric_field1 = to_char(t2.numeric_field2)). This may sound a little strange (maybe not too little) but believe me, it could work.
- If the error is in the columns list of the 'select' statement, you could need to re-write your cursor using dynamic sql, enclosing your column names or even your table names in double quotes.
Example:
<pre>BEGIN
query_str := 'SELECT "num", "cust", "amt" FROM inv_january WHERE invnum = :id';
OPEN c FOR query_str USING inv_num;
LOOP
FETCH c INTO inv_num, inv_cust, inv_amt;
EXIT WHEN c%NOTFOUND;
-- process row here
END LOOP;
CLOSE c;
END;</pre>
Good luck !!
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you for the suggestion.
I think this worked, but about the time I got totrying it, the process started working through the dblink again. So I have both methods working at the moment. If the straight througj method fails, I’ll definitely use this suggested method.