您好,以下是我的代码 . 我需要使用视图中的数据创建一个视图并在另一个游标中获取数据 . 但是当我执行我的代码时,我收到错误
“ORA-06550:第56行,第37列:PL / SQL:ORA-00942:表或视图不存在ORA-06550:第52行,第9列:PL / SQL:忽略SQL语句”
这是什么问题?提前致谢 .
declare
drop_view_sql varchar2(100) := 'drop view rv_task_number_view';
type tasks_rec is record(task_number varchar2(20));
type t_tab is table of tasks_rec;
tasks_tab t_tab;
notes_rec xx_fs_mob_loc_rec.task_notes_rec;
notes_tab xx_fs_mob_loc_rec.task_notes_tab;
begin
execute immediate 'create view apps.rv_task_number_view as
SELECT distinct ct.task_number
FROM csf_ct_tasks ct ,
cs_estimate_details ced,
csf_debrief_headers cdh,
csf_debrief_lines cdl,
jtf_task_assignments jta
WHERE 1=1
and jta.task_id = ct.task_id
and jta.task_assignment_id = cdh.task_assignment_id(+)
and cdh.debrief_header_id = cdl.debrief_header_id(+)
and cdl.debrief_line_id = ced.source_id(+)
AND ((ct.planned_end_date between (sysdate-30) and (sysdate+30)) or (ct.scheduled_end_date between (sysdate-30) and (sysdate+30)))
and jta.resource_id = 10153';
execute immediate 'grant select on apps.rv_task_number_view to apps';
DBMS_OUTPUT.PUT_LINE(sqlerrm);
declare
CURSOR c2 is
SELECT ct.task_id,jtf_note_id, jnv.notes,
jnv.creation_date,jnv.last_update_date
FROM
jtf_notes_vl jnv,
csf_ct_tasks ct,apps.rv_task_number_view rvt
WHERE
jnv.source_object_code = 'TASK'
and ct.task_number = jnv.source_number
and ct.task_number = rvt.task_number
order by task_id, jtf_note_id;
begin
open c2;
fetch c2 bulk collect into notes_tab;
DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
execute immediate drop_view_sql;
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
1 回答
您正在尝试访问
TABLE/VIEW
,该TABLE/VIEW
是使用动态SQL
在同一个块中创建的SELECT
它(静态SQL
)每个
PL/SQL
块都会在执行前编译 . 虽然编译rv_task_number_view
不可用!所以,
SELECT
也需要是动态的!