在dw中当数据源是存储过程时怎么老出错啊？

[changqr]

环境：pb8＋oracle8.1.7.
我想用存储过程（存储过程中调用了oracle得dbms_sql包来得到部分得数据，用到了包中得如下过程：open_cursor、parse、define_column、EXECUTE、fetch_rows、column_value、close_cursor）作为数据源来建立dw，就是建立不起来（以前一直是好的）：出错得信息是ORA-06502/ORA-06512/ORA-01007.
曾尝试用EXECUTE IMMEDIATE试过，同样得问题，请指教！

[waterstony]

你可以让程序管理事务
执行存储过程、对于以存储过程为源的数据窗口retrieve时,，sqlca.autocommit = true ,执行完后用sqlca.autocommit = false

[changqr]

我的整个过程中是不涉及事务的啊

[supershb]

ORA-06502/ORA-06512/ORA-01007.
我把上面三个错误号的原因给录下来。
----------------------------------------------------------------------------
ORA-06502 PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
------------------------------------------------------------------------------
ORA-06512 at string line string

Cause: Backtrace message as the stack is unwound by unhandled exceptions.

Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or database administrator.
------------------------------------------------------------------------------
ORA-01007 variable not in select list

Cause: A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error.

Action: Determine which of the problems listed caused the problem and take appropriate action.
－－－－－－－－－－－－－－说明－－－－－－－－－－－－－－－－－－－－－－－－

可能是你的存储过程以前没有考虑到一些边界数据，例如：NULL等等。还可能是一些垃圾数据导致你正确的存储过程发生错误。
总之，问题出现在你的数据上，你的存储过程没有处理异常情况。
请仔细检查你的存储过程，尤其是对异常的数据的处理。

[changqr]

CREATE OR REPLACE PROCEDURE P_LOGSTAT (
v_batch_code IN NUMBER, --批次号
v_errno OUT NUMBER, --返回错误号：0表示成功执行；否则表示出错
po_retset OUT pk_type.ref_cursor--返回结果集
) IS
vv_batch_code VARCHAR2(20);
v_pas_file NUMBER:=0;--性能调优模块输出文件个数
v_pp_file NUMBER:=0;--预处理模块输入文件个数
v_pp_source_cdrs NUMBER:=0;--预处理输入话单数
v_pp_invalid_cdrs NUMBER:=0;--预处理无效话单数
v_pp_undis_cdrs NUMBER:=0;--预处理无法分拣话单数
v_pp_dis_cdrs NUMBER:=0;--预处理分拣话单数
v_pp_corr_cdrs NUMBER:=0;--预处理输出话单数
v_pp_err_cdrs NUMBER:=0;--预处理异常话单数
v_rate_source_cdrs1 NUMBER:=0;--批价原始话单数1
v_rate_err_cdrs1 NUMBER:=0;--批价错误话单数1
v_rate_corr_cdrs1 NUMBER:=0;--批价输出正确话单数1
v_rate_f_cdrs1 NUMBER:=0;--排重输出正常话单1
v_rate_f_rep_cdrs1 NUMBER:=0;--排重输出重复话单数1
v_sett_source_cdrs1 NUMBER:=0;--结算原始话单数1
v_sett_source_cdrs2 NUMBER:=0;--结算原始话单数2
v_sett_corr_cdrs2 NUMBER:=0;--正常参与结算的话单数2
v_sett_err_cdrs2 NUMBER:=0;--未能参与结算的话单数2
v_sett_counts2 NUMBER:=0;--结算的记录条数2
v_stat_cdrs NUMBER:=0;--数据库中正确话单数
v_stat_count NUMBER:=0;--数据库中统计记录数
v_dispense_file NUMBER:=0;--分发的文件数
v_stat_cdrs_temp NUMBER:=0;
v_stat_count_temp NUMBER:=0;
v_sql VARCHAR2(1023);
v_cursor NUMBER;
v_rows NUMBER;
v_table_name user_tables.table_name%TYPE;

CURSOR c_file_stat_1(batch_code_tmp VARCHAR2) IS
SELECT a.log_type log_type,
SUM(nvl(a.in_file_nums,0)) in_file_nums,
SUM(nvl(a.out_file_nums,0)) out_file_nums,
SUM(nvl(a.num_cdrs,0)) num_cdrs,
SUM(nvl(a.durations,0)) durations,
SUM(nvl(a.sett_fees,0)) sett_fess
FROM te_stat_log a
WHERE (a.log_type = 23
OR a.log_type BETWEEN 2 AND 13)
AND a.batch_code IN (SELECT DISTINCT substr(b.batch_code,1,9)
FROM te_stat_log b
WHERE b.batch_code LIKE '%'||batch_code_tmp)
GROUP BY a.log_type;
CURSOR c_file_stat_2(batch_code_tmp VARCHAR2) IS
SELECT a.log_type log_type,
SUM(nvl(a.in_file_nums,0)) in_file_nums,
SUM(nvl(a.out_file_nums,0)) out_file_nums,
SUM(nvl(a.num_cdrs,0)) num_cdrs,
SUM(nvl(a.durations,0)) durations,
SUM(nvl(a.sett_fees,0)) sett_fees
FROM te_stat_log a
WHERE a.log_type BETWEEN 13 AND 18
AND a.batch_code LIKE '%'||batch_code_tmp
GROUP BY log_type;
CURSOR c_table IS
SELECT table_name
FROM user_tables
WHERE lower(table_name) LIKE 'tl%settle%'
ORDER BY table_name;
BEGIN
--初始化
ROLLBACK;
v_errno := 0;
--得到带.的9位批次号
vv_batch_code := trim(to_char(v_batch_code,'00000000'));
vv_batch_code := substr(vv_batch_code,1,3)||'.'||substr(vv_batch_code,4);
--得到各模块处理的话单条数
FOR tmp_c_file_stat IN c_file_stat_1(vv_batch_code) LOOP
--改名的文件个数
IF tmp_c_file_stat.log_type = 2 THEN
v_pas_file := tmp_c_file_stat.out_file_nums;
--预处理输入话单数与记录数
ELSIF tmp_c_file_stat.log_type = 23 THEN
v_pp_file := tmp_c_file_stat.in_file_nums;
v_pp_source_cdrs := tmp_c_file_stat.num_cdrs;
ELSIF tmp_c_file_stat.log_type = 3 THEN
v_pp_corr_cdrs := tmp_c_file_stat.num_cdrs;
--预处理无效话单数
ELSIF tmp_c_file_stat.log_type = 4 THEN
v_pp_invalid_cdrs := tmp_c_file_stat.num_cdrs;
--预处理无法分拣话单数
ELSIF tmp_c_file_stat.log_type = 5 THEN
v_pp_undis_cdrs := tmp_c_file_stat.num_cdrs;
--预处理错误话单数
ELSIF tmp_c_file_stat.log_type = 6 THEN
v_pp_err_cdrs := tmp_c_file_stat.num_cdrs;
--预处理拆分话单
ELSIF tmp_c_file_stat.log_type = 8 THEN
v_pp_dis_cdrs := tmp_c_file_stat.num_cdrs;
--批价输出正确
ELSIF tmp_c_file_stat.log_type = 9 THEN
v_rate_corr_cdrs1 := tmp_c_file_stat.num_cdrs;
--批价输出异常话单
ELSIF tmp_c_file_stat.log_type = 10 THEN
v_rate_err_cdrs1 := tmp_c_file_stat.num_cdrs;
--批价输出异常
ELSIF tmp_c_file_stat.log_type = 11 THEN
v_rate_f_cdrs1 := tmp_c_file_stat.num_cdrs;
ELSIF tmp_c_file_stat.log_type = 12 THEN
v_rate_f_rep_cdrs1 := tmp_c_file_stat.num_cdrs;
ELSIF tmp_c_file_stat.log_type = 13 THEN
v_sett_source_cdrs1 := tmp_c_file_stat.num_cdrs;
END IF;
END LOOP;
v_pp_corr_cdrs := v_pp_source_cdrs - v_pp_invalid_cdrs - v_pp_undis_cdrs + v_pp_dis_cdrs - v_pp_err_cdrs;
v_rate_source_cdrs1 := v_rate_err_cdrs1 + v_rate_corr_cdrs1;

