2 Replies Latest reply: May 8, 2013 12:02 AM by 585159 RSS

    Object of Objects in Oracle - How to implement in Java

    585159
      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;
      Can any body please guide me calling this procedure in JAVA.
        • 1. Re: Object of Objects in Oracle - How to implement in Java
          rp0428
          >
          Can any body please guide me calling this procedure in JAVA.
          >
          No - not in this forum. This forum, as the title says is for SQL Developer and your question has nothing to do with sql developer.

          You need to use JDBC to do what you are asking.

          Please mark this question ANSWERED and repost it in the JDBC forum.
          https://forums.oracle.com/forums/category.jspa?categoryID=288

          Before you repost you should review this article that demonstrates how to call an Oracle stored procedure and pass Oracle object types.
          http://technology.amis.nl/2008/07/16/oracle-jdbc-passing-a-table-of-custom-object-type-to-a-stored-procedure-implementing-efficient-single-round-trip-data-exchange-part-two-of-an-adf-application-on-a-plsql-api/

          And for any of the code and example you don't understand you should review the relevant sections of the JDBC Developer's Guide.
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraarr.htm#g1072333

          If you do repost in the JDBC forum provide your 4 digit Oracle version, your os and JDK versions and the version and name of the JDBC jar file that you are using.
          • 2. Re: Object of Objects in Oracle - How to implement in Java
            585159
            Moving the post to another thread in jdbc.
            Thanks to rp0428