accessing table of records

From: Clay Colburn <clay.colburn_at_gmail.com>
Date: Fri, 27 Aug 2010 17:06:43 -0700
Message-ID: <AANLkTi=fAryc4vJgLOYa-QakTpXznV+oLjV8APUr9mj5_at_mail.gmail.com>

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

Received on Fri Aug 27 2010 - 19:06:43 CDT