http://blog.csdn.net/dutguoyi/archive/2007/11/11/1879416.aspx上面的文章中把把STRUCT放在ARRAY中传给plsql function,
现在我想直接把STRUCT传给plsql function, 但是报错:
ORA-06550: 第 1 行, 第 36 個欄位:
PLS-00103: 發現了符號 ";" 當您等待下列事項之一發生時: . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
<指數 (**)> <> or != or ~= >= <= <> and or like like2 like4
likec between || indicator multiset member submultiset
符號 ")" 取代了 ";" 才可以繼續作業. (注:系统是繁体)attach code:private void init2() throws SQLException{
Connection conn = GetConnection.getConn();//取得数据库连接,代码省略
CallableStatement cstmt = null;
ResultSet rs = null;
final String CMD6OBJECT = "CMD6OBJECT";
// private static final String CMD6ARRAY = "CMD6ARRAY";
final String UTIL_DATAARRAY = "UTIL_DATAARRAY";
try{
Vector data = new Vector();
Object[] arr = new Object[]{"生活","工作","hello","1234"};
data.add(arr);
data.add(CommandName.RETURNTYPE_ORACLECURSOR);
for(int i=0;i<data.size()-1;i++){
Object[] objArr = (Object[]) data.elementAt(i);
if(objArr.length<10){
Object[] objArr2 = new Object[10];
System.arraycopy(objArr, 0, objArr2, 0, objArr.length);
for(int j=objArr.length;j<objArr2.length;j++){
objArr2[j] = "";
}
data.set(i, objArr2);
}
objArr = (Object[])data.elementAt(i);
/*for(int j=0;j<objArr.length;j++){
System.out.println(objArr[i].toString());
}*/
}
int returnType = (Integer)data.get(data.size()-1);//最后一個傳入的參數是返回類型
String param = "";
if(data.size()==1){
param = "(?)";
}else{
param = "(?";
for(int i=1;i<data.size()-1;i++){
param += ",?";
}
param += ",?";
}
String sql = "{? = call HiwayTest.test8"+param+"}";
cstmt = conn.prepareCall(sql);
if(returnType==CommandName.RETURNTYPE_JAVAARRAY){
cstmt.registerOutParameter(1, OracleTypes.ARRAY,UTIL_DATAARRAY);
}
if(returnType==CommandName.RETURNTYPE_ORACLECURSOR){
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
}
StructDescriptor structDesc = new StructDescriptor(CMD6OBJECT,conn);
for(int i=0;i<data.size()-1;i++){
oracle.sql.STRUCT struct = new oracle.sql.STRUCT(structDesc,conn,(Object[])data.elementAt(i));
cstmt.setObject(i+2, struct);
}
cstmt.setString(data.size()+1, "system");//最后一個參數是loginID
cstmt.execute();
Vector result = new Vector();
if(returnType==CommandName.RETURNTYPE_ORACLECURSOR){
rs = (ResultSet) cstmt.getObject(1);
ResultSetMetaData metaData = rs.getMetaData();
Vector header = new Vector();
for(int i=1;i<=metaData.getColumnCount();i++){//oracle下標從1開始
header.add(metaData.getColumnLabel(i));
}
result.add(header);
while(rs.next()){
Vector row = new Vector();
for(int i=1;i<=metaData.getColumnCount();i++){
row.add(rs.getObject(i));
}
result.add(row);
}
}
if(returnType==CommandName.RETURNTYPE_JAVAARRAY){
Array Arr = cstmt.getArray(1);
String[] strArr = (String[]) Arr.getArray();
result.add("返回的是java數組");
result.add(strArr);
}
System.out.println("執行結果:"+result);
if(rs!=null){
rs.close();
rs = null;
}
if(cstmt!=null){
cstmt.close();
cstmt = null;
}
ResultVO rvo = new ResultVO(result,null,null,null);
}catch(Exception e){
e.printStackTrace();
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw));
if(rs!=null){
rs.close();
rs = null;
}
if(cstmt!=null){
cstmt.close();
cstmt = null;
}
}finally{
conn.close();
}
}function test8(vArr in cmd6object,vLoginID in varchar2) return report_row_type --report_row_type是sys_refcursor类型
is
result report_row_type;
begin
open result for
select 'OK' from dual;
return result;
end;
多谢!!!
现在我想直接把STRUCT传给plsql function, 但是报错:
ORA-06550: 第 1 行, 第 36 個欄位:
PLS-00103: 發現了符號 ";" 當您等待下列事項之一發生時: . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
<指數 (**)> <> or != or ~= >= <= <> and or like like2 like4
likec between || indicator multiset member submultiset
符號 ")" 取代了 ";" 才可以繼續作業. (注:系统是繁体)attach code:private void init2() throws SQLException{
Connection conn = GetConnection.getConn();//取得数据库连接,代码省略
CallableStatement cstmt = null;
ResultSet rs = null;
final String CMD6OBJECT = "CMD6OBJECT";
// private static final String CMD6ARRAY = "CMD6ARRAY";
final String UTIL_DATAARRAY = "UTIL_DATAARRAY";
try{
Vector data = new Vector();
Object[] arr = new Object[]{"生活","工作","hello","1234"};
data.add(arr);
data.add(CommandName.RETURNTYPE_ORACLECURSOR);
for(int i=0;i<data.size()-1;i++){
Object[] objArr = (Object[]) data.elementAt(i);
if(objArr.length<10){
Object[] objArr2 = new Object[10];
System.arraycopy(objArr, 0, objArr2, 0, objArr.length);
for(int j=objArr.length;j<objArr2.length;j++){
objArr2[j] = "";
}
data.set(i, objArr2);
}
objArr = (Object[])data.elementAt(i);
/*for(int j=0;j<objArr.length;j++){
System.out.println(objArr[i].toString());
}*/
}
int returnType = (Integer)data.get(data.size()-1);//最后一個傳入的參數是返回類型
String param = "";
if(data.size()==1){
param = "(?)";
}else{
param = "(?";
for(int i=1;i<data.size()-1;i++){
param += ",?";
}
param += ",?";
}
String sql = "{? = call HiwayTest.test8"+param+"}";
cstmt = conn.prepareCall(sql);
if(returnType==CommandName.RETURNTYPE_JAVAARRAY){
cstmt.registerOutParameter(1, OracleTypes.ARRAY,UTIL_DATAARRAY);
}
if(returnType==CommandName.RETURNTYPE_ORACLECURSOR){
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
}
StructDescriptor structDesc = new StructDescriptor(CMD6OBJECT,conn);
for(int i=0;i<data.size()-1;i++){
oracle.sql.STRUCT struct = new oracle.sql.STRUCT(structDesc,conn,(Object[])data.elementAt(i));
cstmt.setObject(i+2, struct);
}
cstmt.setString(data.size()+1, "system");//最后一個參數是loginID
cstmt.execute();
Vector result = new Vector();
if(returnType==CommandName.RETURNTYPE_ORACLECURSOR){
rs = (ResultSet) cstmt.getObject(1);
ResultSetMetaData metaData = rs.getMetaData();
Vector header = new Vector();
for(int i=1;i<=metaData.getColumnCount();i++){//oracle下標從1開始
header.add(metaData.getColumnLabel(i));
}
result.add(header);
while(rs.next()){
Vector row = new Vector();
for(int i=1;i<=metaData.getColumnCount();i++){
row.add(rs.getObject(i));
}
result.add(row);
}
}
if(returnType==CommandName.RETURNTYPE_JAVAARRAY){
Array Arr = cstmt.getArray(1);
String[] strArr = (String[]) Arr.getArray();
result.add("返回的是java數組");
result.add(strArr);
}
System.out.println("執行結果:"+result);
if(rs!=null){
rs.close();
rs = null;
}
if(cstmt!=null){
cstmt.close();
cstmt = null;
}
ResultVO rvo = new ResultVO(result,null,null,null);
}catch(Exception e){
e.printStackTrace();
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw));
if(rs!=null){
rs.close();
rs = null;
}
if(cstmt!=null){
cstmt.close();
cstmt = null;
}
}finally{
conn.close();
}
}function test8(vArr in cmd6object,vLoginID in varchar2) return report_row_type --report_row_type是sys_refcursor类型
is
result report_row_type;
begin
open result for
select 'OK' from dual;
return result;
end;
多谢!!!
解决方案 »
- i++问题
- 如何将主窗体参数传递给对话框?
- HELP ,我都急疯了
- 请高手讲讲命令模式.(最好结合代码例子,简单易懂点的例子)
- 绘制图形时 (paint 方法) 如何出现滚动条
- 关于日文排序
- installanywhere打包问题彻底解决!foryouever进来领分,100全给你!!
- 给新手一点建议
- 请帮忙,调用可执行文件的问题!
- :在窗口中设置一个“形状”菜单,包括三个“圆形”“扇形”、“矩 形”菜单项,点击菜单顶,在窗口中出现相应的形状(实心),设置 ||/| | 个列列表框 包括三个
- Java 实现 byte[] 写入到流。
- A question about displaying image in JTable cell
Object[] objArr2 = new Object[10];
System.arraycopy(objArr, 0, objArr2, 0, objArr.length);
for(int j=objArr.length;j<objArr2.length;j++){
objArr2[j] = "";
}
data.set(i, objArr2);
由于CMD6OBJECT是这样定义的:create or replace type cmd6Object as object(col1 varchar2(100),col2 varchar2(100),col3 varchar2(100)
,col4 varchar2(100),col5 varchar2(100),col6 varchar2(100),col7 varchar2(100),
col8 varchar2(100),col9 varchar2(100),col10 varchar2(100))因此对传入的参数不足10位的补""