从 oracle 到水晶报表的调用过程

2022-01-13 00:00:00 sql crystal-reports oracle date-arithmetic

我创建了一个 oracle 程序，它正在使用 oracle SQL 编辑器.
我想在水晶报表中使用该过程.
从数据库专家那里，我选择了程序并出现错误消息:

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 行
本机错误:'
================================

我这样声明变量:
================================
str INT;
inv_date 日期；
================================

这是程序，
================================
创建或替换过程 PROC_YEARLYACTIVELIST2(in_year IN VARCHAR)
作为

ctr INT;
str INT;
curr_rowid VARCHAR2(50);
inv_date 日期；

开始

从 TBLACTIVELISTYEARLY2 中删除；
ctr := 1;
对于 1..12 中的 ctr
循环

如果 ctr = 1 那么
str := '01';
inv_date := to_date('31-01-' || in_year || '23:59:59', 'DD-MM-YYYY HH24:MI:SS');
如果结束;
结束;
/
================================

==============================
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;
/
==============================

我一直在努力解决这个问题，但仍然找不到解决方案.
我希望你能帮助我.

谢谢！:)

-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');

这会引发什么错误?

好像年份为空，我们会得到那个错误:

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

相关文章