Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Dynamic SQL Error -ORA-00904: invalid identifier

702494May 20 2009 — edited May 20 2009
Hello!

I'm really hoping I can get some fast help on this. In the interest of honesty, this is for a university oracle programming assignment. I've searched everywhere I can for the answer and haven't had any luck.

Anyway, the problem in a nutshell. I have to write a package to write a text file. The formatting information for the file is held in a table, which has columns to define the various characteristics like justification, padding character and field value.

RECORDTYPE FIELDNAME FIELDVALUE FIELDSIZE FIELDORDER PADDINGCHAR JUSTIFICATION DATASOURCE
7 CreditTotal v_credit 10 5 '0' LPAD PROGRAM
7 RecordType '7' 1 1 ' ' RPAD HARDCODE

I can build the whole file except this footer. the FieldValue "v_credit" references a variable that has the calculated total of the credit amounts for the file. There are also "v_debit", and "(v_credit-v_debt)" entries.

My question seems simple... How can I use this VARCHAR2 value from the table, and have it reference the variable within a Dynamic SQL string? If I use the textvalue of the column in the SQL string it gives me the invalid identifier error when it hits the EXECUTE IMMEDIATE statement. There is another row for the header which has "to_char(sysdate,'DDMMYYYY')" in it, and that runs fine. I'm assuming because they are native SQL statements and variables.

I have tried encapsulating the v_credit in "s, and using dynamica bind variables but had the same problem passing the names to the USING clause.

The SQL string created by the procedure is: SELECT LPAD(v_credit,10,'0' ) FROM dual

The full error is:
ORA-00904: "V_CREDIT": invalid identifier
ORA-06512: at "BWOOD.PKG_BRIAN", line 108
ORA-06512: at line 2

Line 108 is the EXECUTE IMMEDIATE statement

I would really appreciate someones help! I'm happy to post the procedure I've written, with the caveat that it's sloppy student work and needs cleaning up of all the experimental commented attempts:)

Edited by: user5426606 on 20-May-2009 03:34 - Added a few more facts.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 17 2009
Added on May 20 2009
5 comments
3,063 views