What causes ora-00904 error on valid fields when querying across dblink in a cursor

15 pts.
Tags:
Cursor
DBLink
ORA-00904
PL/SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 !!

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Tom msu
    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.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following