Re: accessing table of records
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: clay.colburn@xxxxxxxxx
- Date: Fri, 27 Aug 2010 17:29:40 -0700
I don't have time for a complete example, but consider this:
The SQL engine is different than the PL/SQL engine. PL/SQL passes SQL to the
SQL engine for execution. When the SQL engine receives the SQL, it has no
knowledge of the variables you defined in the DECLARE section of your
PL/SQL. The TYPES you have defined, are 'hidden' to the SQL engine, so there
is no way for it to know what updated_contract is. However, the SQL engine
DOES have the ability to go to the dictionary and look things up. So, the
key to success for you is to define your objects in the data dictionary.
Also consider that the TABLE function works upon a collection of OBJECTS,
not a collection of records.
I mention these concepts because they were the ones that tripped me up when
first learning how to use the TABLE function. With this in mind, you should
be able to GOOGLE SQL TABLE functions and find plenty of examples.
good luck,
Mike
On Fri, Aug 27, 2010 at 5:06 PM, Clay Colburn <clay.colburn@xxxxxxxxx>wrote:
> 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:
