SQL Exception : Invalid Column Index while invoking SP in Oracle

[Former Member]

Hi All,

I am getting the following error while trying to invoke call to Stored Procedure in Oracle DB using SAP MII 12.1 SP5. I tried out the following combinations and get the same error as java.sql.SQLException: Invalid column index.

1.

Modes : FixedQueryWithOutput

select * from table(eusuper.xxxx_mii.Get_Facility_Daily_Estimate('8402', '09/01/2010', '09/23/2010'))

java.sql.SQLException: Invalid column index

2.

Modes : FixedQueryWithOutput

call eusuper.xxxx_mii.Get_Facility_Daily_Estimate('8402', to_date('[Param.1]', 'MM/DD/YYYYY HH24:MI:SS'), to_date('[Param.2]', 'MM/DD/YYYY HH24:MI:SS'))

java.sql.SQLException: Invalid column index

3.

Modes : FixedQueryWithOutput

select * from table (eusuper.bhpp_mii.Get_Facility_Daily_Estimate('8402', to_date('[Param.1]', 'MM/DD/YYYYY HH24:MI:SS'), to_date('[Param.2]', 'MM/DD/YYYY HH24:MI:SS')))

java.sql.SQLException: Invalid column index

I am using Oracle 9.. and able to execute other statements and read/write data to the DB using the same JDBC Driver.

However I tried out executing the statements in Query Analyzer on Oracle DB and able to retrieve records successfully.

Can anyone help me with this ...

Thanks in Advance,

Amit Rath

[sidnooradarsh · Accepted solution]

Hi Amith,

Internally MII makes JDBC connection via Java code (probably might use Prepared statement query) to Oracle DB and This error comes up when you are sending incorrect number of parameters.

How are you sending the Data to Stored procedure from Javascript? Are you sending with quotes on?

If you are sending with any quotes or if your data has some quotes in between you need to escape them first so that system can interpret column and data properly.

Some basics,

1) Use FixedQueryWithOutput mode if you are returning a Cursor output from your Oracle stored procedure.

2) Use Command mode if you are performing insert/delete/update and not planning to return anything.

3) Use the following syntax,

 
CALL StoredProcName ('[Param.1]','[Param.2]','[Param.3]','[Param.4]',:OutVar)
or
CALL StoredProcName  ('[Param.1]','[Param.2]','[Param.3]','[Param.4]',?)

Hope this helps!!

Regards,

Adarsh