Re: accessing table of records

You might want to read what I wrote in this post as it may be helpful to you:

http://oratips-ddf.blogspot.com/2008/09/how-dynamic.html

The TABLE() function is used in several PL/SQL examples.
 
David Fitzjarrell





________________________________
From: Clay Colburn <clay.colburn_at_gmail.com>
To: oracle-l <Oracle-L_at_freelists.org>
Sent: Fri, August 27, 2010 8:06:43 PM
Subject: accessing table of records

I'm having trouble working with a table of records.  I am trying to do the 
following: 


SQL> declare
  2    type updated_contract_record_type is record (d_contract_id number, 
transaction_date date);
  3    type updated_contract_table_type is table of updated_contract_record_type 
index by pls_integer;
  4    updated_contract updated_contract_table_type;
  5    i integer;
  6  begin
  7    updated_contract(1).d_contract_id := 3;
  8    updated_contract(1).transaction_date := '03-aug-10';
  9    dbms_output.put_line('output = ' || updated_contract(1).d_contract_id || 
' - ' || updated_contract(1).transaction_date);
 10    insert into my_test2(id, my_date)
 11    select uc.d_contract_id, uc.transaction_date
 12    from table(updated_contract) uc;
 13    rollback;
 14  end;
 15  /
  from table(updated_contract) uc;
             *
ERROR at line 12:
ORA-06550: line 12, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 12, column 8:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored


Basically, I'd like to be able to access a table of updated_contract_record_type 
records as a table.  Eventually to join to another "real" table to act as 
filtering criteria, but I started with the test above.

Any ideas on how to treat this table of records as if it was a table?

Thanks!


      

--
http://www.freelists.org/webpage/oracle-l