Oracle table cast collection updating type using table cast Cucold chat cam

Posted by / 26-Sep-2017 23:42

Oracle table cast collection updating type using table cast

Posted by Bill Ramos | On March 16th, 2017 | In Amazon RDS, AWS, AWS SCT, Data Management, Database Migration | Tags: #awssct, AWS Schema Conversion Tool, aws sct, data migration, database migration, Migration, migration to My SQL, Oracle to My SQL migration Oracle’s SELECT INTO statement with BULK COLLECT clause allows you to retrieve an entire result set and store it in a PL/SQL collection type variable in a single operation.This approach allows to avoid the use of a loop statement to retrieve one result row at a time, thus making the code compact and effective.We can select records from this table-like-structure in an sequence determined by a normal SQL ORDER BY clause.The result of that query is turned into a collection again, using MULTISET, and subsequently casted into the desired collection type, namely NUM_TABLE.Use the last_name for every -- element in l_cpx_tbl as a key for the l_sort_tbl.Associate -- the key with a current element in the l_cpx_tbl collection.Despite the fact that My SQL doesn’t support record-type variables, SCT handles them properly.So, the source code will look as follows: CREATE PROCEDURE SCT_DEMO.

The source code includes the collection variable v_list and the SELECT statement with BULK COLLECT clause to fill it.

We define two helper types in the database:declare type complex_type is record ( last_name varchar2(50) , street varchar2(50) , city varchar2(50) , birthdate date , zipcode varchar2(15) , email_address varchar2(50) , many_more_elements varchar2(4000) ); type complex_type_table_type is table of complex_type; -- define and initialize a complex Nested Table Collection l_cpx_tbl complex_type_table_type:= complex_type_table_type(); l_cpx_rec complex_type; begin -- first populate the collection with a few values.

l_cpx_rec.last_name:= 'Jellema'; l_cpx_rec.street:= 'Rembrandtlaan'; l_cpx_rec.email_address:= '[email protected]'; l_cpx_tbl.extend; l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec; l_cpx_rec.last_name:= 'Abramovic'; l_cpx_rec.street:= 'Chelsea Road'; l_cpx_rec.email_address:= '[email protected]'; l_cpx_tbl.extend; l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec; l_cpx_rec.last_name:= 'Rooney'; l_cpx_rec.street:= 'Man You 32'; l_cpx_rec.email_address:= '[email protected]'; l_cpx_tbl.extend; l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec; l_cpx_rec.last_name:= 'Basten'; l_cpx_rec.street:= 'Russia Park 1988'; l_cpx_rec.email_address:= '[email protected]'; l_cpx_tbl.extend; l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec; We want to sort the collection by the last_name field in the complex_type records.

loop l_sort_tbl.extend; -- note: the first field in the sortable object is the expression used for sorting!!!

-- the second field is a pointer to the current position of the element in the l_cpx_tbl l_sort_tbl( l_sort_tbl.last):= new sortable( l_cpx_tbl(l_idx).last_name, l_idx); l_idx:= l_cpx_tbl.next(l_idx); exit when l_idx is null; end loop; -- SORT L_SORT_TBL -- using the table operator we pretend that l_sort_tbl is a table-like-recordset; -- we can select records from this table-like-structure in an sequence determined by a -- normal SQL ORDER BY clause -- the result of that query is turned into a collection again, using MULTISET, and casted -- into the desired collection type, namely SORTABLE_TABLE select cast ( multiset( select * from table( l_sort_tbl ) order by 1 -- note: here we have a very simple sort-operation; -- it could be much more complex obviously ) as sortable_table) into l_sort_tbl from dual; -- at this point, l_sort_tbl is sorted -- The hard work was done by the SQL engine!!

oracle table cast collection updating type using table cast-34oracle table cast collection updating type using table cast-77oracle table cast collection updating type using table cast-8

One thought on “oracle table cast collection updating type using table cast”