General OBIEE

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Donald Ferreira
    Java.sql.SQLException: Invalid column indexAnswered
    Topic posted November 15, 2019 by Donald FerreiraRed Ribbon: 250+ Points, tagged BI Publisher, BI Publisher Data Models 
    57 Views, 12 Comments
    Title:
    Java.sql.SQLException: Invalid column index
    Summary:
    Errors out with the mesaage
    Content:

    Hello There,

    We are using Oracle Financials Cloud and BI Publisher. I have a simple WITH Clause sql construct. On execution it gives the below error. .

    Could you please advise on what needs to be corrected?


    oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLException: Invalid column index
     

    Database Version:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    PL/SQL Release 12.1.0.2.0 - Production
    CORE 12.1.0.2.0 Production
    TNS for Linux: Version 12.1.0.2.0 - Production
    NLSRTL Version 12.1.0.2.0 - Production

    SQL Query:

    WITH  FUNCTION GetValue1 (p_value VARCHAR2) Return VARCHAR2 IS
                      CURSOR c1 IS
                           SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
          
                  cc VARCHAR2(100);
                BEGIN
                     FOR x IN c1
                     LOOP
                          cc := x.aa;
                     END LOOP;
                     RETURN cc;
                EXCEPTION
                      WHEN OTHERS
                      THEN
                          RETURN 'Error GetValue1: '|| p_value;
                END GetValue1;
    ------------
         PO AS
         (SELECT * FROM po_headers_all)
    ------------
    ,    POLines AS
         (SELECT * FROM po_lines_all)
    ------------
    --Main Query
    ------------
    SELECT po.segment1, polines.po_header_id , GetValue1('ABC') GetValue
    FROM   PO, POLines
    WHERE  polines.po_header_id = po.po_header_id

     

     

    Thank you.

    Look forward to hearing from you.

    Regards,

    Don

    Best Comment

    Fernando Ponte

    Hi Donald.

    Forgot about some options in the Data Model.

    The following works.

    Type of SQL: Non-Standard SQL.

    I used this code in my Data Model just to test.

    WITH  FUNCTION GetValue1 (p_value VARCHAR2) Return VARCHAR2 IS
                      CURSOR c1 IS
                           SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
          
                  cc VARCHAR2(100);
                BEGIN
                     FOR x IN c1
                     LOOP
                          cc := x.aa;
                     END LOOP;
                     RETURN cc;
                EXCEPTION
                      WHEN OTHERS
                      THEN
                          RETURN 'Error GetValue1: '|| p_value;
                END GetValue1;
    ------------
    --Main Query
    ------------
    SELECT GetValue1('ABC') GetValue
    FROM   DUAL


    Cheers

    Fernando

     

     

     

    Comment

     

    • Fernando Ponte

      Hi Donald.

      This is just me and my standards, so ignore this first  lines if you want. There is one thing that always bugs me when I see some Data Models. It is the "SELECT * FROM table". It may not be the your case but, if something change in the table structure your Data Model will be affected and you are selecting more than you need.

      Does this work fine on SQL Developer? I believe it will so I think it is something related to the way BI Publisher interacts with the database.

      Can you download the Engine Log from the data model for more details and share with us.

      Are you able to add just the columns you need on those two WITH block PO and POLINES and test again?

      Cheers

      Fernando

      • Donald Ferreira

        Hello Fernando,

        Thank you for taking the time to look into the issue and for your reply.

        The query works fine outside BI Publisher, no problem. 

        I tried to add alias to the columns in the main query, no benefit.

        I have attached the engine log file for your reference.

        Also, below change I did, discable statements FOR LOOP and it works good. Attached it's 'engine log file 2.txt' for your reference:

        WITH
        ------------
           FUNCTION GetValue1 (p_value VARCHAR2) Return VARCHAR2 IS
             CURSOR c1 IS
                SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
              
               cc VARCHAR2(100);
             BEGIN
               /*FOR x IN c1
               LOOP
                 cc := x.aa;
               END LOOP;
           */
               RETURN 'Test';
             EXCEPTION
                    WHEN OTHERS
                     THEN
                            RETURN 'Error GetValue1: '|| p_value;
             END GetValue1;
        ------------
             PO AS
             (SELECT * FROM po_headers_all)
        ------------
        ,    POLines AS
             (SELECT * FROM po_lines_all)
        ------------
        --Main Query
        ------------
        SELECT po.segment1, polines.po_header_id , GetValue1('ABC') GetValue 
        FROM   PO, POLines
        WHERE  polines.po_header_id = po.po_header_id
         
         

        Look forward to hearing from you.

        Regards,

        Donald

        Engine Log File.txt (70KB)
        • Fernando Ponte

          Hi Donald

          I think the first error was because of the following part in your original code.

          FOR x IN c1
                           LOOP
                                cc := x.aa;
          END LOOP;
          RETURN cc;

          [2019-11-18T07:54:45.258+00:00][dp id:463169166][sch info:]Bind Variables ...
          [2019-11-18T07:54:45.258+00:00][dp id:463169166][sch info:]    1: :null
          [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]XMLGEN:Exception while processing datamodel...:__ora_fusion_user__temp_81910071_8fb6_4741_9ac3_042425fc62e8_xdm
          [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]Process Cancelled Stage:false
          [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]XMLPGEN:Invalid column index
          [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]java.sql.SQLException: Invalid column index

          If I'm not wrong when you use ":" in BI Publisher it will expect a Parameter right? That is why if failed exactly on that validation.

          Once you removed in the second example the Error became a Warning in the Engine Log 2.

          Cheers

          Fernando

          • Donald Ferreira

            Hello Fernando,

            Yes I noticed that := assignment was creating the problem, thank you.

            But in a PL/SQL function you have to assign the values to a variable in that method, whereas BI Publisher doesn't accept it. How to overcome this issue in BIP?

            Regards,

            Donald 

            • Fernando Ponte

              Hi Donald.

              I would transfer the function to the database instead of being part of the WITH block. Especially if you plan to reuse it.

              I like the challenge so I will do some tests too. So far I don't know an answer for the workaround in the Data Model. Hope someone else has the same issue and come with some ideas.

              Cheers

              Fernando

              • Donald Ferreira

                Hello Fernando,

                We are working with Oracle Cloud Financials, so creating the function in database is not possible and therefore the use of FUNCTION in the WITH clause.

                Thank you for spending time on this issue. Would love to hear how to get around it.

                If I come across any solution,will keep you updated.

                Regards,

                Donald

                 

    • Donald Ferreira

      Engine Log File 2.txt attachment

       

      Regards,

      Donald

      Engine Log File 2.txt (62KB)
    • Fernando Ponte

      Hi Donald.

      The only weird thing I found in the engine log was.

      [dv id:1293682950]Validation code:DM08_COLUMN_ALIAS_LENGTH
      [dv id:1293682950]Error Type:WARNING
      [dv id:1293682950]Description:Selected column length name exceeds the limit of 15. Length of the column name must not be more than 15 chars. Use short alias for column names. 
      [dv id:1293682950]Details:Column name / alias:   REMARKS
      ----------------------------------------------------------------------
      polines.po_header_id      length exceeds limit of 15 chars.
       
      [dv id:1293682950]==================================
       
      Here you find more information about the process of Data Model validation.

      Just to remove that warning can you add an alias to your "polines.po_header_id"

      Just another note. Can you modify the with block to the construction below.

           PO AS
           (SELECT Segment1 FROM po_headers_all)
      ,    POLines AS
           (SELECT PO_Header_Id FROM po_lines_all)

      Cheers

      Fernando

    • Fernando Ponte

      Hi Donald.

      Forgot about some options in the Data Model.

      The following works.

      Type of SQL: Non-Standard SQL.

      I used this code in my Data Model just to test.

      WITH  FUNCTION GetValue1 (p_value VARCHAR2) Return VARCHAR2 IS
                        CURSOR c1 IS
                             SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
            
                    cc VARCHAR2(100);
                  BEGIN
                       FOR x IN c1
                       LOOP
                            cc := x.aa;
                       END LOOP;
                       RETURN cc;
                  EXCEPTION
                        WHEN OTHERS
                        THEN
                            RETURN 'Error GetValue1: '|| p_value;
                  END GetValue1;
      ------------
      --Main Query
      ------------
      SELECT GetValue1('ABC') GetValue
      FROM   DUAL


      Cheers

      Fernando

       

       

       

      • Donald Ferreira

        Thank you Fernando for taking the time to find the workaround. It works for me toosmileyyes

        It is strange to see, the same query gets executed in normal run in the database, whereas in BIP we have to say Oracle's sql is a non standard sql

         

        Regards,

        Donald

        • Fernando Ponte

          Hi Donald.

          No worries. It was an interesting learning. Never thought about using Function on WITH clause in BIP. 

          An old workmate once told me while running SQL on different tools.

          "We have two pieces of software talking with the database. They make not talk the same way all the time"

          Cheers

          Fernando.

     

    community forum