Getting "ORA-03115: unsupported network datatype or representation" error while fetching array of varchar from anonymous pl/sql

Question

I am getting "ORA-03115: unsupported network datatype or representation " exception while fetching the varray of type from anonymous PL/SQL block.

my code is:

    Connection con = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    String dequeueQuery = "DECLARE " +
            " type namesarray IS VARRAY(5) OF VARCHAR2(10); " +
            " names namesarray;" +
            "   total integer;" +
            "   BEGIN " +

            "   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); " +

            "   ? := names;"+

            " END;";

    try{

            con = getConnection();

            con.setAutoCommit(false);

            cstmt =(OracleCallableStatement )con.prepareCall(dequeueQuery);
            cstmt.registerOutParameter(1, OracleTypes.ARRAY);
            boolean b = cstmt.execute();
            Array arr = cstmt.getArray(1);

              String[] recievedArray = (String[]) arr.getArray();
              for (int i = 0; i < recievedArray.length; i++)

                System.out.println(recievedArray[i]);

            con.commit();

    }catch (Exception e) {
        try {
            con.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }`

Please help me. Thank you in advance.

Answer 1

java.sql.SQLException: ORA-03115: unsupported network datatype or representation

This is caused by the following statement:

cstmt.registerOutParameter(1, OracleTypes.ARRAY);

This statement says array will be the output, but didn't specify the actual Oracle Type name as third parameter. You can check this Oracle Doc for more information on this.

We can fix the exception "java.sql.SQLException: ORA-03115: unsupported network datatype or representation" by adding a third parameter with actual Oracle Type name. In your case it is NAMESARRAY.

cstmt.registerOutParameter(1, OracleTypes.ARRAY,"NAMESARRAY");

But the above statement will throw following exception while running:

java.sql.SQLException: invalid name pattern: SCOTT.NAMESARRAY

This is because we haven't declared the type NAMESARRAY inside DB. The above exception says the user as SCOTT, but you can connect to the user of your choice and create type.

Creating type in DB:

connect scott/tiger
CREATE OR REPLACE TYPE namesarray AS VARRAY(5) OF VARCHAR2(10) ;
/

Once we create the type NAMESARRAY, if we execute your code without changing we will hit the following error:

java.sql.SQLException: ORA-06550: line 1, column 180:

PLS-00382: expression is of wrong type ORA-06550: line 1, column 173:

PL/SQL: Statement ignored

This error is because we have already defined the type at user level, but we are trying to create the type again inside the following code block:

String dequeueQuery = "DECLARE " +
            " type namesarray IS VARRAY(5) OF VARCHAR2(10); " +
            " names namesarray;" +
            "   total integer;" +
            "   BEGIN " +
            "   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); " +
            "   ? := names;"+
            " END;";

So, we need to remove the type declaration from that.

String dequeueQuery = "DECLARE " +
            " names namesarray;" +
            "   total integer;" +
            "   BEGIN " +
            "   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); " +
            "   ? := names;"+
            " END;";

After removing it if we execute the program after compilation, we should be able to see the following output:

Kavita
Pritam
Ayan
Rishav
Aziz

Following is the updated program:

import java.io.*;
import java.sql.*;
import oracle.jdbc.*;

public class DBQC {
   public static void main(String[] args) {
   try {
      Connection con=null;
      Class.forName("oracle.jdbc.OracleDriver");
      String connStr = "jdbc:oracle:thin:scott/tiger@//dbhost:1521/dbsrvc";
      con=DriverManager.getConnection(connStr);
      if(con != null)
      {
         System.out.println("Connection succeeded");

         String dequeueQuery = "DECLARE " +
            " names namesarray;" +
            "   total integer;" +
            "   BEGIN " +
            "   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); " +
            "   ? := names;"+
            " END;";

         CallableStatement cstmt = null;
         con.setAutoCommit(false);
         cstmt =(OracleCallableStatement)con.prepareCall(dequeueQuery);

         cstmt.registerOutParameter(1, OracleTypes.ARRAY,"NAMESARRAY");
         boolean b = cstmt.execute();
         Array arr = cstmt.getArray(1);

         String[] recievedArray = (String[]) arr.getArray();
         for (int i = 0; i < recievedArray.length; i++)
             System.out.println(recievedArray[i]);

         con.commit();
      }
      con.close();
    } catch(Exception e){e.printStackTrace();}
    }
}