从oracle到水晶报表的调用过程 [英] Calling procedure from oracle to crystal report
问题描述
我创建了一个oracle过程,它正在使用oracle SQL编辑器。
我想使用Crystal报表中的过程。
从数据库专家,程序,并显示错误讯息:
I've created an oracle procedure and it's working on oracle SQL editor.
I want to use the procedure in Crystal report.
From database expert, I select the procedure and there's an error message:
====================== =======
查询引擎错误:'ADO错误代码:0x
资料来源:OraOLEDB
描述:ORA-01850:小时必须介于0到23之间。\\
ORA-06512:在NPLS.PROC_YEARLYACTIVELIST2,第19行<
ORA-06512:第1行b本机错误:'
============================
¨
我声明这样的变量:royalty
======================== ====_LINK
str INT;
inv_date DATE;
================ ================ $ b $bÁ
以下是程序,
$ $ $ $ $ ======================
CREATE OR REPLACE PROCEDURE PROC_YEARLYACTIVELIST2(in_year IN VARCHAR)
AS
ctr INT;
str INT;
curr_rowid VARCHAR2(50);
inv_date DATE;
$ b $b¨
BEGIN
0047
从TBLACTIVELISTYEARLY2删除;
ctr := 1;
FOR ctr IN 1..12
如果ctr = 1,则
LOOPÁ
THEN
str:='01';
inv_date:= to_date('31 -01-'|| in_year || '23:59:59','DD-MM-YYYY HH24:MI:SS');
END IF;
END;
/
==============================
==============================
Query Engine Error: 'ADO Error Code: 0x
Source: OraOLEDB
Description: ORA-01850: hour must be between 0 and 23
ORA-06512: at "NPLS.PROC_YEARLYACTIVELIST2", line 19
ORA-06512: at line 1
Native Error: '
==============================
I declare the variables like this:
==============================
str INT;
inv_date DATE;
==============================
Here's the procedure,
==============================
CREATE OR REPLACE PROCEDURE PROC_YEARLYACTIVELIST2(in_year IN VARCHAR)
AS
ctr INT;
str INT;
curr_rowid VARCHAR2(50);
inv_date DATE;
BEGIN
DELETE FROM TBLACTIVELISTYEARLY2;
ctr := 1;
FOR ctr IN 1..12
LOOP
IF ctr = 1 THEN
str := '01';
inv_date := to_date('31-01-' || in_year || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
END IF;
END;
/
==============================
我一直试图解决这个问题这么久,仍然找不到解决方案。
我希望你能帮助me。
$
\\
谢谢! :)
¥b $ b
-Michelle(在工作受训者身上)
I've been trying to solve this problem for so long and still can't find solution.
I hope you can help me.
Thanks! :)
-Michelle (On the job trainee)
推荐答案
一种可能性是,in_year未被Crystal传递或以NULL传递。
one possibility is that in_year is not being passed in by Crystal or passed in as NULL.
请验证这一点。您可以添加此代码作为快速检查:
please verify this. you can add this code as a quick check:
if (trim(in_year) is null) or length(in_year != 4))
then
raise_application_error(-20000, 'year is invalid: ' || in_year);
end if;
inv_date := to_date('31-01-' || in_year || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
这会引发什么错误?
如果年份为null,我们会收到以下错误:
as if the year was null, we'd get that error:
SQL> declare
2 inv_date DATE;
3 in_year varchar2(42) ;
4 begin
5 inv_date := to_date('31-01-' || in_year || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS');
6 end;
7 /
declare
*
ERROR at line 1:
ORA-01850: hour must be between 0 and 23
ORA-06512: at line 5
这篇关于从oracle到水晶报表的调用过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!