Java.Sql.Sqlexception: Ora-03115: Unsupported Network Datatype or Representation

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

Instead of:

rs = ps.executeQuery(Query);

you should execute:

rs = ps.executeQuery();

Otherwise, you unprepare your statement and lose all parameters because you assign it a new SQL statement (even though it's the same).

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

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();}
    }
}

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

Try replacing the line

            callStatement.registerOutParameter("OUT_REPAIR_INFO", Types.REF);

with

            callStatement.registerOutParameter("OUT_REPAIR_INFO", oracle.jdbc.OracleTypes.CURSOR);

ORA-03115: unsupported network datatype or representation when trying to execute stored procedure with .NET Core

I solved my problem! The boolean parameter p_DumpDebugMsgs caused the exception. ODP does not seem to support boolean parameters as described here.

Because the parameter p_DumpDebugMsgs has a default value, I ended up not passing a value for this parameter.

ORA-03115: unsupported network datatype or representation when trying to to fill DataSet

You need to add an OracleParameter for the OUT parameter TestOut but you are not doing this.

Add these lines after the other lines that set up the parameters:

    var outParam = new OracleParameter("TestOut", OracleDbType.RefCursor, ParameterDirection.Output);
    command.Parameters.Add(outParam);

You will then need to execute the command separately rather than pass the command to the OracleDataAdapter. Do this by adding the line

    command.ExecuteNonQuery();

immediately after the two that add outParam.

Finally, fill the dataset from the ref cursor in the OUT parameter by replacing the line

    new OracleDataAdapter(command).Fill(ds);

with

    new OracleDataAdapter().Fill(ds, (OracleRefCursor)outParam.Value);

Incidentally, I got a different error when I ran your code. I got the error PLS-00306: wrong number or types of arguments in call to 'GET_DATA'.

---

Related Topics