`

Dose oracle.sql.ArrayDescriptor support the user defined type in package?

阅读更多

Not all PL/SQL arrays can be direcly accessed using JDBC. Procedures or Functions that take arrays and are declared inside packages instead of with “CREATE TYPE” can not be called using JDBC. This is a limitation of Oracle.
Approach to solve this issue:
1. Write an additonal wrapper procedure that converts an array created with CREATE TYPE into the format required by your procedure
If you can not change the procedure itself  or it is already used by a large base of existing code you may have to go with option 2 of writing an additional wrapper procedure that takes parameters usable by JDBC:

Original Code: 
Create or Replace Package tms_user_autocode IS
Type DeriveValuesREC IS RECORD (
DefLevelId number(10),
ColumnName VARCHAR2(30),
ValueText tms_dict_contents.term%TYPE,
ClassificationCode VARCHAR2(1));

Type DeriveValuesTAB is TABLE of DeriveValuesREC INDEX BY BINARY_INTEGER;

FUNCTION ClassifyTerm(
pDefDictionaryId     IN  NUMBER
pDefDomainId         IN  NUMBER
pTerm                IN  VARCHAR2
pSourceTermId        IN  NUMBER
pOccurrenceId        IN  NUMBER
pSourceTermAltKey    IN  VARCHAR2
pNoOmissionFlag      IN  VARCHAR2
pDefIntegrationKey   IN  VARCHAR2
pDefInstanceName     IN  VARCHAR2
pXArea       IN  NUMBER
pExtValue1           IN  VARCHAR2
pExtValue2           IN  VARCHAR2
pExtValue3           IN  VARCHAR2
pExtValue4           IN  VARCHAR2
pExtValue5           IN  VARCHAR2
pExtValue6           IN  VARCHAR2
pExtValue7           IN  VARCHAR2
pExtValue8           IN  VARCHAR2
pOmissionStatus      IN OUT VARCHAR2
pOmissionOwner       IN OUT VARCHAR2
pActionText          IN OUT VARCHAR2
pVTAid               OUT NUMBER
pSearchID            OUT NUMBER
pDeriveValues        IN OUT tms_user_autocode.DeriveValesTAB) RETURN PLS_INTEGER

END tms_user_autocode;
Wrapper Procedure:

CREATE OR REPLACE
PROCEDURE CTRM_tms_user_autocode
(jDefDictionaryId     IN  NUMBER,
jDefDomainId         IN  NUMBER,
jTerm                IN  VARCHAR2,
jSourceTermId        IN  NUMBER,
jOccurrenceId        IN  NUMBER,
jSourceTermAltKey    IN  VARCHAR2,
jNoOmissionFlag      IN  VARCHAR2,
jDefIntegrationKey   IN  VARCHAR2,
jDefInstanceName     IN  VARCHAR2,
jXArea       IN  NUMBER,
jExtValue1           IN  VARCHAR2,
jExtValue2           IN  VARCHAR2,
jExtValue3           IN  VARCHAR2,
jExtValue4           IN  VARCHAR2,
jExtValue5           IN  VARCHAR2,
jExtValue6           IN  VARCHAR2,
jExtValue7           IN  VARCHAR2,
jExtValue8           IN  VARCHAR2,
jOmissionStatus      IN OUT VARCHAR2,
jOmissionOwner       IN OUT VARCHAR2,
jActionText          IN OUT VARCHAR2,
jSearchID            OUT NUMBER,
jVTAid               OUT NUMBER,
jDeriveValues        IN OUT CTRM_DeriveValuesTAB
returnValue       OUT Number) AS

l_array tms_user_autocode.DeriveValuesTAB;
l_record tms_user_autocode.DeriveValuesREC;
 
  BEGIN
— Load our JDBC table into the PL/SQL one… 
  l_array.delete;
  FOR i IN jDeriveValues.FIRST
        .. jDeriveValues.LAST LOOP
  l_record := NULL;

  l_record.DefLevelId  := jDeriveValues(i).DefLevelId;
  l_record.ColumnName := jDeriveValues(i).ColumnName;
  l_record.ValueText := jDeriveValues(i).ValueText;
  l_record.ClassificationCode := jDeriveValues(i).ClassificationCode;
  l_array(i) := l_record;
 
  END LOOP;
 returnValue := tms.TMS_user_AUTOCODE.ClassifyTerm(
              jDefDictionaryId
            , jDefDomainId
            , jTerm
            , jSourceTermId
            , jOccurrenceId
            , jSourceTermAltKey
            , jNoOmissionFlag
            , jDefIntegrationKey
            , jDefInstanceName
            , jXArea
            , jExtValue1
            , jExtValue2
            , jExtValue3
            , jExtValue4
            , jExtValue5
            , jExtValue6
            , jExtValue7
            , jExtValue8
            , jOmissionStatus
            , jOmissionOwner
            , jActionText
            , jSearchId
            , jVTAid
            ,l_array
            );
           
  FOR i IN l_array.FIRST .. l_array.LAST LOOP
    jDeriveValues(i).DefLevelId   := l_array(i).DefLevelId;
    jDeriveValues(i).ColumnName  := l_array(i).ColumnName;
    jDeriveValues(i).ValueText  := l_array(i).ValueText;
    jDeriveValues(i).ClassificationCode  := l_array(i).ClassificationCode;
   
  END LOOP;

END;

Java program :

 import java.sql.*;

import oracle.jdbc.OracleTypes;
 import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
 
 public class Test {
  public static void main(String[] args) throws SQLException {
   CallableStatement proc = null;
     try {
      Connection conn=null;
 DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
 //Establish a connection
  conn = DriverManager.getConnection
     (“jdbc:oracle:oci8:@123.WORLD”,
                                   “scott”, “tiger);
  System.out.println(“connection success0″);
 
  int DictId = 8;
  int DomainId = 1;
  String Term = “BLOOD”;
  int SourceId=0;
  String pOccurrenceId=”0″;
  String pSourceTermAltKey =””;
  String pDefIntegrationKey=”CTRM”;
  String pDefInstanceName=”OCP451.GROTON.PFIZER.COM”;
  int pXArea=1;
  String Flag=”N”;
  String pExtValue1=””;
  String pExtValue2=””;
  String pExtValue3=””;
  String pExtValue4=””;
  String pExtValue5=””;
  String pExtValue6=””;
  String pExtValue7=””;
  String pExtValue8=””;
  String ActionText=””;
  int returnValue=0;
  int sid=0;
  String oStat=””;
  String oOwner=””;
  //String xsystem=””;
  int vtaid=0;
 
 
   // create the ARRAY by calling the constructor
  

    System.out.println(“connection success1.1″);
//First, declare the Object arrays that will store the data.
  Object [] p1recobj = {new Integer(811),”TERM”,””,””};
  Object [] p2recobj  = {new Integer(811),”DICT_CONTENT_CODE”,””,””};
  Object [] p3recobj = {new Integer(812),”TERM”,””,””};
  Object [] p4recobj = {new Integer(812),”DICT_CONTENT_CODE”,””,””};
  System.out.println(“connection success1.2″);
//Declare the Object Arrays to hold the STRUCTS.
  Object [] p1arrobj;
  //Object [] p2arrobj;
  System.out.println(“connection success1.3″);
  //  Declare two descriptors, one for the ARRAY TYPE
  //  and one for the OBJECT TYPE.
  StructDescriptor desc1 = StructDescriptor.createDescriptor(“CTRM_DERIVEVALUESREC”, conn);
  ArrayDescriptor desc2 = ArrayDescriptor.createDescriptor(“CTRM_DERIVEVALUESTAB”, conn);
  System.out.println(“connection success1.4″);
  //  Create the STRUCT objects to associate the host objects
  //  with the database records.
     STRUCT p1struct = new STRUCT(desc1,conn,p1recobj);
     STRUCT p2struct = new STRUCT(desc1,conn,p2recobj);
     STRUCT p3struct = new STRUCT(desc1,conn,p3recobj);
     STRUCT p4struct = new STRUCT(desc1,conn,p4recobj);
     System.out.println(“connection success1.5″);
  //  Initialize the Input array object – to an array of STRUCT Objects.
     p1arrobj = new Object []{p1struct,p2struct,p3struct,p4struct};
     System.out.println(“connection success1.6″);
  //  Set up the ARRAY object.
     ARRAY  p1arr = new ARRAY(desc2,conn,p1arrobj);
   //  ARRAY  p2arr;
     System.out.println(“connection success1.7″); 
  proc = conn.prepareCall(“{ call CTRM_tms_user_autocode(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}”);
     System.out.println(“connection success1.8″);    
  proc.setInt(1, DictId);
  proc.setInt(2, DomainId);
  proc.setString(3, Term);
  proc.setInt(4, SourceId);
  proc.setString(5, pOccurrenceId);
  proc.setString(6, pSourceTermAltKey);
  proc.setString(7, Flag);
  proc.setString(8, pDefIntegrationKey);
  proc.setString(9, pDefInstanceName);
  proc.setInt(10, pXArea);
  proc.setString(11, pExtValue1);
  proc.setString(12, pExtValue2);
  proc.setString(13, pExtValue3);
  proc.setString(14, pExtValue4);
  proc.setString(15, pExtValue5);
  proc.setString(16, pExtValue6);
  proc.setString(17, pExtValue7);
  proc.setString(18, pExtValue8);
  proc.setString(19, oStat);
  proc.setString(20, oOwner);
  proc.setString(21, ActionText);
  proc.setInt(22,sid);
  proc.setInt(23, vtaid);
  proc.setArray(24,p1arr);
  proc.setInt(25,returnValue);
//Registering OUT parameter
  proc.registerOutParameter (19, Types.VARCHAR);
  proc.registerOutParameter (20, Types.VARCHAR);
  proc.registerOutParameter (21, Types.VARCHAR);
  proc.registerOutParameter (22, Types.INTEGER);
  proc.registerOutParameter (23, Types.INTEGER);
  proc.registerOutParameter(24,OracleTypes.ARRAY,”CTRM_DERIVEVALUESTAB”);
  proc.registerOutParameter (25, Types.INTEGER);
  System.out.println(“connection success1.9″);
  proc.execute();
  System.out.println(“connection success2″);
//  Associate the returned arrays with the ARRAY objects.
  oStat=proc.getString(19);
  oOwner=proc.getString(20);
  ActionText=proc.getString(21);
  sid = proc.getInt(22);
  vtaid = proc.getInt(23);
  p1arr = (ARRAY) proc.getArray(24);
  returnValue=proc.getInt(25);
  System.out.println(“connection success2.1″);
//Get the data back into the data arrays.
  p1arrobj = (Object [])p1arr.getArray();
  System.out.println(“connection success2.2″);
//  Get the data records from each array element (which is of type STRUCT).
  p1recobj = ((STRUCT)p1arrobj[0]).getAttributes();
  p2recobj = ((STRUCT)p1arrobj[1]).getAttributes();
  p3recobj = ((STRUCT)p1arrobj[2]).getAttributes();
  p4recobj = ((STRUCT)p1arrobj[3]).getAttributes();
  System.out.println(“connection success2.3″);
//Show the results:
  System.out.println(“returnValue “+returnValue);
  System.out.println(“ActionText “+ActionText);
  System.out.println(“OmissStat “+oStat);
  System.out.println(“OmissOwner “+oOwner);
  System.out.println(“SearchID “+sid);
  System.out.println(“VTAID “+vtaid);
  System.out.println(“First Object is now “+p1recobj[0]+” and “+p1recobj[1]+” and “+p1recobj[2]+” and “+p1recobj[3]);
  System.out.println(”                    “+p2recobj[0]+” and “+p2recobj[1]+” and “+p2recobj[2]+” and “+p2recobj[3]);
  System.out.println(“Second Object is now “+p3recobj[0]+” and “+p3recobj[1]+” and “+p3recobj[2]+” and “+p3recobj[3]);
  System.out.println(”                    “+p4recobj[0]+” and “+p4recobj[1]+” and “+p4recobj[2]+” and “+p4recobj[3]);
 

  
         conn.commit();
 
     } catch (Exception e) {
        
         System.out.println(“e>”+e
     
     } finally {
         if (proc != null)
             proc.close();
     }
 }
}

ArrayDescriptor.createDescriptor(type, conn);
schema级别对象类型是可以的
只是单独的名字,默认前面是schema,但是不能加package,应该是oracle jdbc里面代码的问题
解决的办法只有对package.type建立一个同义词了(尝试下同义词)
要么你就用schema级别的对象(自定义类型放在包外定义),jdbc这点不是很好
 
这是一个长久以来的缺陷
To my knowledge, the Oracle JDBC driver does not support using the ArrayDescriptor for array data types (varray or nested table) that are defined inside of a package. The same is true for StructDescriptor as well. If you want to use array and object data types, you must define them outside of a package. Then you'll be able to use the descriptors in your JDBC programs.
 
As far as I know, you can only create an "ArrayDescriptor" and a "StructDescriptor" for database types.
In other words, types that were created using the CREATE TYPE (DDL) statement.
You cannot create an "ArrayDescriptor" or a "StructDescriptor" for types created in PL/SQL packages.

 

https://community.oracle.com/thread/845161

http://arwaheem.wordpress.com/2008/02/23/java-passing-arrays-to-a-stored-procedure-with-plsql-table-type/

http://blog.csdn.net/rocgege/article/details/7947078

分享到:
评论

相关推荐

    解决Android Studio安装后运行出错dose not...和Internal error...

    从提供的文件内容来看,主要涉及的是如何解决Android Studio在安装后运行时出现的两种常见错误:“dose not point to a valid jvm installation”和“Internal error...”。错误的具体内容和解决步骤如下: 1. ...

    sqlserver2008安装报语言不符的解决方法

    安装SqlServer2008R2遇到系统提示: 代码如下:SQL Server setup media does not support the language of the OS or does not have ENU localized files.Use the matching language-specific SQL Server media or ...

    BSD Hacks.pdf

    5. **Using the Mouse at a Terminal (HACK 5):** This hack explores how to enable and utilize mouse support in terminal emulators, which can greatly enhance the user experience by allowing mouse-based ...

    Ansys_ Fluent_in_Ansys_Workbench_Users_Guide 2022 R1.pdf

    * 版权所有:ANSYS, Inc. * 作者:ANSYS, Inc. * 发布日期:2022 年 1 月 * ISBN:无 * 分类号:TF * 主题词:计算流体力学,CFD,Ansys Fluent,Ansys Workbench 结语: Ansys Fluent 在 Ansys Workbench 用户...

    Python库 | dose-1.2.1-py2.py3-none-any.whl

    python库。 资源全名:dose-1.2.1-py2.py3-none-any.whl

    1_dose_escalation_simulations.R.r

    1_dose_escalation_simulations.R.r

    Dose calculations for external photon beams in radiotherapy

    放射治疗中的外部光子束剂量计算是医学物理领域的一个核心课题,主要涉及肿瘤治疗中射线能量分布的精确预测和优化。在这个过程中,理解和掌握相关的IT技术至关重要,因为它们直接影响到治疗的效果和患者的安全。...

    10. 英语语音绕口令.doc

    Some early birds run to eat the worms in the sun."中的"ar"和"oo"音,例如在"early"和"birds"中,要求舌头中部接近口腔中央,发音饱满。 3. **Back Vowels(后元音)**: "12. The car which Mark borrowed from...

    Economical Writing

    A dose of McCloskey banishes the dismal from the 'dismal science.' McCloskey is the Strunk and White of economics, and Economic Writing should be required reading for all economists." -- Claudia Gold...

    Experimental low-level direct current therapy in liver metastases: influence of polarity and current dose

    Experimental low-level direct current therapy in liver metastases: influence of polarity and current dose Bioelectromagnetics 21:395^401 (2000) Experimental Low-Level Direct Current Therapy in ...

    BSD HACKS -- 100个业界最尖端的技巧和工具

    Credits About the Author Contributors Acknowledgments Preface Why BSD Hacks? How to Use this Book How This Book Is Organized Conventions Used in This Book Using ...

    英文原版-Learning Radiology Recognizing the Basics 3rd Edition

    Learn the latest on ultrasound, MRI, CT, patient safety, dose reduction, radiation protection, and more, in a time-friendly format with brief, bulleted text and abundant high-quality images....

    新译林8B英语UNIT3单元知识点归纳与练习题集.doc

    同时,区分了询问外貌、品质和喜好的三种表达方式:What do/dose sb. look like?(问外貌),What be sb. like?(问品质),What do/does sb. like?(问喜好)。 2. "chat":动词,表示聊天。搭配短语有"chat with ...

    数字生物模拟环境(DOSE)___下载.zip

    《数字生物模拟环境(DOSE):探索生命科学的数字化工具》 数字生物模拟环境(DOSE)是一个创新的软件平台,专为生物学家、生物物理学家、生物化学家以及相关领域的研究者设计,它允许用户在计算机上模拟生物系统的行为...

    近十年(99~09)考研英语阅读

    ②In several of the studies, when stressed-out female rats had their ovaries (the female reproductive organs) removed, their chemical responses became equal to those of the males. ③Adding to a woman...

    Dose-Response曲线步骤方法.doc

    剂量-反应曲线(Dose-Response Curve)是生物医学、药理学以及毒理学等领域中常用的一种统计分析工具,用于研究药物或刺激物对生物体产生的效应与剂量之间的关系。这种曲线可以帮助我们理解不同剂量的物质如何影响...

Global site tag (gtag.js) - Google Analytics