dbms_sql.varchar2_table type variable

 
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.	First DBMS_SQL Example
2.	Pass a query statement to a stored procedure
3.	Use dbms_sql.open_cursor create a cursor
4.	Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.	Use dbms_sql.bind_array to bind array to a cursor
6.	Use dbms_sql.parse to bind a select statement to a cursor
7.	Call dbms_sql.describe_columns2 to get info for a column
8.	Non-Query DML and DDL Statements
9.	Use DBMS_SQL with the RETURNING clause.
10.	DBMS_SQL.NATIVE;
11.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
12.	Dynamic sql statement with variable binding
13.	Dynamic select statement and row fetch
14.	Execute PL/SQL block
15.	DBMS_SQL package: dynamic SQL
16.	Pass where clause to a stored procedure
17.	Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
18.	Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
19.	drop the supplied table using dynamic SQL.
20.	DBMS_SQL.BIND_VARIABLE_CHAR
21.	illustrate the importance of setting out_value_size.
22.	Calling a function which uses dynamic SQL from within an SQL statement.
23.	Illustrates the interaction of roles and dynamic SQL.
24.	illustrates a DDL statement which is built dynamically from the procedure parameters.
25.	Use dbms_sql to process query, cursor and value
26.	Use dbms_sql.bind_variable to bind variable

dbms_sql.varchar2_table type variable : dbms_sql « System Packages « Oracle PL / SQL

Related examples in the same category