Re: accessing table of records
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 27 Aug 2010 19:38:25 -0700 (PDT)
Message-ID: <620109.63336.qm_at_web65404.mail.ac4.yahoo.com>
Date: Fri, 27 Aug 2010 19:38:25 -0700 (PDT)
Message-ID: <620109.63336.qm_at_web65404.mail.ac4.yahoo.com>
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-lReceived on Fri Aug 27 2010 - 21:38:25 CDT