null data from bind variable on table insert

[pretzla]

I have a PL/SQL script where I load data from a stored procedure into bind variables. Then, I insert that data from the bind variables into an Oracle table with a simple insert statement.

The execution of the stored procedure and the insert are in a FOR-LOOP.

[code=oracle]
insert into ccd_t
(acct_id,
acct_seq_no,
acct_btn)
values
(to_number(v_ac ct_id),
to_number(v_acc t_seq_no),
substr(v_acct_b tn,1,10)
)
[/code]
After exiting the for-loop, I do a commit. Everything works great unless there is an exception. When an exception occurs (unless it is ORACLE not available) the rows get inserted, but the v_acct_btn is NULL.

There is a WHEN OTHERS for exception processing that prints out the error.

I am thinking the acct_id and acct_seq_no are inserting valid values because of the conversion function, to_number. Since substr is a character function, the v_acct_btn is not dynamic?

Does that make sense? Can anyone verify my conclusion?

The stored procedure always returns an acct_btn, not NULLs so it is not from the stored procedure. Acct_id and acct_seq_no always return numbers.

Or is there some other reason for this odd behavior?

field: acct_id number(10)
field acct_seq_no number(3)
field: acct_btn varchar2(10)
field: v_acct_id varchar2(10);
field: v_acct_seq_no varchar2(3);
field: v_acct_btn varchar2(20);

Thanks.

[amitpatel66]

I have a PL/SQL script where I load data from a stored procedure into bind variables. Then, I insert that data from the bind variables into an Oracle table with a simple insert statement.

The execution of the stored procedure and the insert are in a FOR-LOOP.

[code=oracle]
insert into ccd_t
(acct_id,
acct_seq_no,
acct_btn)
values
(to_number(v_ac ct_id),
to_number(v_acc t_seq_no),
substr(v_acct_b tn,1,10)
)
[/code]
After exiting the for-loop, I do a commit. Everything works great unless there is an exception. When an exception occurs (unless it is ORACLE not available) the rows get inserted, but the v_acct_btn is NULL.

There is a WHEN OTHERS for exception processing that prints out the error.

I am thinking the acct_id and acct_seq_no are inserting valid values because of the conversion function, to_number. Since substr is a character function, the v_acct_btn is not dynamic?

Does that make sense? Can anyone verify my conclusion?

The stored procedure always returns an acct_btn, not NULLs so it is not from the stored procedure. Acct_id and acct_seq_no always return numbers.

Or is there some other reason for this odd behavior?

field: acct_id number(10)
field acct_seq_no number(3)
field: acct_btn varchar2(10)
field: v_acct_id varchar2(10);
field: v_acct_seq_no varchar2(3);
field: v_acct_btn varchar2(20);

Thanks.

Could you please post the error that thrown?
I am not able to make it from your explanation to what exactly is your problem. And also post the full procedure for my reference!!

[pretzla]

Could you please post the error that thrown?
I am not able to make it from your explanation to what exactly is your problem. And also post the full procedure for my reference!!

I will do so.
The exact problem is that rows get inserted with a valid acct_id, but an acct_btn with NULL values when an Exception occurs.

Here is one error that was thrown. I am not worried about the exact error. I know what is causing it. I am questioning why rows still get inserted, but with an acct_btn that is nulls and the acct_id is not nulls. acct_btn is not nulls coming from the stored procedure (btn_sp).

[ERROR]
Unknown Failure - SQLERRM: ORA-02068: following severe error from
tstdb
ORA-03113: end-of-file on communication channel
DECLARE
*
ERROR at line 1
[/ERROR]

[CODE=Oracle]
WHENEVER SQLERROR EXIT SQL.SQLCODE;
SET SERVEROUTPUT ON SIZE 200000;
SET ECHO OFF
SET TIME ON
SET TIMI ON
SET FEEDBACK ON
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD:HH24:MI:SS';
DECLARE
l_acct_not_foun d NUMBER;
v_lookup_cnt NUMBER;
v_acct_id VARCHAR2(20);
v_acct_seq_no VARCHAR2(20);
v_btn VARCHAR2(20);


CURSOR dss_cur IS
SELECT SUBSTR(etn,1,20 ) AS etn
, SUBSTR(btn,1,20 ) as btn
FROM ccd_hst
GROUP BY SUBSTR(etn,1,20 ), SUBSTR(btn,1,20 );

dss_rec dss_cur%rowtype ;
BEGIN

FOR dss_rec IN dss_cur LOOP
btn_sp(dss_rec. etn
, dss_rec.btn
, v_acct_id
, v_acct_seq_no
, v_btn
);

IF v_acct_id = '9999999999' THEN
l_acct_not_foun d := l_acct_not_foun d + 1;
ELSE
INSERT INTO cct_t
( acct_dim_id
, c_id
, acct_id
, acct_seq_no
, acct_btn
, insdate )
VALUES
( cct_t.NEXTVAL
, NULL
, TO_NUMBER(v_acc t_id)
, TO_NUMBER(v_acc t_seq_no)
, SUBSTR(v_btn,1, 10)
, SYSDATE );

END IF;

END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT _LINE('Unknown Failure - SQLERRM: '||SQLERRM);
RAISE;
END;

[/CODE]

[t3chn0n3rd]

[code=oracle]

INSERT INTO ccd_t
(acct_id,
acct_seq_no,
acct_btn)
VALUES
(TO_NUMBER(v_ac ct_id),
TO_NUMBER(v_acc t_seq_no),
SUBSTR(v_acct_b tn,1,10)
)

[/code]

[amitpatel66]

Are you sure the value of the acct_btn retunred by the procedure btn_sp is NOT NUL??

[pretzla]

Are you sure the value of the acct_btn retunred by the procedure btn_sp is NOT NUL??

I am absolutely sure that the acct_btn is never returned NULL. I have tested this procedure for over 1.5 million executions consecutively without ever returning a NULL. Moreover, there are no acct_btn with the value of NULL on the originating table.

[pretzla]

[code=oracle]

INSERT INTO ccd_t
(acct_id,
acct_seq_no,
acct_btn)
VALUES
(TO_NUMBER(v_ac ct_id),
TO_NUMBER(v_acc t_seq_no),
SUBSTR(v_acct_b tn,1,10)
)

[/code]

I am unsure what your post meant. Are you verifying that the problem is because the v_acct_id has a conversion function, and v_acct_btn has a character function? Can you direct me to some documentation, or provide an explanation?

Thanks.