Oracle Transactional Business Intelligence

Products Banner
Home› Oracle Analytics› Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Java.sql.SQLException: Invalid column index

Received Response
845
Views
12
Comments
Donald
Donald ✭✭✭
in Oracle Transactional Business Intelligence

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

Tagged:
· Share on FacebookShare on Twitter

Answers

  • FPonte
    FPonte ✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Donald
    Donald ✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Donald
    Donald ✭✭✭

    Engine Log File 2.txt attachment

     

    Regards,

    Donald

    · Share on FacebookShare on Twitter
  • FPonte
    FPonte ✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter
  • FPonte
    FPonte ✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Donald
    Donald ✭✭✭

    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 

    · Share on FacebookShare on Twitter
  • FPonte
    FPonte ✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Donald
    Donald ✭✭✭

    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

     

    · Share on FacebookShare on Twitter
  • FPonte
    FPonte ✭✭✭✭✭

    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

     

     

     

    No_standard_SQL.JPG

    · Share on FacebookShare on Twitter
  • Donald
    Donald ✭✭✭

    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

    · Share on FacebookShare on Twitter
  • FPonte
    FPonte ✭✭✭✭✭

    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.

    · Share on FacebookShare on Twitter
  • Donald
    Donald ✭✭✭

    Good one, I will remember that, "We have two pieces of software...." smiley

    Regards,

    Donald

    · Share on FacebookShare on Twitter