Oracle Live SQL - Script: Session 01

Statement 1

 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code,rowid 
)))

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table test_1 (code number, text varchar2(100))

Table created.

Statement 3

 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code,rowid 
)))

ORA-00904: "TEST_PGK"."TRANSFORM_TEST": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 4

create table test_1 (code number, text varchar2(100))

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 5

insert into test_1 values (1,'t1')

1 row(s) inserted.

Statement 6

 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code,rowid 
)))

ORA-00904: "TEST_PGK"."TRANSFORM_TEST": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 7

insert into test_1 values (2,'t2')

1 row(s) inserted.

Statement 8

insert into test_1 values (3,'t3')

1 row(s) inserted.

Statement 9

create or replace package test_pgk 
as 
  type txn_test_r is record 
  ( 
  code test_1.code%type, 
  text test_1.text%type); 
    type txn_test_t is table of txn_test_r; 
  type txn_test_rc is ref cursor return txn_test_r; 
   
   function transform_test 
  (p_refcur          in txn_test_rc 
  ) 
  return txn_test_t 
  pipelined 
  parallel_enable(partition p_refcur by any);  
end test_pgk;

Package created.

Statement 10

create or replace PACKAGE body test_pgk 
AS 
  FUNCTION transform_test( 
      p_refcur IN txn_test_rc ) 
    RETURN txn_test_t pipelined parallel_enable( 
      partition p_refcur BY ANY) 
  AS 
    l_txn_test_t txn_test_t := txn_test_t(); 
  BEGIN 
    LOOP 
      FETCH p_refcur bulk collect INTO l_txn_test_t limit 500; 
    EXIT 
  WHEN l_txn_test_t IS NULL OR l_txn_test_t.count = 0; 
    FOR i_index IN 1..l_txn_test_t.count 
    LOOP 
      dbms_output.put_line('Inside loop'); 
      pipe row (l_txn_test_t(i_index)); 
    END LOOP; 
  END LOOP; 
RETURN; 
END transform_test; 
END test_pgk;

Package Body created.

Statement 11

 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code,rowid 
)))

ORA-03001: unimplemented feature

More Details: https://docs.oracle.com/error-help/db/ora-03001

Statement 12

create table test_1 (code number, text varchar2(100))

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 13

insert into test_1 values (1,'t1')

1 row(s) inserted.

Statement 14

insert into test_1 values (2,'t2')

1 row(s) inserted.

Statement 15

insert into test_1 values (3,'t3')

1 row(s) inserted.

Statement 16

create or replace package test_pgk 
as 
  type txn_test_r is record 
  ( 
  code test_1.code%type, 
  text test_1.text%type); 
    type txn_test_t is table of txn_test_r; 
  type txn_test_rc is ref cursor return txn_test_r; 
   
   function transform_test 
  (p_refcur          in txn_test_rc 
  ) 
  return txn_test_t 
  pipelined 
  parallel_enable(partition p_refcur by any);  
end test_pgk; 
 
 
create or replace PACKAGE body test_pgk 
AS 
  FUNCTION transform_test( 
      p_refcur IN txn_test_rc ) 
    RETURN txn_test_t pipelined parallel_enable( 
      partition p_refcur BY ANY) 
  AS 
    l_txn_test_t txn_test_t := txn_test_t(); 
  BEGIN 
    LOOP 
      FETCH p_refcur bulk collect INTO l_txn_test_t limit 500; 
    EXIT 
  WHEN l_txn_test_t IS NULL OR l_txn_test_t.count = 0; 
    FOR i_index IN 1..l_txn_test_t.count 
    LOOP 
      dbms_output.put_line('Inside loop'); 
      pipe row (l_txn_test_t(i_index)); 
    END LOOP; 
  END LOOP; 
RETURN; 
END transform_test; 
END test_pgk; 
 
 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code 
)));

Errors: PACKAGE TEST_PGK
Line/Col: 19/1 PLS-00103: Encountered the symbol "CREATE"

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 17

 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code 
)))

ORA-06575: Package or function TEST_PGK is in an invalid state

More Details: https://docs.oracle.com/error-help/db/ora-06575

Statement 18

create or replace package test_pgk 
as 
  type txn_test_r is record 
  ( 
  code test_1.code%type, 
  text test_1.text%type); 
    type txn_test_t is table of txn_test_r; 
  type txn_test_rc is ref cursor return txn_test_r; 
   
   function transform_test 
  (p_refcur          in txn_test_rc 
  ) 
  return txn_test_t 
  pipelined 
  parallel_enable(partition p_refcur by any);  
end test_pgk;

Package created.

Statement 19

create or replace PACKAGE body test_pgk 
AS 
  FUNCTION transform_test( 
      p_refcur IN txn_test_rc ) 
    RETURN txn_test_t pipelined parallel_enable( 
      partition p_refcur BY ANY) 
  AS 
    l_txn_test_t txn_test_t := txn_test_t(); 
  BEGIN 
    LOOP 
      FETCH p_refcur bulk collect INTO l_txn_test_t limit 500; 
    EXIT 
  WHEN l_txn_test_t IS NULL OR l_txn_test_t.count = 0; 
    FOR i_index IN 1..l_txn_test_t.count 
    LOOP 
      dbms_output.put_line('Inside loop'); 
      pipe row (l_txn_test_t(i_index)); 
    END LOOP; 
  END LOOP; 
RETURN; 
END transform_test; 
END test_pgk;

Package Body created.

Statement 20

 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code 
)))


CODE TEXT
1 t1
1 t1
2 t2
2 t2
3 t3
3 t3

6 rows selected.
Inside loop
Inside loop
Inside loop
Inside loop
Inside loop
Inside loop

CODE	TEXT
1	t1
1	t1
2	t2
2	t2
3	t3
3	t3

Statement 21

 select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code,rowid 
)))

ORA-03001: unimplemented feature

More Details: https://docs.oracle.com/error-help/db/ora-03001

Session 01

Additional Information