5 Replies Latest reply on May 20, 2009 1:23 PM by 702494

    Dynamic SQL Error -ORA-00904: invalid identifier

    702494
      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.
        • 1. Re: Dynamic SQL Error -ORA-00904: invalid identifier
          Aman....
          Hi,

          I would suggest that you make a small example of code( small means not more than 10-15 lines) which would regenerate the issue and post here. It wouldn't be possible to get the cause and surely not the solution by only with what you have posted. Use the {\code\} tag( without \ ) to make your code readable.

          HTH
          Aman....
          • 2. Re: Dynamic SQL Error -ORA-00904: invalid identifier
            Satish Kandi
            SELECT LPAD(v_credit,10,'0' ) FROM dual
            I presume your code is something like
            EXECUTE IMMEDIATE 'SELECT LPAD(v_credit,10,''0'' ) FROM dual';
            Whereas if this is a dynamic SQL, then it should be
            EXECUTE IMMEDIATE 'SELECT LPAD('||v_credit||',10,''0'' ) FROM dual';
            Having said that, you don't need a SELECT for this purpose. You can assign output of LPAD to a variable directly.
            • 3. Re: Dynamic SQL Error -ORA-00904: invalid identifier
              702494
              Thank you for the fast replies. I'll post a short chunk of code to demonstrate.

              The actual procedure is quite a bit longer, but this should give you the idea.
              PROCEDURE build_dbfile (p_settlement_date IN DATE, p_settlement_key IN VARCHAR2, p_type IN VARCHAR2) AS
              v_sqlString    VARCHAR2(4000) := 'SELECT ';
              v_rectype     NUMBER := 7;
              v_line           LONG;
              v_debit        NUMBER := 0;
              v_credit       NUMBER := 0;
              v_nrRecords    NUMBER := 0;
                CURSOR c_settlement IS... -- to select records for data rows
              
               -- cursor to select the rows with the file formatting and data info
                CURSOR c_header IS   SELECT   NVL(fieldvalue,fieldname) db_field, <---- selects the data column
                                                             fieldsize,
                                                             NVL(paddingchar, ''' ''') paddingchar,
                                                             NVL(justification,'LPAD') justification,
                                                            datasource
                                                FROM     FSS_DESKBANK_REF
                                                WHERE    recordtype = r_rec_types.recordtype
                                                ORDER BY recordType, fieldorder;
              -- SELECT to get the SUM of datarows for v_credit
                SELECT      SUM(transactionamount)
                INTO      v_credit... 
              
              -- SELECT to get the SUM of datarows for v_debit
                SELECT      SUM(transactionamount)
                INTO      v_debit....
              
              FOR r_head IN c_header LOOP
              
              v_sqlString := v_sqlString  || r_head.justification|| '('
                                                   || r_head.db_field ||','
                                                   || r_head.fieldsize || ','
                                                   || r_head.paddingchar || ')';
              v_sqlString := v_sqlString || ' FROM ' || v_tbl_name;
              DBMS_OUTPUT.PUT_LINE('SQL --> '||v_sqlString);
              EXECUTE IMMEDIATE v_sqlString INTO v_line;
              END LOOP;
              DBMS_OUTPUT.PUT_LINE(v_line);
              END build_dbfile;
              So the loop goes through the table rows, grabs each data value and formatting column, and builds the sql string, which is then passed to the execute statement. The issue is the ones where the datarow contains a reference to v_credit, and the error is generated. ie r_head.db_field contains the string "v_credit"

              Satish, the sql does look like that in the string, but the string is built from the table, not hardcoded. ie v_sqlString = "SELECT LPAD(v_credit,10,'0' ) FROM dual" when it is passed to the EXECUTE IMMEDIATE.

              Edited by: user5426606 on 20-May-2009 05:19
              • 4. Re: Dynamic SQL Error -ORA-00904: invalid identifier
                Satish Kandi
                Satish, the sql does look like that in the string, but the string is built from the table, not hardcoded. ie v_sqlString = "SELECT LPAD(v_credit,10,'0' ) FROM dual" when it is passed to the EXECUTE IMMEDIATE.
                v_credit is a variable and it cannot be resolved dynamically to be a column from dual. You need to construct the string before passing it for execution (as I have indicated above).
                • 5. Re: Dynamic SQL Error -ORA-00904: invalid identifier
                  702494
                  I think I see what you mean. The string within the field needs to be bracketed with the ||'s and passed like that to the execute statement.

                  I'll give that a go.

                  Thanks Satish!

                  [EDIT]

                  OK, the error is gone... but the output is wrong. The sql being passed is : SELECT LPAD('||v_credit||',10,'0' ) FROM dual

                  But the output is: 7999-999 000000000 ||v_credit||v_debit| ||v_nr

                  I assume I need to delimit the ' by adding them in pairs to the string, but its proving difficult to balance them.

                  Using this to substitute:
                  SELECT DECODE(r_head.datasource, 'HARDCODE', r_head.db_field, 'PROGRAM', _
                                       _'''||'||r_head.db_field||'||''', r_head.db_field)
                  INTO v_db_field
                  FROM DUAL;
                  The datasource column will always be PROGRAM for these entries, others need to be treated differently.

                  Edited by: user5426606 on 20-May-2009 06:16

                  Edited by: user5426606 on 20-May-2009 06:22