StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Declare <code>t_FeeRecord</code> and <code>t_Fees</code> as database objects, not pl/sql objects,<br> in Oracle PL/SQL types cannot be used in SQL queries, this gives you datatype error (however, this restriction is removed in Oracle 12c). <br><br> <code>t_FeeRecord</code> must be created as object type, not a record type, since records are PL/SQL types and cannot be used in SQL queries.</p> <pre><code>create type t_FeeRecord is object( description varchar2(80), amount number(12,2), taxAmount number(12,2) );/ create type t_Fees as table of t_FeeRecord; / </code></pre> <p>Here is a simple demo that creates a table of records, open a ref cursor for this table and reads the cursor and inserts rows retrieved from the cursor to the table (tested on 11.2g): </p> <pre><code>create type t_FeeRecord is object( description varchar2(80), amount number(12,2), taxAmount number(12,2) ); / create type t_Fees as table of t_FeeRecord; / create table temporary_fee( description varchar2(80), amount number(12,2), taxAmount number(12,2) ); declare fees t_Fees; TYPE rctl IS REF CURSOR; cur rctl; rec TEMPORARY_FEE%ROWTYPE; begin fees := t_Fees ( t_FeeRecord( 'aaa', 20, 30 ), t_FeeRecord( 'bbb', 10, 76 ), t_FeeRecord( 'xxx', 4, 23 ), t_FeeRecord( 'zzz', 7, 43 ), t_FeeRecord( 'ccc', 13, 44 ) ); open cur for select * from table( fees ); LOOP FETCH cur INTO rec; EXIT WHEN cur%NOTFOUND; INSERT INTO TEMPORARY_FEE VALUES rec; END LOOP; close cur; end; / select * from temporary_fee; DESCRIPTION AMOUNT TAXAMOUNT ------------ ---------- ---------- aaa 20 30 bbb 10 76 xxx 4 23 zzz 7 43 ccc 13 44 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload