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.

insert into ccd_t
(acct_id,
acct_seq_no,
acct_btn)
values
(to_number(v_acct_id),
 to_number(v_acct_seq_no),
 substr(v_acct_btn,1,10)
)
 

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.

insert into ccd_t
(acct_id,
acct_seq_no,
acct_btn)
values
(to_number(v_acct_id),
 to_number(v_acct_seq_no),
 substr(v_acct_btn,1,10)
)
 

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]

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_found  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_found := l_acct_not_found + 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_acct_id)
            , TO_NUMBER(v_acct_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;
 
 

[t3chn0n3rd]

 
INSERT INTO ccd_t
(acct_id,
acct_seq_no,
acct_btn)
VALUES
(TO_NUMBER(v_acct_id),
 TO_NUMBER(v_acct_seq_no),
 SUBSTR(v_acct_btn,1,10)
)
 
 

[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]

 
INSERT INTO ccd_t
(acct_id,
acct_seq_no,
acct_btn)
VALUES
(TO_NUMBER(v_acct_id),
 TO_NUMBER(v_acct_seq_no),
 SUBSTR(v_acct_btn,1,10)
)
 
 

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.