从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屋！