Oracle stored function with temp table

Question

I created a temporary table and a stored function to read it. when i call it following message appears:

RA-22905: Zugriff auf Zeilen eines Objekts, das keine Nested Table ist, nicht möglich
22905. 00000 -  "cannot access rows from a non-nested table item"
*Cause:    attempt to access rows of an item whose type is not known at
           parse time or that is not of a nested table type
*Action:   use CAST to cast the item to a nested table type
Fehler in Zeile: 3 Spalte: 15

So, how can I do that CAST thing?

My beloved function:

create or replace PACKAGE BODY testlho2 IS 
  FUNCTION getBasicDate (app_in IN varchar2, termc_in IN varchar2) 
    return sys_refcursor is 
    l_rc SYS_REFCURSOR; 
  BEGIN 
    -- Populate temporary table 
    INSERT INTO temp_tab_test_lho2 (app, sla, tsl) 
      SELECT app, sla, tslstat 
      FROM pmon_orig_file 
      WHERE app = app_in and termcause = termc_in; 
    -- Open REF CURSOR for Output 
    open l_rc for 
      select app, sla, tsl 
      from temp_tab_test_lho2; 
    return l_rc; 
  END; 
END testlho2 ;

possible duplicate of [ORA-22905 - when quering a table type with a select statement](http://stackoverflow.com/questions/19208264/ora-22905-when-quering-a-table-type-with-a-select-statement) — Sanders the Softwarer, Apr 27 '15 at 12:07
Sorry, my crystall ball is out of order. If you publish your function, maybe somebody will can say how to repair it. — Sanders the Softwarer, Apr 27 '15 at 12:09
create or replace PACKAGE BODY testlho2 IS FUNCTION getBasicDate (app_in IN varchar2, termc_in IN varchar2) return sys_refcursor is l_rc SYS_REFCURSOR; BEGIN (-- Populate temporary table) INSERT INTO temp_tab_test_lho2 (app, sla, tsl) SELECT app, sla, tslstat FROM pmon_orig_file WHERE app = app_in and termcause = termc_in; (-- Open REF CURSOR for Output) open l_rc for select app, sla, tsl from temp_tab_test_lho2; return l_rc; END; END testlho2 ; — user, Apr 27 '15 at 12:28
At first look I didn't see any cause for ORA-22905. Are table fields all ordinally typed? BTW, do you really need of temp table? Why you don't just return select from pmon_orig_file? — Sanders the Softwarer, Apr 27 '15 at 12:42
my table: CREATE GLOBAL TEMPORARY TABLE "PMON"."TEMP_TAB_TEST_LHO2" ( "APP" VARCHAR2(255 BYTE), "SLA" VARCHAR2(255 BYTE), "TSL" TIMESTAMP (6) ) ON COMMIT PRESERVE ROWS ; — user, Apr 27 '15 at 12:47
wha temp table? i try to create some kind of a View. We use jasperreports and if you have lots of subreports, these is a huge advantage. — user, Apr 27 '15 at 12:54
... or just run the select straight from pmon_orig_file -- not sure what a temporary table adds to this. — David Aldridge, Apr 28 '15 at 11:59

score 0 · Answer 1 · edited May 23 '17 at 12:06

Temp table:

create global temporary table tbl(name_ varchar2(50))
/

Function:

create or replace function foo(app_in IN varchar2)
return sys_refcursor is 
l_rc SYS_REFCURSOR; 
begin
  insert into tbl select app_in from dual;
  open l_rc for select name_ from tbl;
  return l_rc;
end;
/

selecting the data from the function:

select * from table(foo('hiiii'));

Error:

ORA-22905: cannot access rows from a non-nested table item
22905. 00000 -  "cannot access rows from a non-nested table item"

If one want to use the function as above it must return a type. see the below question:

Function return sys_refcursor call from sql with specific columns

If you use the function in an anonymous block then it will work:

declare
l_rc SYS_REFCURSOR; 
begin
l_rc := foo('hiii');
end;
/

anonymous block completed

Even if you solve the return type issue with type, if you use the function in a select statement then it will error out saying:

ORA-14551: cannot perform a DML operation inside a query 
14551. 00000 -  "cannot perform a DML operation inside a query "

Here it says how one can solve the above problem

Solution to "cannot perform a DML operation inside a query"?

Oracle stored function with temp table

1 Answers1