oracle 传递table,将对象类型表从java传递到oracle 8i(Pass table of object type from java to oracle 8i)...

将对象类型表从java传递到oracle 8i(Pass table of object type from java to oracle 8i)

我需要将一个对象列表(如值表)从java传递给oracle 8i。

我正在尝试使用oracle 8i中的以下数据库对象。

这可能在8i？

如果可能的话，请帮我解决一下。

create or replace type GROUP_OBJ as object (

GROUP_TYPE VARCHAR2(32),

PARAM_1 VARCHAR2(16),

PARAM_2 VARCHAR2(16)

);

/

CREATE OR REPLACE

TYPE GROUP_table AS TABLE OF GROUP_OBJ;

/

CREATE OR REPLACE PROCEDURE GROUP_TABLE_TEST (TABLE_OBJ IN GROUP_table)

IS

BEGIN

null;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK;

END GROUP_TABLE_TEST;

I need to pass a list of objects (like a table of values) from java to oracle 8i.

I am trying with the below database objects in oracle 8i.

Is this possible in 8i?

Please help me with a solution if possible.

create or replace type GROUP_OBJ as object (

GROUP_TYPE VARCHAR2(32),

PARAM_1 VARCHAR2(16),

PARAM_2 VARCHAR2(16)

);

/

CREATE OR REPLACE

TYPE GROUP_table AS TABLE OF GROUP_OBJ;

/

CREATE OR REPLACE PROCEDURE GROUP_TABLE_TEST (TABLE_OBJ IN GROUP_table)

IS

BEGIN

null;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK;

END GROUP_TABLE_TEST;

原文：https://stackoverflow.com/questions/20703898

更新时间：2019-09-20 18:59

最满意答案

我得到了答案。 由于区分大小写问题我遇到了麻烦。 要传递给Structdescriptor和arraydescriptor的类型名称区分大小写。 当两者都给出了大写字母时，它起作用了。

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.SQLException;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import oracle.sql.STRUCT;

import oracle.sql.StructDescriptor;

public class PassTableOfObject {

public static void main(String[] args) throws SQLException {

Connection conn=FucntionTest.getConnection();

try{

StructDescriptor itemDescriptor = StructDescriptor

.createDescriptor("GROUP_OBJ", conn);

STRUCT[] structs = new STRUCT[2];

for (int index = 0; index < 2; index++)

{

String[] params = new String[2];

params[0] = "Group "+index;

params[1] = "Param 1 "+index;

STRUCT struct = new STRUCT(itemDescriptor, conn, params);

structs[index] = struct;

}

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("GROUP_TABLE", conn);

ARRAY oracleArray = new ARRAY(desc, conn, structs);

CallableStatement cs = null;

cs = conn.prepareCall("{call GROUP_TABLE_TEST(?)}");

cs.setArray(1, oracleArray);

cs.execute();

conn.commit();

System.out.println("insert procedure executed successfully");

}catch(SQLException e){

e.printStackTrace();

}finally{

conn.close();

}

}

}

I got the answer. I was getting into trouble due to case sensitivity issue. the type name to pass into Structdescriptor and arraydescriptor is case sensitive. when gave the Upper case for both , it worked.

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.SQLException;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import oracle.sql.STRUCT;

import oracle.sql.StructDescriptor;

public class PassTableOfObject {

public static void main(String[] args) throws SQLException {

Connection conn=FucntionTest.getConnection();

try{

StructDescriptor itemDescriptor = StructDescriptor

.createDescriptor("GROUP_OBJ", conn);

STRUCT[] structs = new STRUCT[2];

for (int index = 0; index < 2; index++)

{

String[] params = new String[2];

params[0] = "Group "+index;

params[1] = "Param 1 "+index;

STRUCT struct = new STRUCT(itemDescriptor, conn, params);

structs[index] = struct;

}

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("GROUP_TABLE", conn);

ARRAY oracleArray = new ARRAY(desc, conn, structs);

CallableStatement cs = null;

cs = conn.prepareCall("{call GROUP_TABLE_TEST(?)}");

cs.setArray(1, oracleArray);

cs.execute();

conn.commit();

System.out.println("insert procedure executed successfully");

}catch(SQLException e){

e.printStackTrace();

}finally{

conn.close();

}

}

}

2013-12-21

相关问答

我使用ArrayDescriptor解决了我的问题： //Need to create connection

ArrayDescriptor desArray = ArrayDescriptor.createDescriptor("XPTO.TABLE_XPTO", conn);

//I don't need to send information for procedure

ARRAY array_to_pass = new ARRAY(desArray, conn, null);

...

...

以下是一种方法： select t.type as "Type"

, sum(case when t.status = 'A' then 1 else 0 end) as "Count A"

, sum(case when t.status = 'I' then 1 else 0 end) as "Count I"

, sum(case when t.status = 'F' then

...

我不相信服务器版本对您可以使用的DBD :: Oracle版本有任何影响，只是您安装的客户端库版本。 Oracle客户端库的9.2,10.1和10.2版本支持连接到Oracle服务器8.1.7.4，而最新版本的DBD :: Oracle仍然与9.2以上的所有客户端库兼容，所以我认为你不会实际上有任何问题。 但是，如果安装版本11客户端，则将失去连接到低于9.2.0的服务器版本的能力。 I don't believe that the server version has any bearing o

...

我得到了答案。 由于区分大小写问题我遇到了麻烦。 要传递给Structdescriptor和arraydescriptor的类型名称区分大小写。 当两者都给出了大写字母时，它起作用了。 import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.SQLException;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

impo

...

如果在类路径中不包含orai18n.jar，并且数据库字符集不在此列表中，则会发生这种情况：ASCII，UTF8或ISOLATIN1。 所有的字符都替换为“？”的“替换字符”。 您可以从OTN下载orai18n.jar(在谷歌搜索“oracle jdbc download”)。 This happens when you don't include orai18n.jar in your classpath and when the database character set is not in

...

不要将所有字符串组合在一起。 Don't combine the, strings put all in one.

Connection connection = DriverManager.getConnection(url);

DatabaseMetaData meta = connection.getMetaData();

String product = meta.getDatabaseProductName();

String major = meta.getDatabaseMajorVersion();

String minor = meta.getDatabaseMinorVersion();

...

您可以查询数据字典： SELECT 'CREATE PUBLIC DATABASE LINK "'||DB_LINK||'" CONNECT TO '||USERNAME||' IDENTIFIED BY "" USING '''||HOST||''';' AS cmd

FROM DBA_DB_LINKS

WHERE owner = 'PUBLIC'

AND DB_LINK = 'LINKNAME';

如果您错过了信息，请尝试选择表sys.link$ 。 You can que

...

使用(+)运算符： select users.user_id, users.group_id, users.shift, pi."Picks", pi."Pick Volume", pu."Putaways", pu."Putaway Volume", re."Relocates", re."Relocate Volume"

from users, pi, pu, re

where users.user_id = pi.user_id(+)

and users.user_id = pu.user_

...

这样做了 INSERT INTO

COLLECTION_TABLE

values (2, COLLECTION_01(

dept_typ_01(

(PERSON_TYP ('name1','ssn1', 'addr2')),

(PERSON_TYP ('name2','ssn2', 'addr2'))

)

)

);

This did the trick INSERT INTO

COLLE

...