Onkar Onkar - 3 months ago 7
SQL Question

getting error ORA-01400:cannot insert NULL ... while inserting data with help of rowtype

I'm trying to insert data in a backup table with help of rowtype as below

declare
vl_bkp_rec schema.table1%ROWTYPE;
BEGIN
FOR cur_rec IN
(SELECT *
FROM schema.table1
WHERE column_1 ='3f1d6348-014e-1000-8461-700c000493e0'
AND primary_key_column NOT IN ('8dc81f6e-0156-1000-8291-700e000493e0')
)
LOOP
INSERT INTO schema.backup_table VALUES vl_bkp_rec;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := SUBSTR(SQLERRM, 1, 2999);
DBMS_OUTPUT.PUT_LINE('Handled - error while executing script. =>'|| lv_err_msg );
ROLLBACK;
END;
/


i'm getting below error

Handled - error while executing script. =>ORA-01400: cannot insert NULL into ("schema"."backup_table"."primary_key_column")


but
table1
and
backup_table
have exactly same structure. (created
backup_table
as below)

CREATE TABLE schema.backup_table AS
(SELECT * FROM schema.table1 WHERE rownum <1
);


and select query used above fetches valid data. What am I doing wrong here?

Answer

You need to use your variable in the below way. Currently in your code the variable declared as table type is not getting filled: See below how to use it.

  declare
     vl_bkp_rec table1%ROWTYPE; 
     BEGIN   
        FOR cur_rec IN
                  (SELECT      * 
                     FROM table1             
                  )
        LOOP                                
            vl_bkp_rec:=cur_rec; --Assign values of the cursor variable to your variable

            INSERT INTO  backup_table VALUES vl_bkp_rec;

        END LOOP; 
    COMMIT;
       EXCEPTION
       WHEN OTHERS THEN
         --lv_err_msg := SUBSTR(SQLERRM, 1, 2999); ---you need to decalre it befor using it
         DBMS_OUTPUT.PUT_LINE('Handled - error while executing script. =>' );
         ROLLBACK;
       END;
       /