Oracle Transactional Business Intelligence

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

Categories

Java.sql.SQLException: Invalid column index

Received Response
2548
Views
14
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:

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

  • 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

  • Donald
    Donald ✭✭✭

    Engine Log File 2.txt attachment

     

    Regards,

    Donald

  • 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

  • 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

  • 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 

  • 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

  • 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

     

  • 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

  • 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

  • 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.

  • Donald
    Donald ✭✭✭

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

    Regards,

    Donald

  • User_6NK6Y
    User_6NK6Y ✭

    hello

    i am getting an error when running procedure call dm report

    the error is

    java.sql.SQLException: Invalid column index

    any idea?

    thanks

  • User_0YPLB
    User_0YPLB ✭

    The error you encountered, oracle.xdo.XDOException: java.sql.SQLException: Invalid column index, typically occurs when there's a mismatch in the columns being referenced or returned within the SQL query or in the way BI Publisher is handling the data.

    Here are some potential causes and solutions for the issue:

    1. Invalid Column Index in Function Return:

    The error may be related to how the GetValue1 function is defined or invoked. Ensure that the function is correctly handling the cursor and returning the expected value. Since the cursor c1 might not return any rows if the condition is not met, make sure to initialize the variable cc with a default value or handle this scenario explicitly.

    FUNCTION GetValue1 (p_value VARCHAR2) RETURN VARCHAR2 IS
    
    CURSOR c1 IS
    
        SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
    
    cc VARCHAR2(100) := 'Default Value';  -- Initialize with a default value
    
BEGIN
    
    FOR x IN c1 LOOP
    
        cc := x.aa;
    
    END LOOP;
    
    RETURN cc;
    
EXCEPTION
    
    WHEN OTHERS THEN
    
        RETURN 'Error GetValue1: ' || p_value;
    
END GetValue1;

    2. Check the Column Mappings in BI Publisher:

    If the query works correctly in SQL Developer but fails in BI Publisher, ensure that all columns referenced in the SQL query are properly mapped in the data model of BI Publisher. Sometimes, BI Publisher expects specific column indices that may not align with the SQL query.

    3. Check the Compatibility of the SQL Query:

    Although the WITH clause and functions are valid in Oracle SQL, BI Publisher might not handle such constructs well in certain versions or configurations. Simplify the query, if possible, by removing the WITH clause and inline the function call directly within the main query.

    4. Data Type or Conversion Issues:

    Ensure that the data types returned by the function and used in the query are consistent and do not require any implicit conversions that could lead to errors.

    5. Check for BI Publisher Patch or Configuration Issues:

    Sometimes, specific versions of BI Publisher may have bugs or configuration issues that lead to such errors. Ensure that you have the latest patches installed and check Oracle's support site for any known issues related to your BI Publisher version.

    Final Query:

    Here’s how you might modify the function to be more robust and ensure the query structure is BI Publisher-friendly:

    WITH 
    
    FUNCTION GetValue1 (p_value VARCHAR2) RETURN VARCHAR2 IS
    
        CURSOR c1 IS
    
            SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
    
        cc VARCHAR2(100) := 'Default Value';
    
    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

    If this doesn’t resolve the issue, consider running the query outside of BI Publisher to see if it produces any errors or behavior that might indicate what’s going wrong.