Java.sql.SQLException: Invalid column index

Donald · November 2019

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

User_0YPLB · September 10

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.

FPonte · November 2019

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 · November 2019

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 · November 2019

Engine Log File 2.txt attachment

Regards,

Donald

FPonte · November 2019

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.

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acpmr/validate-data-models.html#GUID-1FB8BC31-3546-4237-867C-4A592425395F

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 · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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

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

Oracle Transactional Business Intelligence

Categories

Java.sql.SQLException: Invalid column index

Summary

Content

Best Answer

1. Invalid Column Index in Function Return:

2. Check the Column Mappings in BI Publisher:

3. Check the Compatibility of the SQL Query:

4. Data Type or Conversion Issues:

5. Check for BI Publisher Patch or Configuration Issues:

Final Query:

Answers