---------------------------------------------------------------------------------------------------------------以下是数据源
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
SQL> desc dept1;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SQL> select * from dept1;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-----------------------------------------------------------------------------------------------------------以下是定义 type 和存储过程
create type p_table is object ( ----------类型1
deptno NUMBER(2) ,
dname VARCHAR2(14),
loc VARCHAR2(13)
);
create type use_p_table is table of p_table; -----------类型2
create or replace procedure up_test(o out sys_refcursor) is ----存储过程1
begin
open o for select * from dept1;
end;
create or replace procedure use_sys_ref is -------- 存储过程2
o sys_refcursor;
test_table use_p_table;
begin
up_test(o);
fetch o bulk collect into test_table;
for i in 1..test_table.count loop
dbms_output.put_line(test_table(i).deptno||''||test_table(i).dname||''||test_table(i).loc);
end loop;
end;
--------------------------------------------------------------------------------------------------------------------------------以下是调用
begin
use_sys_ref;
end;
----------------------------------------------------------------以下是报错
ora-06504
l/sql:结果集变量或查询的返回类型不匹配