Session 01

View All Public Scripts Login to Run Script

Script Name Session 01
Visibility Unlisted - anyone with the share link can access
Description Rowid Issue
Contributor
Created Wednesday July 29, 2020

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

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

Statement 4
```
create table test_1 (code number, text varchar2(100))
```
ORA-00955: name is already used by an existing object
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

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

Statement 12
```
create table test_1 (code number, text varchar2(100))
```
ORA-00955: name is already used by an existing object
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"

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

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

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials