请问各位高手,不用EXECUTE IMMEDIATE 包装的情况下,应该怎么写呢?例:以下V_CHAR这个参数传不进去!不知道是为什么!
create or replace procedure SP_RPT_FCT_DD38_DTL_BUSI_fwl(dta_flag in int,
v_date in date,
v_flag out int
--V_ROWS_FINISHED OUT INT,
--V_ROWS_REJECTED OUT INT
) authid current_user is
v_char varchar2(10);
v_flag_char varchar2(100);
v_flag_date varchar2(20);
V_ERROR_CODE NUMBER;
V_ERROR_MESSAGE VARCHAR2(5000);
v_dta_flag number(10);begin
v_dta_flag := dta_flag; case
when v_dta_flag = 0 then
V_CHAR := 'PAR_' || TO_CHAR(V_DATE, 'YYYYMM');
when v_dta_flag = 1 then
V_CHAR := 'PAR_' || TO_CHAR(sysdate, 'YYYYMM');
end case;
INSERT INTO RPT.RPT_FCT_DD38_FWL
(CALL_NAME, CNT, CUST_NANE, BUSI_TP)
( --商客大类
select a.call_busi_tp, count(*), '商客', '大类'
from ods.OSOR_EVT_ACT_CALL partition(''||V_CHAR||'') a,
CRM.OSOR_DIM_ORG_DAY b
where b.ORG_NAME_L3 in ('客服质监部商客热线')
and a.crm_create_org_id = b.org_id(+)
group by a.call_busi_tp); commit; v_flag := 1;
DBMS_OUTPUT.PUT_LINE(V_CHAR);EXCEPTION
WHEN OTHERS THEN
V_FLAG := -1;
V_ERROR_CODE := SQLCODE;
V_ERROR_MESSAGE := SQLERRM;
DBMS_OUTPUT.PUT_LINE('ERROR: ' || V_ERROR_CODE || ' ' || v_flag_char || ' ' ||
SQLERRM(V_ERROR_CODE));
DBMS_OUTPUT.PUT_LINE(V_CHAR);
end SP_RPT_FCT_DD38_DTL_BUSI_fwl;
create or replace procedure SP_RPT_FCT_DD38_DTL_BUSI_fwl(dta_flag in int,
v_date in date,
v_flag out int
--V_ROWS_FINISHED OUT INT,
--V_ROWS_REJECTED OUT INT
) authid current_user is
v_char varchar2(10);
v_flag_char varchar2(100);
v_flag_date varchar2(20);
V_ERROR_CODE NUMBER;
V_ERROR_MESSAGE VARCHAR2(5000);
v_dta_flag number(10);begin
v_dta_flag := dta_flag; case
when v_dta_flag = 0 then
V_CHAR := 'PAR_' || TO_CHAR(V_DATE, 'YYYYMM');
when v_dta_flag = 1 then
V_CHAR := 'PAR_' || TO_CHAR(sysdate, 'YYYYMM');
end case;
INSERT INTO RPT.RPT_FCT_DD38_FWL
(CALL_NAME, CNT, CUST_NANE, BUSI_TP)
( --商客大类
select a.call_busi_tp, count(*), '商客', '大类'
from ods.OSOR_EVT_ACT_CALL partition(''||V_CHAR||'') a,
CRM.OSOR_DIM_ORG_DAY b
where b.ORG_NAME_L3 in ('客服质监部商客热线')
and a.crm_create_org_id = b.org_id(+)
group by a.call_busi_tp); commit; v_flag := 1;
DBMS_OUTPUT.PUT_LINE(V_CHAR);EXCEPTION
WHEN OTHERS THEN
V_FLAG := -1;
V_ERROR_CODE := SQLCODE;
V_ERROR_MESSAGE := SQLERRM;
DBMS_OUTPUT.PUT_LINE('ERROR: ' || V_ERROR_CODE || ' ' || v_flag_char || ' ' ||
SQLERRM(V_ERROR_CODE));
DBMS_OUTPUT.PUT_LINE(V_CHAR);
end SP_RPT_FCT_DD38_DTL_BUSI_fwl;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货