Use bulk collect and rownum to insert first 10 records : Insert with subquery « Insert Delete Update « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Insert Delete Update
  3. Insert with subquery

Use bulk collect and rownum to insert first 10 records

   
SQL>
SQL>
SQL> create table myTable
  2  as
  3  select rownum id, a.*
  4    from all_objects a
  5   where 1=0
  6  /

Table created.

SQL>
SQL> declare
  2      l_owner        dbms_sql.varchar2_table;
  3      l_object_name  dbms_sql.varchar2_table;
  4      l_object_type  dbms_sql.varchar2_table;
  5      l_created      dbms_sql.varchar2_table;
  6
  7      cursor c is
  8      select owner, object_name, object_type, created
  9      from myTable
 10      order by created DESC;
 11  begin
 12      select owner, object_name, object_type, created
 13        bulk collect into l_owner, l_object_name, l_object_type, l_created from ( select owner, object_name, object_type, created from myTable order by created DESC )
 14       where ROWNUM <= 10;
 15
 16      open c;
 17      fetch c bulk collect
 18       into l_owner, l_object_name, l_object_type, l_created
 19      limit 10;
 20      close c;
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>








Related examples in the same category

1.Perform more complicated inserts using sub-selects
2.Merge into a table
3.Insert into ... select
4.Conditional INSERT Statement
5.Insert bulk by insert ... into ... select
6.To insert records into a table using a subquery:
7.Adding Multiple Rows to a Table