Oracle DML LOG ERROR Alternative in Snowflake

Oracle database supports many user friendly error handling features that make the developer’s life easier when working on DML statements such as INSERT, UPDATE, DELETE, MERGE. One of the such features is dml error logging. Using this feature, your DML statements will continue by logging errors in the error table created using the DBMS_ERRLOG package. This feature is not yet available in Snowflake. In this article, we will check Oracle DML LOG ERROR alternative methods in Snowflake scripting.

Oracle DML Error Logging

With Oracle DML error logging feature, you can add a clause to your bulk DML statements (INSERT, UPDATE, DELETE and MERGE) to prevent the statement failing on error. Error rows are added to a specifically-created errors table for investigation. Error table is created using the DBMS_ERRLOG package. In addition, we can control the number of bad records (REJECT LIMIT UNLIMITED/row_limit_number) before failing the entire statement. This REJECT LIMIT subclause is an optional. This subclause indicates the maximum number of errors that can be encountered before the DML statement terminates and rolls back.

Following is the example of Oracle error log with reject row limit.

INSERT INTO target_table
 SELECT * FROM source_table
 LOG ERRORS INTO target_errors
 REJECT LIMIT UNLIMITED;

Once the above is statement executed, you can find the error records from target_errors table.

DML LOG ERROR Alternative in Snowflake

Snowflake does supports exception handling in stored procedures. If you are migrating an Oracle stored procedure with DML LOG ERROR to reject error rows into error table, you can use Snowflake error handling to capture error records into error table whenever there is an error during DML operations inside loop such as cursors with loops.

There are multiple methods that you can use when implementing Oracle DML LOG ERROR feature in Snowflake. We will discuss a couple of methods in this article.

Snowflake Script Error Handling to Capture Reject Row – DML Log Error Alternative

In the following Oracle examples, you can see that the cursor is being used to loop through the data and insert into the target table. You can see the highlighted part where the error table is being populated in case of any errors during that data insert.

 CREATE  OR REPLACE PROCEDUREv_test_prc (pv_cursor_limit IN NUMBER)
    IS
        CURSOR lcur_dim_tstg IS
            SELECT ID, NAME FROM v_test_stg;
        TYPE dim_tstg_ntab IS TABLE OF lcur_dim_tstg%ROWTYPE;
        lv_dim_tstg_tab dim_tstg_ntab;			
    BEGIN
        OPEN lcur_dim_tstg;

        LOOP
            
            FETCH lcur_dim_tstg
                BULK COLLECT INTO lv_dim_tstg_tab
                LIMIT pv_cursor_limit;

            EXIT WHEN lv_dim_tstg_tab.COUNT = 0;
        
            FORALL lrec_indx IN INDICES OF lv_dim_tstg_tab 
                insert into v_test_ld (id, name)
                     VALUES (lv_dim_tstg_tab (lrec_indx).ID,
                             lv_dim_tstg_tab (lrec_indx).NAME)
                        LOG ERRORS INTO V_TEST_LD_ERROR 
                                (lv_dml_err_tag)
                                REJECT LIMIT UNLIMITED;
								
            lv_dim_tstg_tab.delete;
          
        END LOOP;
        CLOSE lcur_dim_tstg;
    END load_scd1_target_stage;

Similar functionality is not yet supported in Snowflake. But, as a Snowflake supports exception handling. As a workaround you can use exception handling to capture error rows and insert them into an explicit error table. Unlike Oracle, there is no system procedure or function to create an error table. You need to create error table manually.

Following is the Snowflake Script example to handle LOG ERROR to capture REJECT LIMIT in Snowflake.

create or replace procedure v_test_prc(pv_cursor_limit number)
  returns int
  language sql
  EXECUTE AS CALLER
  as
  $$
  DECLARE
        r1 string;
        r2 string;
        cnt int:=0;
      lcur_dim_tstg  CURSOR  for
            SELECT ID, NAME FROM v_test_stg;
BEGIN
	  for record in lcur_dim_tstg
      do
      begin
       r1:=record.id;
       r2:=record.name;
        insert into v_test_ld 
        values (:r1, :r2);
        exception 
          when other then 
          insert into v_test_err values (1, 'error');              
      end;
      end for;
      exception 
        when other then 
        insert into v_test_err values (1, 'error');
	  return cnt;
end;
$$ ;

Snowflake Snowpark Error Handling to Capture Reject Row – DML Log Error Alternative

And you can write similar logic in Snowflake Snowpark as well.

Following is the Snowpark example to handle LOG ERROR to capture REJECT LIMIT in Snowflake.

def v_test_prc(pv_cursor_limit):

    # Translated Query:
    lcur_dim_tstg = session.sql("SELECT ID, NAME FROM v_test_stg").collect()

    for lv_dim_tstg_tab in lcur_dim_tstg:
        try:
            session.sql(
                "insert into v_test_ld VALUES ( '" + str(lv_dim_tstg_tab["ID"]) + "','" + lv_dim_tstg_tab[
                    "NAME"] + "')").collect()
        except Exception as e:
            print (e)
            session.sql("insert into v_test_err values (1, 'ERROR')").collect()