CODEWITHSUNDEEP
oracle-databaseplsql
Shubhajit Chanda
Shubhajit Chanda

Reputation: 19

Trying to query data using select statement getting ORA-22905: cannot access rows from a non-nested table item

I am using a package "EXCELTABLE" to read data from excel file, the query i use works perfectly, but the problem is whenever i try to use the same package inside a for loop in a stored procedure, it gives me error as "ORA-22905: cannot access rows from a non-nested table item".

Below is the select query i use:-

  select ROWNUM "ID","Netzelementtyp","Projekt_ID","Ticket_ID","Netzelement_Nr","Projektart","Statusliste_Status","Statusliste_Ist_Datum","Statusliste_Plan_Datum","Statusliste_Bemerkung" from 
  (select t."Netzelementtyp",t."Projekt_ID",t."Ticket_ID",t."Netzelement_Nr",t."Projektart",t."Statusliste_Status",t."Statusliste_Ist_Datum",t."Statusliste_Plan_Datum",t."Statusliste_Bemerkung" from table(
 EXCELTABLE.GETROWS(
  EXCELTABLE.GETFILE('DOC','TicketTracking2.xlsx')
   , 'Page1_4'
  , ' "Netzelementtyp"    number
    , "Projekt_ID"    number
    , "Ticket_ID"     varchar2(100)
    , "Netzelement_Nr"      NUMBER
      , "Projektart"  varchar2(1000)
    , "Statusliste_Status"  varchar2(100)
    , "Statusliste_Ist_Datum"  DATE
    , "Statusliste_Plan_Datum" DATE
    , "Statusliste_Bemerkung"  varchar2(4000)'
   , 'A2'
    )
  ) t)

Upvotes: 0

Views: 545

Answers (1)

kfinity
kfinity

Reputation: 9091

The ExcelTable developer says that it's a known issue (see comments), and the workaround they offer is to use dynamic sql:

OPEN my_refcursor FOR 'SELECT ...' USING <bind variables>

You can read more about the underlying ORA-22905 error on this related question. The basic issue is that EXCELTABLE.GETROWS() returns a pipelined ANYDATASET, which is not a nested table; the Oracle documentation describes it as one of the "opaque types; the internal structure of these types is not known to the database". I think you should be able to access the individual elements using the ANYDATASET API, but it looks much simpler to use a dynamic refcursor, as suggested by the developer above.

Upvotes: 1

Related Questions