Re: accessing table of records
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: clay.colburn@xxxxxxxxx, oracle-l <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 27 Aug 2010 19:38:25 -0700 (PDT)
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@xxxxxxxxx>
To: oracle-l <Oracle-L@xxxxxxxxxxxxx>
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!
Other related posts:
