Mega Code Archive
Return a varray from a function
<![CDATA[
SQL>
SQL> create table gift(
2 gift_id NUMBER,
3 emp_id NUMBER,
4 register_date DATE ,
5 total_price NUMBER(7,2),
6 DELIVER_DATE DATE,
7 DELIVER_TIME VARCHAR2(7),
8 payment VARCHAR2(2) ,
9 EMP_NO NUMBER(3,0),
10 DELIVER_NAME VARCHAR2(35),
11 message VARCHAR2(100)
12 )
13 storage(initial 50m);
Table created.
SQL>
SQL> alter table gift
2 add constraint gift_pk primary key(gift_id);
Table altered.
SQL>
SQL> analyze table gift compute statistics;
Table analyzed.
SQL>
SQL> create or replace type gift_type as object
2 ( gift_id NUMBER,
3 emp_id NUMBER,
4 register_date DATE ,
5 total_price NUMBER(7,2),
6 DELIVER_DATE DATE,
7 DELIVER_TIME VARCHAR2(7),
8 payment VARCHAR2(2) ,
9 EMP_NO NUMBER(3,0),
10 DELIVER_NAME VARCHAR2(35),
11 message VARCHAR2(100)
12 );
13 /
SQL>
SQL> create or replace type gift_table as table of gift_type;
2 /
Type created.
SQL>
SQL> create or replace function gift_generator_piped
2 (p_num_rows in number)
3 RETURN gift_table
4 PIPELINED
5 AS
6 BEGIN
7
8 for i in 1..p_num_rows loop
9 PIPE ROW ( gift_type(i,i,sysdate,0,sysdate,null,'CA',1,null,null ));
10 end loop;
11
12 return;
13
14 END;
15 /
Function created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> declare
2 v_start number;
3 v_run1 number;
4 begin
5 v_start := dbms_utility.get_time;
6 insert /*+APPEND */ into gift (select * from table(gift_generator_piped(1000) ) );
7 v_run1 := dbms_utility.get_time - v_start;
8 dbms_output.put_line('piped '||v_run1);
9
10 end;
11 /
piped 2
PL/SQL procedure successfully completed.
SQL> drop table gift;
Table dropped.
SQL>
]]>