2
0
Stored Procedure Operations: Recently come up with a requirement where Users were facing hard times while querying the customer number in their application based on the Snowflake Database. Users were getting the message “Customer Number does not exist” for some random customer numbers. After verification with the Users we find these were highly prioritize customers and should be exists in the database.
It seems either these customers did not feed to the application by the legacy system or there was some load failed in the past which has prevented these records to get inserted into the Snowflake. To determine how many such failed records are ,Business has decided to share the list of all prioritized customers in the sheet and we need to develop a mechanism to identify missing records in Snowflake database and push them into a Error Log or Audit table to examine at the later stage.
Also business did not allow to access this table and they want the list of missing customers should be compile in one sheet which we would be upload to the Staging area. Post this Business will download the file and verify the customers.
However, to implement the scenario, we have created following two JavaScript procedures.
- customer_validate(CUSTOMERNUM varchar(100)
- looping_process()
Table creation:
Following Tables have been used:
//Firstly, Original table in DB to hold Customer details
create or replace table customer_detail
(cust_no varchar(10),cust_name varchar(100),Status varchar(9),CRID varchar(30));
//Secondly, Error Log table to capture the Missing Customer
CREATE OR REPLACE TABLE error_log (customer varchar ,error_code number, error_state string, error_message string, stack_trace string);
//Hold the List of Customers shared by Business
create or replace table customer_list (cust_no varchar(10));
Insert Data into tables for this POC:
insert into customer_detail values
('9560315720','sachin','Active','abcd'),
('9560315721','amit','Active','abcd');
insert into customer_list values
('9560315720'), ('9560315721'), ('9560315722')
Note: ‘9560315722’ does not exists inside the customer_detail table, so should be insert into the ERROR LOG table.
Customer Validation Procedure:
Wrapping Procedure :
Via this procedure we are reading the Customer from customer_list table and passing them as Input argument to the customer_validate procedure.
Through this we have implemented Error Logging, Record Looping, Data Loading, Unloading, Input Parameters and PROC call … all Stored Procedure Operations at one place.
