Data de Inclusão:
02/09/2006 | Última Alteração:
02/09/2006
Tipo:Scripts | Enviada por:
Moderador Oracle_br (moderador@oraclebr.com.br)
Selecting from a PL/SQL Table
This tip comes from Chandran Gangadharan and Vitali Prikodhko, database administrators with Grocery Gateway in Toronto, Ontario.
This script returns a ref cursor that selects from a PL/SQL table. Applications that use PL SQL procedures may occasionally require information that is populated in a PLSQL table. An alternative would be to create a global temporary table, however PLSQL tables are very convenient especially for a small number of rows
I used the method described below in a procedure that accepted an xml, used the data in the xml to create a select statement that was returned in the form of a cursor. The below description just describes the steps involved in returning a cursor from a procedure that selects data from a PLSQL table.
Steps:
1. Create corresponding types
2. Create package
3. run plsql block from sqlplus to output the data in a plsql table
create or replace
type typ_plsqltables_pkg_obj as object (sql_line varchar2(4000));
/
create or replace
type typ_plsqltables_pkg_tab is table of typ_plsqltables_pkg_obj;
/
CREATE OR REPLACE PACKAGE plsqltables_pkg IS
TYPE t_out_cur_typ IS REF CURSOR;
PROCEDURE tabledata(
o_cur OUT t_out_cur_typ);
END plsqltables_pkg;
/
CREATE OR REPLACE PACKAGE BODY plsqltables_pkg IS
PROCEDURE tabledata(
o_cur OUT t_out_cur_typ) IS
tab_plsqltables_pkg typ_plsqltables_pkg_tab;
BEGIN
tab_plsqltables_pkg :=
typ_plsqltables_pkg_tab(typ_plsqltables_pkg_obj('ab'));
tab_plsqltables_pkg.extend;
tab_plsqltables_pkg(2) := typ_plsqltables_pkg_obj('a');
tab_plsqltables_pkg.extend;
tab_plsqltables_pkg(3) := typ_plsqltables_pkg_obj('b');
tab_plsqltables_pkg.extend;
tab_plsqltables_pkg(4) := typ_plsqltables_pkg_obj('c');
tab_plsqltables_pkg.extend;
tab_plsqltables_pkg(5) := typ_plsqltables_pkg_obj('d');
OPEN o_cur FOR
select t.sql_line
from table(cast(tabledata.tab_plsqltables_pkg as
typ_plsqltables_pkg_tab)) t;
END tabledata;
END plsqltables_pkg;
/
DECLARE
ocur plsqltables_pkg.t_out_cur_typ;
vc_b varchar2(4000);
BEGIN
plsqltables_pkg.tabledata( OCUR );
LOOP
FETCH ocur INTO vc_b;
EXIT when ocur%NOTFOUND;
DBMS_OUTPUT.Put_Line(' '||vc_b);
END LOOP;
END;