oracle 从匿名 pl/sql 获取 varchar 数组时出现“ORA-03115：不支持的网络数据类型或表示”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28579184/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting "ORA-03115: unsupported network datatype or representation" error while fetching array of varchar from anonymous pl/sql
提问by DhanuRoutu
I am getting "ORA-03115: unsupported network datatype or representation " exception while fetching the varray of type from anonymous PL/SQL block.
我在从匿名 PL/SQL 块中获取类型的变量时收到“ORA-03115：不受支持的网络数据类型或表示”异常。
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.
请帮我。先感谢您。
回答by learningloop
java.sql.SQLException: ORA-03115: unsupported network datatype or representation
java.sql.SQLException: ORA-03115: 不支持的网络数据类型或表示
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 Docfor more information on this.
该语句表示数组将是输出，但没有指定实际的 Oracle 类型名称作为第三个参数。您可以查看此Oracle 文档以获取更多信息。
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.
我们可以java.sql.SQLException: ORA-03115: unsupported network datatype or representation通过添加具有实际 Oracle 类型名称的第三个参数来修复异常“ ”。在你的情况下是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
java.sql.SQLException：无效的名称模式：SCOTT.NAMESARRAY
This is because we haven't declared the type NAMESARRAYinside DB. The above exception says the user as SCOTT, but you can connect to the user of your choice and create type.
这是因为我们还没有NAMESARRAY在 DB 中声明类型。上述异常表示用户为 SCOTT，但您可以连接到您选择的用户并创建类型。
Creating type in DB:
在 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:
一旦我们创建了 type NAMESARRAY，如果我们在不更改的情况下执行您的代码，我们将遇到以下错误：
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
java.sql.SQLException: ORA-06550: 第 1 行，第 180 列：
PLS-00382：表达式类型错误 ORA-06550：第 1 行，第 173 列：
PL/SQL：语句被忽略
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();}
}
}
