包头
create or replace package pkg_test is
function f_getsumareaname(in_area_id int, in_area_type int) return varchar2;
procedure p_test;
procedure p_main;
end pkg_test;
包体
create or replace package body pkg_test is
function f_getsumareaname(in_area_id int, in_area_type int) return varchar2 is
v_sumareaname varchar2(100);
begin
begin
select t.area_name
into v_sumareaname
from t_area t
where t.area_type_id = in_area_type
and t.area_id = in_area_id;
exception
when others then
v_sumareaname := null;
end;
return v_sumareaname;
end f_getsumareaname; procedure p_test is
begin
begin
execute immediate '
drop table T_OWEFEE';
exception
when others then
null;
end; execute immediate '
create table t_owefee as
select f_getsumareaname(a.area_id,2) as xj,
f_getsumareaname(a.area_id,3) as zj,
f_getsumareaname(a.area_id,5) as pq,
f_getsumareaname(a.area_id,6) as sq,
a.owefee
from tmp_ffp_servacctarea a';
end p_test; procedure p_main is
begin
p_test;
end;begin
null;
end pkg_test;编译通过后,执行的时候,就报错了。ORA-00904: "F_GETSUMAREANAME": invalid identifier
但是把调用函数的地方都改成加上包名pkg_test.f_getsumareaname后,
能执行了,但是中间不清楚是不是 有什么错误,不停的重新执行
中间我是把所有的异常都捕获的啊
请高手指点一下
create or replace package pkg_test is
function f_getsumareaname(in_area_id int, in_area_type int) return varchar2;
procedure p_test;
procedure p_main;
end pkg_test;
包体
create or replace package body pkg_test is
function f_getsumareaname(in_area_id int, in_area_type int) return varchar2 is
v_sumareaname varchar2(100);
begin
begin
select t.area_name
into v_sumareaname
from t_area t
where t.area_type_id = in_area_type
and t.area_id = in_area_id;
exception
when others then
v_sumareaname := null;
end;
return v_sumareaname;
end f_getsumareaname; procedure p_test is
begin
begin
execute immediate '
drop table T_OWEFEE';
exception
when others then
null;
end; execute immediate '
create table t_owefee as
select f_getsumareaname(a.area_id,2) as xj,
f_getsumareaname(a.area_id,3) as zj,
f_getsumareaname(a.area_id,5) as pq,
f_getsumareaname(a.area_id,6) as sq,
a.owefee
from tmp_ffp_servacctarea a';
end p_test; procedure p_main is
begin
p_test;
end;begin
null;
end pkg_test;编译通过后,执行的时候,就报错了。ORA-00904: "F_GETSUMAREANAME": invalid identifier
但是把调用函数的地方都改成加上包名pkg_test.f_getsumareaname后,
能执行了,但是中间不清楚是不是 有什么错误,不停的重新执行
中间我是把所有的异常都捕获的啊
请高手指点一下
但是中间出了异常,job会不停的重新执行,是什么原因引起的啊?
job没有失败次数。
你的select语句是用exec 执行的所以引用的函数是需要是需要加上包名的
然后 你的程序尾部有问题
procedure p_main is
begin
p_test;
end; begin
null;
这里你自己改吧
--包头
create or replace package pkg_test is
function f_getsumareaname(in_area_id int, in_area_type int) return varchar2;
procedure p_test;
procedure p_main;
end pkg_test;
--包体
create or replace package body pkg_test is
function f_getsumareaname(in_area_id int, in_area_type int) return varchar2 is
v_sumareaname varchar2(100);
begin
begin
select t.area_name
into v_sumareaname
from t_area t
where t.area_type_id = in_area_type
and t.area_id = in_area_id; exception
when others then
v_sumareaname := null;
end;
return v_sumareaname;
end f_getsumareaname; procedure p_test is
begin
begin
execute immediate 'drop table T_OWEFEE';
exception
when others then
null;
end;
execute immediate 'create table t_owefee as select f_getsumareaname(a.area_id,2) as xj,f_getsumareaname(a.area_id,3) as zj,f_getsumareaname(a.area_id,5) as pq,f_getsumareaname(a.area_id,6) as sq,a.owefee from tmp_ffp_servacctarea a';
end p_test; procedure p_main is
begin
p_test;
end; begin
null;
end pkg_test;
在包体函数创建时加上关健字deterministic试一下,如:
function f_getsumareaname(in_area_id int, in_area_type int) return varchar2 deterministic is
v_sql := 'create table as xxxxxxx'; execute immediate(v_sql);