ORACLE声明游标时报1405错？

显示全部楼层 · 2008-12-25 12:01:03

最近项目中遇到这个问题，Pro*C 中声明游标时报1405错，不知为何？
具体语句如下：
EXEC SQL DECLARE cur_txn_map_1 CURSOR FOR

SELECT

txn_date,

txn_code,

txn_amount,

txn_acct,

sa_slmt_cur_code,

sa_slmt_amount,

sa_auth_rsp_code,

dm_auth_rsp_code,

dm_mess_num,

rsp_code,

acct_flag,

sys_flag,

stlm_date,

stlm_flag

FROM

tbl_txn_map

WHERE

transtime
= :ltTblTxnMap.transtime

and transid
= :ltTblTxnMap.transid

and ref_number= :ltTblTxnMap.ref_number

;
请各位帮助~~~~

千问 · 2008-12-25 12:01:03

根据你的错误号，我想你这里的错误信息应该不是发生在cursor声明这里，而是在fetch的那一句。
对于可能会返回null的列，在into到宿主变量的时候，给宿主变量后面再加一个indicator变量，这个错误应该就不会出来了。
ORA-01405:
fetched column value is NULL
Cause:
The INTO clause of a FETCH operation contained a NULL value, and no indicator was used. The column buffer in the program remained unchanged, and the cursor return code was +2. This is an error unless you are running Oracle with DBMS=6, emulating version 6, in which case it is only a warning.
Action:
You may do any of the following:
* Use the NVL function to convert the retrieved NULL to another value, such as zero or blank. This is the simplest solution.
* Use an indicator to record the presence of the NULL. You probably should use this option when you want a specific action to be taken when a NULL arises.
* Revise the cursor definition so that no columns possibly containing NULL values are retrieved.

千问 · 2008-12-25 12:01:03

原帖由 neilkooo 于 2009-10-22 10:32 发表
根据你的错误号，我想你这里的错误信息应该不是发生在cursor声明这里，而是在fetch的那一句。
对于可能会返回null的列，在into到宿主变量的时候，给宿主变量后面再加一个indicator变量，这个错误应该就不会出来了。
ORA-01405:
fetched column value is NULL
Cause:
The INTO clause of a FETCH operation contained a NULL value, and no indicator was used. The column buffer in the program remained unchanged, and the cursor return code was +2. This is an error unless you are running Oracle with DBMS=6, emulating version 6, in which case it is only a warning.
Action:
You may do any of the following:
* Use the NVL function to convert the retrieved NULL to another value, such as zero or blank. This is the simplest solution.
* Use an indicator to record the presence of the NULL. You probably should use this option when you want a specific action to be taken when a NULL arises.
* Revise the cursor definition so that no columns possibly containing NULL values are retrieved.
我明白在fetch的时候会出现1405错，但是它的确是在DECLARE CURSOR的时候出错了，还没有去OPEN呢~~~

千问 · 2008-12-25 12:01:03

可以看一下你的代码么？
只从你的那条语句跟错误号，不好找原因......