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
Answers
-
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
0 -
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 ISCURSOR c1 ISSELECT 'aa' aa FROM dual WHERE p_value = 'ABC';cc VARCHAR2(100);BEGIN/*FOR x IN c1LOOPcc := x.aa;END LOOP;*/RETURN 'Test';EXCEPTIONWHEN OTHERSTHENRETURN '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') GetValueFROM PO, POLinesWHERE polines.po_header_id = po.po_header_idLook forward to hearing from you.
Regards,
Donald
0 -
Engine Log File 2.txt attachment
Regards,
Donald
0 -
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
0 -
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 indexIf 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
0 -
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
0 -
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
0 -
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
0 -
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
CheersFernando
0 -
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
0 -
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.
0 -
Good one, I will remember that, "We have two pieces of software...."
Regards,
Donald
0 -
hello
i am getting an error when running procedure call dm report
the error is
java.sql.SQLException: Invalid column index
any idea?
thanks
0 -
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 cursorc1
might not return any rows if the condition is not met, make sure to initialize the variablecc
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 theWITH
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_idIf 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.
0