写个过程想用动态语句实现,参数是网页传过来的我想用游标放回结果集,目前bulk collect放不进去数据,ORA-00932: 不一致的数据类型: 要求 UDT 得到的却是 CHAR错误。各位我写的有什么问题?有其他办法吗?
declare
p_sql varchar(200);
n_emp_t n_emp_v:= n_emp_v();
BEGIN
p_sql := 'select dw,ksrq,sal,sal1,sal2 from emp';
p_sql := p_sql || ' where kzrq between :1 and :2';
execute immediate p_sql BULK COLLECT INTO n_emp_t using P_RQ1, P_RQ2;
open c for select * from table(gfgs_dj_T);
end;
create or replace type n_emp as object
(
dw VARCHAR2(10),
KSRQ date,
SAL NUMBER(10,2),
SAL1 NUMBER(11,2)
SAL2 NUMBER(11,2)
)create or replace type n_emp_v as table of n_empSQL
declare
p_sql varchar(200);
n_emp_t n_emp_v:= n_emp_v();
BEGIN
p_sql := 'select dw,ksrq,sal,sal1,sal2 from emp';
p_sql := p_sql || ' where kzrq between :1 and :2';
execute immediate p_sql BULK COLLECT INTO n_emp_t using P_RQ1, P_RQ2;
open c for select * from table(gfgs_dj_T);
end;
create or replace type n_emp as object
(
dw VARCHAR2(10),
KSRQ date,
SAL NUMBER(10,2),
SAL1 NUMBER(11,2)
SAL2 NUMBER(11,2)
)create or replace type n_emp_v as table of n_empSQL
p_sql varchar(200);
type my_record is record(
dw VARCHAR2(10),
KSRQ date,
SAL NUMBER(10),
SAL1 NUMBER(11),
SAL2 NUMBER(11));
type n_emp_vv is table of my_record;
n_emp_tt n_emp_vv;
BEGIN p_sql := 'select dw,ksrq,sal,sal1,sal2 from emp';
p_sql := p_sql || ' where sal between :1 and :2';
execute immediate p_sql BULK COLLECT
INTO n_emp_tt
using &P_RQ1, &P_RQ2;
open c for
select * from table(gfgs_dj_T);
end;