This discussion is archived
2 Replies Latest reply: Jan 12, 2013 4:49 AM by Solomon Yakobson RSS

i want to put object type in package .

947771 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points