Re: PL/SQL Tables

From: The Magnet <art_at_unsu.com>
Date: Thu, 6 May 2010 08:37:37 -0700 (PDT)
Message-ID: <64d70eee-adf6-4046-9730-1dce062b88d1_at_p17g2000vbe.googlegroups.com>



On May 6, 10:11 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "The Magnet" <a..._at_unsu.com> a écrit dans le message de news: d239d719-b3ea-4f75-b6f5-1720f159f..._at_u7g2000vbq.googlegroups.com...
> |
> | Hi,
> |
> | I'm trying to create a Pl/SQL table and SELECT from it.  I've looked
> | all over for code examples, but none are working.  Please look at
> | this.  Maybe someone knows what is wrong and can give me the correct
> | code?
> |
> |
> | create or replace package test as
> | TYPE emp_type AS OBJECT
> |  (id   NUMBER,
> |   name VARCHAR2(20));
> |
> | TYPE emp_tab IS TABLE OF emp_type;
> |
> | procedure test1;
> | end test;
> | /
> |
> | create or replace package body test1 as
> |  g_ref REFCURSOR
> |  employees emp_tab := emp_tab();
> |
> | BEGIN
> |  employees.EXTEND(2);
> |  employees(1) := emp_type (1, 'name1');
> |  employees(2) := emp_type (2, 'name2');
> |  OPEN :g_ref FOR
> |  SELECT * FROM TABLE (CAST (employees AS emp_tab));
> | END test1
> | end test;
> | /
> |
> | Thanks!
> |
>
> After fixing about a dozen of syntax errors I get:
>
> SQL> create TYPE emp_type AS OBJECT
>   2    (id   NUMBER,
>   3     name VARCHAR2(20));
>   4  /
>
> Type created.
>
> SQL> create TYPE emp_tab IS TABLE OF emp_type;
>   2  /
>
> Type created.
>
> SQL> create or replace package test as
>   2  procedure test1;
>   3  end test;
>   4  /
>
> Package created.
>
> SQL> create or replace package body test as
>   2    employees emp_tab := emp_tab();
>   3    procedure test1 as
>   4      g_ref sys_REFCURSOR;
>   5  BEGIN
>   6    employees.EXTEND(2);
>   7    employees(1) := emp_type (1, 'name1');
>   8    employees(2) := emp_type (2, 'name2');
>   9    OPEN g_ref FOR
>  10    SELECT * FROM TABLE (CAST (employees AS emp_tab));
>  11  END test1;
>  12  end test;
>  13  /
>
> Package body created.
>
> Regards
> Michel

Michel,

Let me just add, why can I not create those types? I can create a REFCURSOR type in the package header. Received on Thu May 06 2010 - 10:37:37 CDT

Original text of this message