Re: PL/SQL Tables

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 6 May 2010 18:51:59 +0200
Message-ID: <4be2f3ab$0$23309$426a74cc_at_news.free.fr>


"The Magnet" <art_at_unsu.com> a écrit dans le message de news: 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.


Type must be declared outside PL/SQL to be used outside PL/SQL (inside the SQL engine).
Type that is used inside PL/SQL can be declared inside PL/SQL.

Regards
Michel Received on Thu May 06 2010 - 11:51:59 CDT

Original text of this message