4 Replies Latest reply: May 8, 2013 7:21 AM by PhHein RSS

    Object of Objects in Oracle - How to implement in Java

    585159
      {noformat}
      create type course_obj as object ( 
      course_id number,
      course_name varchar2(30)
      );
      
      create type course_tab as table of course_obj;
      
      create type dept_obj as object ( 
          dept_id number,
          dept_name varchar2(30),
          courses  course_tab
      );
      
      create type dept_tab as table of dept_obj;
      
      create table test_course ( 
             course_id number,
             course_name varchar2(30)
      );
      
      create table test_dept ( 
           dept_id number,
           dept_name varchar2(30),
           course_ids varchar2(50) 
      ) ;
      
      create or replace procedure  display_dept (pi_dept_id number ,  pi_dept_details dept_tab  ) 
          is 
          l_course_ids varchar2(150);
          
          begin 
             
              for i in pi_dept_details.first .. pi_dept_details.last 
              loop
                  if pi_dept_details.exists(i)  then 
                    
                     insert into test_dept values ( pi_dept_details(i).dept_id, pi_dept_details(i).dept_name, null);
                     l_course_ids:=null;
                     for j in pi_dept_details(i).courses.first .. pi_dept_details(i).courses.last 
                     loop
                         
                          l_course_ids:=l_course_ids||pi_dept_details(i).courses(j).course_id;
                          insert into test_course values ( pi_dept_details(i).courses(j).course_id, pi_dept_details(i).courses(j).course_name);
                     End loop;
                     
                     update test_dept set course_ids =l_course_ids where dept_id=pi_dept_details(i).dept_id;
                  end if;
              end loop;
              commit;
          End display_dept;
      {noformat}
      ---
      Can anybody pls guide me how to call this procedure ( display_dept) from Java.

      regards,
      Raj Pandit
        • 1. Re: Object of Objects in Oracle - How to implement in Java
          jtahlborn
          you would want to use JDBC to make a call using CallableStatement.
          • 2. Re: Object of Objects in Oracle - How to implement in Java
            585159
            yes

            I coded something like this
            try {
                           // Create CallableStatement
                           conn = ConnectionFactory.getConnection (  );
            
                           cs = conn.prepareCall ( "{call display_dept (?,?)}");
            
                           int deptId=100;
            
                           LOG.debug("deptId : "+ deptId);
            
                           WebSphereNativeJdbcExtractor wsNativeJdbcExtractor = new WebSphereNativeJdbcExtractor ();
                           Connection nativeConn = wsNativeJdbcExtractor.getNativeConnection ( conn );
                           StructDescriptor stDesc = null;
                           Object[] obj = null;
                           Object[] str = null;
                           
                           stDesc = new StructDescriptor( "COURSE_OBJ", nativeConn );
                           obj = new Object[10];
                           for ( int j=0;j<10;j++ ) {
                                 str = new Object[2];
                                 str[0] =j;
                                 str[1] ="Course NAME 00"+j;
                                 STRUCT struct = new STRUCT ( stDesc, nativeConn, str );
                                 obj[j] = struct;
                                 j++;
                           }
                           
                           wsNativeJdbcExtractor = new WebSphereNativeJdbcExtractor ();
                           nativeConn = wsNativeJdbcExtractor.getNativeConnection ( conn );
                           ArrayDescriptor paramsArrDescCR = ArrayDescriptor.createDescriptor ( "COURSE_TAB", nativeConn );
                           ARRAY paramsArrCr = new ARRAY ( paramsArrDescCR, nativeConn, obj );
                    
                           stDesc = new StructDescriptor( "DEPT_OBJ", nativeConn );
                           obj = new Object[10];
                           for ( i=0;i<10;i++ ) {
                                 str = new Object[3];
                                 str[0] = i;
                                 str[1] = "Dept NAME 00"+i;
                                 str[2] = paramsArrCr; 
                                 STRUCT struct = new STRUCT ( stDesc, nativeConn, str );
                                 obj[i] = struct;
                                 i++;
                           }
                           ArrayDescriptor paramsArrDesc = ArrayDescriptor.createDescriptor ( "DEPT_TAB", nativeConn );
                           paramsArr = new ARRAY ( paramsArrDesc, nativeConn, obj );
                           
                           
                           
            
                           LOG.debug("paramsArrDesc "+paramsArrDesc);
                           LOG.debug("paramsArr "+paramsArr);
            
                           cs.setInt(1, deptId);
                           cs.setArray(2, paramsArr);
            
                           // Execute the CALL statement
                           cs.execute ();
                    }
            But it didn't help - it gave me following error


            log4j:WARN No appenders could be found for logger (multiD.MultiD).
            log4j:WARN Please initialize the log4j system properly.
            log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
            java.lang.Exception: Error- ORA-06531: Reference to uninitialized collection
            ORA-06512: at "TWS.DISPLAY_DEPT", line 20
            ORA-06512: at line 1

                 at multiD.MultiD.submitAprvQueue(MultiD.java:99)
                 at multiD.MultiD.main(MultiD.java:125)

            --------
            However if I execute this DB Procedure from DB, it doesn't throw any error.
            • 4. Re: Object of Objects in Oracle - How to implement in Java
              PhHein
              Mod: I'm locking this one.