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

Question

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 1

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;
       /