8 Replies Latest reply: Jul 12, 2011 8:35 AM by 874921 RSS

    how to call stored procedure having parameter as oracle type from java???

    873859

      Hello,
      I have created following type & stored procedure in oracle. How can i call this stored procedure from my java class?
      I want to pass 2d array to this procedure.


      CREATE OR REPLACE TYPE type_survey AS OBJECT ( emp_id number,emp_name varchar(100));
      /


      CREATE OR REPLACE TYPE tbl_survey AS VARRAY(100) OF type_survey;
      /



      CREATE OR REPLACE PROCEDURE INSERTEMP (pp in tbl_survey)
      IS

      BEGIN

      FOR I IN pp.FIRST .. pp.LAST
      LOOP


      INSERT INTO SURVEY (id) VALUES (pp(I).emp_id);
      END LOOP;

      END;
      /

        • 1. Re: how to call stored procedure having parameter as oracle type from java???
          836548
          CREATE OR REPLACE TYPE type_survey AS OBJECT ( emp_id number,emp_name varchar(100));
          /
          Using StructDescriptor create a descriptor for above type 'type_survey'

          Example:

          StructDescriptor desc1=StructDescriptor.createDescriptor("type_survey",connObject);
               STRUCT p1struct1 = new STRUCT(desc1,connObject,p1obj);

          >
          CREATE OR REPLACE TYPE tbl_survey AS VARRAY(100) OF type_survey;
          /
          Using ArrayDescriptor create a descriptor for 'tbl_survey' array.

          Example :
          ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("TAB_TY_EMP",connObject);
          ARRAY array = new ARRAY(arraydesc,connObject,obj_array);

          >
          CREATE OR REPLACE PROCEDURE INSERTEMP (pp in tbl_survey)
          IS

          BEGIN

          FOR I IN pp.FIRST .. pp.LAST
          LOOP


          INSERT INTO SURVEY (id) VALUES (pp(I).emp_id);
          END LOOP;

          END;
          /
          Call the above proc as below:

          CallableStatement cstmt = connObject.prepareCall("{ call p_getter(?)}");
                    cstmt.setObject(1,array);
                    cstmt.execute();

          To compile and execute this program you will require ojdbc14 or ojdbc6.jar.
          • 2. Re: how to call stored procedure having parameter as oracle type from java???
            873859
            Hi,
            I have written the code as you said....but it is showing error as follows....

            Exception in thread "main" java.sql.SQLException: Inconsistent java and sql object types

                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)

                 at oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:706)

                 at oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1375)

                 at oracle.sql.STRUCT.<init>(STRUCT.java:159)

                 at com.flologic.ArrayDemo.passArray(ArrayDemo.java:24)

                 at com.flologic.ArrayDemo.main(ArrayDemo.java:47)


            my code is.............

            public static void passArray() throws SQLException
            {     
                 Connection conn=null;
                 try{
                      conn=getOracleConnection();//this method returns connection object
                 }
                 catch (Exception e)      
                 {
                      e.printStackTrace();
                 }
                 int intArray[][] = {{ 1,2},{3,4},{5,6} };

            StructDescriptor desc1=StructDescriptor.createDescriptor("TYPE_SURVEY",conn);
            STRUCT p1struct1 = new STRUCT(desc1,conn,intArray);


            ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("TBL_SURVEY",conn);
            ARRAY array = new ARRAY(arraydesc,conn,intArray);


            CallableStatement cstmt = conn.prepareCall("{ call INSERTEMP(?,?)}");
            cstmt.setObject(1,array);
            cstmt.execute();
            }
            • 3. Re: how to call stored procedure having parameter as oracle type from java???
              gimbal2
              First of all :
              java.sql.SQLException: Inconsistent java and sql object types
              You pass a 2D array of ints and what I see in your Oracle definition is a 1D array of 'type_survey' objects?


              Secondly:
              CallableStatement cstmt = conn.prepareCall("{ call INSERTEMP(?,?)}");
              cstmt.setObject(1,array);
              cstmt.execute();
              Two parameters expected (two question marks), one passed?
              • 4. Re: how to call stored procedure having parameter as oracle type from java???
                836548
                TYPE_SURVEY is of type int and String.

                Remember you have to follow the definations of type.

                in earlier post you have mentioned as proc created with one parameter, if so then the proc calling line is also having error.

                And when you post the code, highlight the line it is giving exception.
                • 5. Re: how to call stored procedure having parameter as oracle type from java???
                  873859
                  CREATE OR REPLACE TYPE type_survey AS OBJECT ( emp_id number,emp_name varchar(100));
                  /


                  CREATE OR REPLACE TYPE tbl_survey AS VARRAY(100) OF type_survey;
                  /




                  CREATE OR REPLACE PROCEDURE APP_DATA.INSERTEMP (pp in tbl_survey,result out varchar)
                  IS

                  BEGIN

                  FOR I IN pp.FIRST .. pp.LAST
                  LOOP


                  INSERT INTO SURVEY (id) VALUES (pp(I).emp_id);
                  END LOOP;
                  result:='done';
                  END;
                  /






                  public static void passArray() throws SQLException
                  {     
                       Connection conn=null;
                       try{
                            conn=getOracleConnection();//this method returns connection object
                       }
                       catch (Exception e)      
                       {
                            e.printStackTrace();
                       }
                       String[][] val=new String[2][2];
                  *     val[0][0]="1";*
                  *     val[0][1]="aaa";*
                  *     val[1][0]="2";*
                  *     val[1][0]="bbb";*
                       
                       
                  StructDescriptor desc1=StructDescriptor.createDescriptor("TYPE_SURVEY",conn);
                  STRUCT p1struct1 = new STRUCT(desc1,conn,_val_); *//showing error at this line*


                  ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("TBL_SURVEY",conn);
                  ARRAY array = new ARRAY(arraydesc,conn,*p1struct1*);



                  CallableStatement cstmt = conn.prepareCall("{ call INSERTEMP(?,?)}");
                  cstmt.setObject(1,array);
                  cstmt.registerOutParameter(2, OracleTypes.VARCHAR);
                  cstmt.execute();

                  String res=cstmt.getString(2);
                  System.out.println(res);
                  }

                  in the above java code, I have passed 2d array of string to STRUCT constructor and passed STRUCT object i.e. p1struct1  to ARRAY constructor. is it correct? that means @bottom line, I want to pass val array to my stored porcedure. How can i do this? above code is showing following error......



                  Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.String;@146c1d4
                       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
                       at oracle.jdbc.oracore.OracleTypeNUMBER.toNUMBER(OracleTypeNUMBER.java:540)
                       at oracle.jdbc.oracore.OracleTypeNUMBER.toDatum(OracleTypeNUMBER.java:54)
                       at oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:717)
                       at oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1375)
                       at oracle.sql.STRUCT.<init>(STRUCT.java:159)
                       at com.flologic.ArrayDemo.passArray(ArrayDemo.java:29)
                       at com.flologic.ArrayDemo.main(ArrayDemo.java:57)
                  • 6. Re: how to call stored procedure having parameter as oracle type from java???
                    Tolls
                    CREATE OR REPLACE TYPE type_survey AS OBJECT ( emp_id number,emp_name varchar(100));

                    *     String[][] val=new String[2][2];*
                    StructDescriptor desc1=StructDescriptor.createDescriptor("TYPE_SURVEY",conn);
                    STRUCT p1struct1 = new STRUCT(desc1,conn,_val_);

                    How are these two things even vaguely the same?
                    This has been pointed out to you.
                    You have defined a type in Oracle as containing a number and a varchar.
                    You are attempting to make a STRUCT of that type in Java consisting of a pair of String arrays.

                    You should be passing in an Object[], where the first Object is an int and the secind is a String.
                    • 7. Re: how to call stored procedure having parameter as oracle type from java???
                      836548
                           String[][] val=new String[2][2];
                      *     val[0][0]="1";*
                      *     val[0][1]="aaa";*
                      *     val[1][0]="2";*
                      *     val[1][0]="bbb";*
                           
                           
                      StructDescriptor desc1=StructDescriptor.createDescriptor("TYPE_SURVEY",conn);
                      STRUCT p1struct1 = new STRUCT(desc1,conn,_val_); *//showing error at this line*


                      ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("TBL_SURVEY",conn);
                      ARRAY array = new ARRAY(arraydesc,conn,*p1struct1*);
                      instead of above your code should be like this

                      Object [] p1obj = {new Integer(101),new String("Test1")};
                           Object [] p2obj = {new Integer(102),new String("Test2")};
                           Object [] p3obj = {new Integer(103), new String("Test3")};
                           Vector vector = new Vector();

                      StructDescriptor desc1=StructDescriptor.createDescriptor("TYPE_SURVEY",conn);
                           STRUCT p1struct1 = new STRUCT(desc1,conn,p1obj);
                      STRUCT p1struct2 = new STRUCT(desc1,conn,p2obj);
                           STRUCT p1struct3 = new STRUCT(desc1,conn,p3obj);
                           vector.add(p1struct1);
                           vector.add(p1struct2);
                           vector.add(p1struct3);
                                
                           ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("TBL_SURVEY",conn);
                           Object obj_array[] = vector.toArray();
                           ARRAY array = new ARRAY(arraydesc,conn,obj_array);


                      AS mentioned earlier TYPE_SURVEY is of type integer (number) and String (varchar2). so create a object array which holds data of type 'TYPE_SURVEY'

                      Then your created a array 'TBl_SURVEY' which holds objects of type'TYPE_SURVEY', so we have created a ArrayDescriptor and passed the object array of 'TYPE_SURVEY' to it.


                      And in 'INSERTEMP ' proc you've tried to populate the 'SURVEY' table with passed array, so create the 'SURVEY' table.
                      • 8. Re: how to call stored procedure having parameter as oracle type from java???
                        874921
                        Another possibly better and simple way of doing this is to use Object Types in Oracle, and the JPublishertool to generate corresponding stub Java classes. The generated classes have supports for collections as well. These work seamlessly with JDBC, though you may have to use Oracle extensions to JDBC at times. Hope this helps, unless this too late!