BULK Loading In Datawarehousing

Bulk Loading is a feature that’s used in Datawarehouse to load bulk data into tables.This is explicit utility that varies with the type of Database.

Example, For Oracle we use  SQL*Loader and for DB we use Load Utility.

                                                                                                                                                                

Most of the Datawarehouse ETL tools used today makes use of Bulk Utility to load data, in some tools we may require extra plug-in also. For Bulk Loading we mainly require two kinds of file:

a)Control File: This file will contain the metadata of the datafile.This file will contain the location of the source file,datatype , column level information.

b)DataFile: This file contains the data to be loaded into the datawarehouse.

Once we have both the source and data files ready then we can write a command script to invoke the bulk loader and then load the data into the Datawarehouse.If there is any information missing in any of the Control file or Datafile then the bulk loading will not happen and will insert no rows.

Simple Procedures and Triggers Used in ETL

HI Guys,

In this post I have discussed about creating Simple Procedures and Triggers while doing ETL.Please check and share your ideas too

• How to Create SimpleProcedures
• How to Create Triggers

Simple Usage of Trigger

 Below given are few examples of Triggers mostly used

a)Using Trigger to track user while Logging Off

CREATE OR REPLACE TRIGGER log_table

BEFORE LOGOFF ON SCHEMA_NAME

BEGIN

INSERT INTO RECORDS_LOG

VALUES (USER, TO_CHAR(SYSDATE,'DD DY HH24:MI:SS'),LOGG_OFF);

END;

b)Using Trigger to track user while Logging On

CREATE OR REPLACE TRIGGER log_table

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO RECORDS_LOG VALUES (USER, TO_CHAR(SYSDATE,'DD DY HH24:MI:SS'), 'LOGG_ON');

END;

c)Simple Trigger to compare Old and New Salary

CREATE OR REPLACE TRIGGER SAL_CHECK

BEFORE UPDATE OF SAL ON EMP

FOR EACH ROW

WHEN (NEW.SAL < OLD.SAL)

BEGIN

RAISE_APPLICATION_ERROR (-20508,

'NEW SALARY CANNOT BE LESS THAN OLD SALARY');

END;

d)Trigger to Store the Old and New values

CREATE OR REPLACE TRIGGER SAL_RECORD

BEFORE UPDATE OF SAL ON EMP

FOR EACH ROW

BEGIN

INSERT INTO RECORDS_SAL VALUES(:OLD.SAL,:NEW.SAL);

END;

e)To check for Insert Update or Delete on a table

CREATE OR REPLACE TRIGGER CHANGE_TRIGGER

AFTER INSERT OR UPDATE OR DELETE ON EMP

BEGIN

INSERT INTO RECORDS_LOG VALUES (USER,TO_CHAR(SYSDATE,'DD DY HH24:MI:SS'));

END;

How to Create Simple Procedures

Below are examples of few procedures which will give and insight about Procedures

a)Simple Procedure to display “HELLO”

create or replace procedure proc

as

begin

dbms_output.put_line('Hello From Procedure');

end;

b)Simple Procedure to find SUM of two input values and output the Total

create or replace procedure proc

(x  in number, y in number, total out number)

is

begin

total := x+y;

end;

c)Simple Procedure using a CURSOR

create or replace procedure proc(dept_id number)

is

empl_record employee%rowtype;

cursor c1 is select * from employee where deptno =dept_id;

begin

open c1;

loop

fetch c1 into empl_record;

exit when c1%notfound;

dbms_output.put_line(empl_record.name);

end loop;

if c1%rowcount<1 then

dbms_output.put_line('Dept No not present);

end if;

close c1;

end;

d)Simple Procedure to Insert records into Table

create or replace procedure Insert_PROC(emp_no number ,dept_no number,emp_name varchar2)

is

begin

insert into EMP_RECORDS(EMP_ID,DEPT_ID,EMP_NAME) values(emp_no,dept_no,emp_name);

end Insert_PROC;

e)Simple Procedure to Update a Table

create or replace procedure Update_PROC(emp_id)

is

begin             

update EMP_RECORDS set SAL=SAL*1.5 where EMP_ID=emp_id;

end;