Re: accessing table of records

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Fri, 27 Aug 2010 17:29:40 -0700
Message-ID: <AANLkTinV5zJiJk0mz2Gnsm-bWadYhYdcVJxpsc6b1o1B_at_mail.gmail.com>

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_at_gmail.com>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!
>

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

Received on Fri Aug 27 2010 - 19:29:40 CDT