Re: PL/SQL Tables
From: The Magnet <art_at_unsu.com>
Date: Thu, 6 May 2010 08:23:47 -0700 (PDT)
Message-ID: <e5d8971f-09d0-45c4-afcc-1292f089f42d_at_y12g2000vbg.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
Date: Thu, 6 May 2010 08:23:47 -0700 (PDT)
Message-ID: <e5d8971f-09d0-45c4-afcc-1292f089f42d_at_y12g2000vbg.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
Hi Michel,
Not bad. I want to go one step further, is it possible to define the types in the package? I also get an error:
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 test as
employees emp_tab := emp_tab();
procedure test1 as
g_ref sys_REFCURSOR;
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;
/
PLS-00540: object not supported in this context.
Many Thanks! Received on Thu May 06 2010 - 10:23:47 CDT