java.sql.SQLException: Invalid column type when executing procedure with package level types from jdbc

Question

Problem Description: I'm trying to call procedure stored in my database through java code. The procedure I want to call has one parameter with type of 'MAP_VARCHAR' (I have defined it as type in below package).

Now, I'm trying to execute this procedure from my java code however, passing the parameter is giving java.sql.SQLException.

SQL Package to show you how did I create my SQL procedure and type

--------------------------------------------------------
--   Package Body TEST_PACKAGET
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "TEST_PACKAGE" AS

  PROCEDURE PerformersRole(P_Performer_Map MAP_VARCHAR) AS
  BEGIN

        DBMS_OUTPUT.PUT_LINE('Hello ' || P_Performer_Map('Engineer'));

  END PerformersRole;

END TEST_PACKAGE;

/
--------------------------------------------------------
--   Package TEST_PACKAGE
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PACKAGE "TEST_PACKAGE" AS 

  /* TODO enter package declarations (types, exceptions, methods etc) here */ 
  TYPE MAP_VARCHAR IS TABLE OF VARCHAR(100) INDEX BY VARCHAR(100);

  PROCEDURE PerformersRole(P_Performer_Map MAP_VARCHAR);
END TEST_PACKAGE;

/

My java code

    public void executeProcedure(){

       CallableStatement statement = null;
        try {

/// Oracle Connection code should be here 
             Connection connection = connectionManager.createConnection();


            Hashtable newMap = new Hashtable();

            newMap.put("Engineer", "Hanson");
            newMap.put("Assistant", "Alice Kim");
            newMap.put("Supervisor", "James MaCoy");


            StringBuffer procedure = new StringBuffer("{call TEST_PACKAGE.PerformersRole(?) }");
            statement = connection.prepareCall(procedure.toString());
            statement.setInt(1, newMap );

            statement.execute();
        } catch (Exception ex) {


        } 

}

The error I'm receiving when I'm executing my java code is

java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8761)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8259)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9012)
    at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:4983)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)

What I am missing ?!

Appreciate your help in-advance

Your procedure doesn't seem to accept an integer as input value, there must be some more relevant method than `setInt` . — Arnaud, Aug 17 '17 at 13:11
MAP_VARCHAR is custom type which will hold table of VARCHAR and indexed by VARCHAR. i.e. item('Madrid'):= 'Real Madrid' — Hanson, Aug 17 '17 at 21:18

score 0 · Answer 1 · answered Aug 18 '17 at 08:04

0

In Oracle 11.2 is not possible to use package level types from jdbc. And is not possible to create associative array on sql level.

This code will fail.

create  TYPE MAP_VARCHAR IS TABLE OF VARCHAR(100) INDEX BY VARCHAR(100);

One exception is associative array of varchar or number index by binary_integer. Accessing PL/SQL Associative Arrays

answered Aug 18 '17 at 08:04

Arkadiusz Łukasiewicz

6,241
1
11
17

Thank you for your useful comment @ArkadiuszŁukasiewicz I wish I could vote for it as useful but I have just created my account and I can not vote because my reputation is < 15 – Hanson Aug 23 '17 at 04:55

score 0 · Accepted Answer · answered Aug 23 '17 at 04:50

OK, I have spent time trying to figure out how can I overcome this issue after Arkadiusz Łukasiewicz comment and since he did not provide any official document mentioning that, I did quick research and it seems his claim is legit.

There are 2 approaches to overcome the issue of accessing SQL package level types from your java:

Approach# 1
The answer provided by Panser in below URL
[https://stackoverflow.com/a/25989632/8477836][1]

all what you need is to build SQL DECLARE ... BEGIN ... END block and then execute it from your java.

Approach# 2
I inserted the level type (MAP_VARCHAR) values I want to pass in my procedure in separate table and then inside my SQL package, I created function to retrieve those values using cursor from the table I created and store the returned values into my local variable which has type of (MAP_VARCHAR)

Function which will be responsible to store values of MAP_VARCHAR

    FUNCTION GET_MAP_VARCHAR(P_PROCESS_ID NUMBER)
    RETURN MAP_VARCHAR IS
      LV_MAP_VARCHAR MAP_VARCHAR;
      LV_MAP_VARCHAR_TABLE_ROW EPAD_WF_RESD_PRFM_RLE%ROWTYPE;

        CURSOR C_GET_MAP_VARCHAR_ROWIS 
            SELECT * FROM TEST T
            WHERE T.TEST = P_PROCESS_ID;

  BEGIN

      FOR R_GET_MAP_VARCHAR_ROWIN C_GET_MAP_VARCHAR_ROWLOOP
        LV_MAP_VARCHAR(R_GET_MAP_VARCHAR_ROW .ROLE):= R_GET_MAP_VARCHAR_ROW.NAME;
      END LOOP;

    RETURN LV_MAP_VARCHAR;

  END GET_MAP_VARCHAR

My new SQL procedure should looks like:

  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "TEST_PACKAGE" AS

  PROCEDURE PerformersRole(P_ID Number) AS

    LV_Performer_Map MAP_VARCHAR
  BEGIN
    LV_Performer_Map MAP_VARCHAR= GET_MAP_VARCHAR(P_PROCESS_ID NUMBER);

        DBMS_OUTPUT.PUT_LINE('Hello ' || LV_Performer_Map('Engineer'));

  END PerformersRole;

Hope this will help anyone facing trouble with accessing SQL package type from Java

score 0 · Answer 3 · answered Jan 15 '21 at 19:43

Aside from all the other answers, the scenario where we had the issue was different. This error isn't an "exact" error you can diagnose but you'll have to assume the column it's complaining about is the clue towards the issue.

We had the same issue on one of our database, and the issue was that the query had a subquery which was expecting a single item but was being passed an array of data instead. This threw off the main query, and so it reported an 'invalid column' for that column which had an array of data. Check to make sure your main query isn't using a subquery or subqueries. If it is using subqueries, then check each of them and ensure they're all being returned what your expectations are.

java.sql.SQLException: Invalid column type when executing procedure with package level types from jdbc

3 Answers3