create type t_var is varray(10) of number; / create procedure p_var(p_v in out t_var) as begin for i in 1..10 loop p_v.extend; p_v(i):=i; dbms_output.put_line(p_v(i)); end loop; end; / SQL> set serveroutput on SQL> SQL> declare 2 v_var t_var:=t_var(); 3 begin 4 p_var(v_var); 5 end; 6 /
CREATE OR REPLACE PACKAGE ado_callpkg AS TYPE eid IS TABLE of NUMBER(4) INDEX BY BINARY_INTEGER; TYPE ename IS TABLE of VARCHAR2(40) INDEX BY BINARY_INTEGER; PROCEDURE getEmpNames (empid OUT eid,empname OUT ename); end ado_callpkg; /CREATE OR REPLACE PACKAGE BODY ado_callpkg AS PROCEDURE getEmpNames (empid OUT eid,empname OUT ename) IS CURSOR c1 IS select employee_id,first_name||','||Middle_Initial||','||last_name as name from employee; cnt NUMBER DEFAULT 1; c c1%ROWTYPE; BEGIN open c1; FETCH c1 INTO c; while c1%Found Loop empname(cnt):=c.name; empid(cnt):=c.employee_id; cnt :=cnt+1; FETCH c1 INTO c; END LOOP; close c1; END; end ado_callpkg;
/
create procedure p_var(p_v in out t_var)
as
begin
for i in 1..10 loop
p_v.extend;
p_v(i):=i;
dbms_output.put_line(p_v(i));
end loop;
end;
/
SQL> set serveroutput on
SQL>
SQL> declare
2 v_var t_var:=t_var();
3 begin
4 p_var(v_var);
5 end;
6 /
TYPE eid IS TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE ename IS TABLE of VARCHAR2(40) INDEX BY BINARY_INTEGER;
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename);
end ado_callpkg;
/CREATE OR REPLACE PACKAGE BODY ado_callpkg AS
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename) IS
CURSOR c1 IS select employee_id,first_name||','||Middle_Initial||','||last_name as name from employee;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE;
BEGIN
open c1;
FETCH c1 INTO c;
while c1%Found
Loop
empname(cnt):=c.name;
empid(cnt):=c.employee_id;
cnt :=cnt+1;
FETCH c1 INTO c;
END LOOP;
close c1;
END;
end ado_callpkg;