得到"ORA-03115:不支持的网络数据类型或表示".从匿名pl/sql获取varchar数组时出错 [英] Getting "ORA-03115: unsupported network datatype or representation" error while fetching array of varchar from anonymous pl/sql

问题描述

从匿名PL/SQL块中获取类型的varray时，出现"ORA-03115:不支持的网络数据类型或表示形式"异常.

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

我的代码是:

    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.

推荐答案

java.sql.SQLException:ORA-03115:不支持的网络数据类型或 表示形式

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

这是由以下语句引起的:

This is caused by the following statement:

cstmt.registerOutParameter(1, OracleTypes.ARRAY);

此语句说，数组将是输出，但没有将实际的Oracle Type名称指定为第三个参数.您可以检查 Oracle Doc 了解更多信息对此.

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.

我们可以通过添加带有实际Oracle类型名称的第三个参数来修复异常"java.sql.SQLException: ORA-03115: unsupported network datatype or representation".您的情况是NAMESARRAY.

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:无效的名称模式:SCOTT.NAMESARRAY

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

这是因为我们尚未在DB内部声明类型NAMESARRAY.上面的例外将用户表示为SCOTT，但是您可以连接到您选择的用户并创建类型.

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.

在数据库中创建类型:

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

一旦我们创建了类型NAMESARRAY，如果我们在不更改代码的情况下执行代码，则会遇到以下错误:

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

java.sql.SQLException:ORA-06550:第1行，第180列:

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

PLS-00382:表达式的类型错误ORA-06550:第1行，第173列:

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

PL/SQL:语句被忽略

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

以下是更新的程序:

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

这篇关于得到"ORA-03115:不支持的网络数据类型或表示".从匿名pl/sql获取varchar数组时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！