Meghna wrote:
is there any way to use pl/sql collection in SQL or refcur without creating it because i am not able to create type in database.The only way I am aware of is pipelined function:
create or replace
package pkg1
is
type traRecord
is record(
ename emp.ename%type,
sal emp.sal%type
);
TYPE traArray IS TABLE OF traRecord;
function f1
return traArray
pipelined;
end;
/
create or replace
package body pkg1
is
function f1
return traArray
pipelined
is
v_rec traRecord;
begin
v_rec.ename := 'Sam';
v_rec.sal := 1000;
pipe row(v_rec);
v_rec.ename := 'John';
v_rec.sal := 1500;
pipe row(v_rec);
v_rec.ename := 'Mary';
v_rec.sal := 2000;
pipe row(v_rec);
return;
end;
end;
/Now you can:
SQL> select * from table(pkg1.f1)
2 /
ENAME SAL
---------- ----------
Sam 1000
John 1500
Mary 2000
SQL>Keep in mind, it will create system generated types:
SQL> select type_name from user_types
2 /
TYPE_NAME
------------------------------
SYS_PLSQL_73305_9_1
SYS_PLSQL_73305_DUMMY_1
SYS_PLSQL_73305_34_1
SQL> desc SYS_PLSQL_73305_9_1
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
SQL> desc SYS_PLSQL_73305_DUMMY_1
SYS_PLSQL_73305_DUMMY_1 TABLE OF NUMBER
SQL> desc SYS_PLSQL_73305_34_1
SYS_PLSQL_73305_34_1 TABLE OF SYS_PLSQL_73305_9_1
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
SQL>SY.