2 Replies Latest reply: Jan 12, 2013 6:49 AM by Solomon Yakobson RSS

    i want to put object type in package .

    947771
      Hi,

      create type my_tab_type is object
      (prodid number, a varchar2(1), b varchar2(1),
      c varchar2(1), d varchar2(1), e varchar2(1))
      .
      /

      create type my_tab_type_coll is table of my_tab_type;
      /

      create or replace function get_some_data (p_val in number)
      return my_tab_type_coll pipelined is
      begin
      FOR i in (select * from my_table where prodid=p_val) loop
      pipe row(my_tab_type(i.prodid,i.a,i.b,i.c,i.d,i.e));
      end loop;
      return;
      end;
      /


      1)how can i put the above thing package ?
      2)can i keep function out of package and other things in side?
      3)which one of above approach would be better?

      i have tried but it is giving warning and error.

      yours sincerely
        • 1. Re: i want to put object type in package .
          SomeoneElse
          i have tried but it is giving warning and error.
          No errors mentioned.
          • 2. Re: i want to put object type in package .
            Solomon Yakobson
            944768 wrote:
            1)how can i put the above thing package ?
            Object type is SQL, not PL/SQL construct therefore you can't create object type in a package.
            2)can i keep function out of package and other things in side?
            As I said, create object type can't be inside a package. Object collection type can. However, as soon as you'll create pipelined function returning that package declared type Oracle will, behing the scene, create system generated (hidden in 11g) types.
            3)which one of above approach would be better?
            Object and object collection types should be created in SQL.

            And some illustration:
            SQL> create or replace
              2    type my_tab_type
              3      is object(
              4                prodid number,
              5                a varchar2(1),
              6                b varchar2(1),
              7                c varchar2(1),
              8                d varchar2(1),
              9                e varchar2(1)
             10               )
             11  /
            
            Type created.
            
            SQL>
            SQL> -- check for hidden types created today
            SQL>
            SQL> select  o.owner,
              2          o.object_name
              3    from  sys.type$ t,
              4          dba_objects o,
              5          sys."_CURRENT_EDITION_OBJ" o1
              6    where bitand(t.properties, 2048) != 0
              7      and o1.oid$ = t.toid
              8      and o.object_id = o1.obj#
              9      and o.last_ddl_time >= trunc(sysdate)
             10    order by o.object_id
             11  /
            
            no rows selected
            
            SQL> create or replace
              2    package pkg1
              3      is
              4         type my_tab_type_coll is table of my_tab_type;
              5  end;
              6  /
            
            Package created.
            
            SQL>
            SQL> -- check for hidden types created today
            SQL>
            SQL> select  o.owner,
              2          o.object_name
              3    from  sys.type$ t,
              4          dba_objects o,
              5          sys."_CURRENT_EDITION_OBJ" o1
              6    where bitand(t.properties, 2048) != 0
              7      and o1.oid$ = t.toid
              8      and o.object_id = o1.obj#
              9      and o.last_ddl_time >= trunc(sysdate)
             10    order by o.object_id
             11  /
            
            no rows selected
            
            SQL> create or replace
              2    function get_some_data(
              3                           p_val in number
              4                          )
              5      return pkg1.my_tab_type_coll
              6      pipelined
              7      is
              8      begin
              9          pipe row(my_tab_type(p_val,'A','B','C','D','E'));
             10  end;
             11  /
            
            Function created.
            
            SQL>
            SQL> -- check for hidden types created today
            SQL>
            SQL> select  o.owner,
              2          o.object_name
              3    from  sys.type$ t,
              4          dba_objects o,
              5          sys."_CURRENT_EDITION_OBJ" o1
              6    where bitand(t.properties, 2048) != 0
              7      and o1.oid$ = t.toid
              8      and o.object_id = o1.obj#
              9      and o.last_ddl_time >= trunc(sysdate)
             10    order by o.object_id
             11  /
            
            OWNER                          OBJECT_NAME
            ------------------------------ ----------------------------
            SCOTT                          SYS_PLSQL_75080_9_1
            SCOTT                          SYS_PLSQL_75080_DUMMY_1
            
            SQL> desc SYS_PLSQL_75080_9_1
             SYS_PLSQL_75080_9_1 TABLE OF MY_TAB_TYPE
             Name                                      Null?    Type
             ----------------------------------------- -------- -------------
             PRODID                                             NUMBER
             A                                                  VARCHAR2(1)
             B                                                  VARCHAR2(1)
             C                                                  VARCHAR2(1)
             D                                                  VARCHAR2(1)
             E                                                  VARCHAR2(1)
            
            SQL> desc SYS_PLSQL_75080_DUMMY_1
             SYS_PLSQL_75080_DUMMY_1 TABLE OF NUMBER
            
            SQL>  -- keep in mind, hidden types remain even when we
            SQL>  -- drop the function
            SQL> drop function get_some_data
              2  /
            
            Function dropped.
            
            SQL>
            SQL> -- check for hidden types created today
            SQL>
            SQL> select  o.owner,
              2          o.object_name
              3    from  sys.type$ t,
              4          dba_objects o,
              5          sys."_CURRENT_EDITION_OBJ" o1
              6    where bitand(t.properties, 2048) != 0
              7      and o1.oid$ = t.toid
              8      and o.object_id = o1.obj#
              9      and o.last_ddl_time >= trunc(sysdate)
             10    order by o.object_id
             11  /
            
            OWNER                          OBJECT_NAME
            ------------------------------ ----------------------------------------
            SCOTT                          SYS_PLSQL_75080_9_1
            SCOTT                          SYS_PLSQL_75080_DUMMY_1
            
            SQL> -- types will be reused if we create a new pipelined
            SQL> -- function with same return type
            SQL> create or replace
              2    function new_func
              3      return pkg1.my_tab_type_coll
              4      pipelined
              5      is
              6      begin
              7          pipe row(my_tab_type(99,'A','B','C','D','E'));
              8  end;
              9  /
            
            Function created.
            
            SQL>
            SQL> -- check for hidden types created today
            SQL>
            SQL> select  o.owner,
              2          o.object_name
              3    from  sys.type$ t,
              4          dba_objects o,
              5          sys."_CURRENT_EDITION_OBJ" o1
              6    where bitand(t.properties, 2048) != 0
              7      and o1.oid$ = t.toid
              8      and o.object_id = o1.obj#
              9      and o.last_ddl_time >= trunc(sysdate)
             10    order by o.object_id
             11  /
            
            OWNER                          OBJECT_NAME
            ------------------------------ ----------------------------------------
            SCOTT                          SYS_PLSQL_75080_9_1
            SCOTT                          SYS_PLSQL_75080_DUMMY_1
            
            SQL>
            SY.