Re: PL/SQL Tables

"The Magnet" <art_at_unsu.com> a écrit dans le message de news: d239d719-b3ea-4f75-b6f5-1720f159fd4d_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 Received on Thu May 06 2010 - 10:11:45 CDT