FOR tmp_c_file_stat IN c_file_stat_2(vv_batch_code) LOOP
IF tmp_c_file_stat.log_type = 13 THEN
v_sett_source_cdrs2 := tmp_c_file_stat.num_cdrs;
ELSIF tmp_c_file_stat.log_type = 14 THEN
v_sett_counts2 := tmp_c_file_stat.num_cdrs;
ELSIF tmp_c_file_stat.log_type = 15 THEN
v_sett_corr_cdrs2 := v_sett_source_cdrs2 - tmp_c_file_stat.num_cdrs;
ELSIF tmp_c_file_stat.log_type = 18 THEN
v_dispense_file := tmp_c_file_stat.out_file_nums;
END IF;
END LOOP;

FOR tmp_c_table IN c_table LOOP

v_sql := 'select sum(a.num_cdrs),count(*) from '||tmp_c_table.table_name||' a WHERE a.batch_code2 = '||to_char(v_batch_code);
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,v_sql,dbms_sql.v7);
dbms_sql.define_column(v_cursor,1,v_stat_cdrs_temp);
dbms_sql.define_column(v_cursor,2,v_stat_count_temp);
v_rows := dbms_sql.EXECUTE(v_cursor);
v_rows := dbms_sql.fetch_rows(v_cursor);
dbms_sql.column_value(v_cursor,1,v_stat_cdrs_temp);
dbms_sql.column_value(v_cursor,2,v_stat_count_temp);
dbms_sql.close_cursor(v_cursor);
v_stat_cdrs := v_stat_cdrs + nvl(v_stat_cdrs_temp,0);
v_stat_count := v_stat_count + nvl(v_stat_count_temp,0);
END LOOP;

OPEN po_retset FOR
SELECT v_pas_file pas_file_num,
v_pp_file pp_file_num,
v_pp_source_cdrs pp_source_cdrs,
v_pp_err_cdrs pp_error_cdrs,
v_pp_invalid_cdrs pp_invalid_cdrs,
v_pp_undis_cdrs pp_undis_cdrs,
v_pp_dis_cdrs pp_dis_cdrs,
v_pp_corr_cdrs pp_corr_cdrs,
v_rate_source_cdrs1 rate_source_cdrs1,
v_rate_err_cdrs1 rate_err_cdrs1,
v_rate_corr_cdrs1 - v_rate_f_cdrs1 rate_corr_cdrs1,
v_rate_f_rep_cdrs1 rate_rep_cdrs1,
v_sett_source_cdrs1 sett_source_cdrs1,
v_sett_source_cdrs2 sett_source_cdrs2,
v_sett_corr_cdrs2 sett_corr_cdrs2,
v_sett_err_cdrs2 sett_err_cdrs2,
v_sett_counts2 sett_count2,
v_dispense_file dispense_file,
v_stat_cdrs stat_cdrs,
v_stat_count stat_count
FROM dual;
EXCEPTION
WHEN OTHERS THEN
v_errno := to_char(SQLCODE);
END;

[changqr]

逻辑上很简单的
由于受长度的限制，我删掉了部分的注释！

[changqr]

出问题的语句在第三个for循环内

[waterstony]

orcal调用存储过程用这种方法更有效：

声明为RPC：
SUBROUTINE CalcAmount(string LS_In1, ref string LS_Out2) RPCFUNC

调用：
string in_parm1
string out_parm2

in_parm1 = "input value"
out_parm2 = space(50) // preallocating space for string

SQLCA.CalcAmount(in_parm1, out_parm2)

[changqr]

to supershb
同样的内容，我以前建存储过程成功过的啊。我再试一下，是不是和环境有关。
另外一个方面，oralce的dbms_sql包在编译时是不进行语法检查的，只在具体执行时才进行相关的语法检查，总之我觉得有点怪！

[supershb]

现在我也无法说什么了，请把你的存储过程贴出来吧。
这样我比较容易发现问题。

[supershb]

v_sql := 'select sum(a.num_cdrs),count(*) from '||tmp_c_table.table_name||' a WHERE a.batch_code2 = '||to_char(v_batch_code);
这句话有隐患，请再加以调试。