ADD RECORD INTO THE EXISTING TABLE RECORD USING SQL LOADER CONTROL FILE

AS SALAMO ALAIKUM

TABLE STRUCTURE
=================
create table XXX_EMP_STG
(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(20) NOT NULL,
    JOB VARCHAR(20) NOT NULL,
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(8,3),
    COMM NUMBER(8,3),
    DEPTNO NUMBER(2)
)

=====================
CONTROL FILE
=====================

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'D:\ORACLEDEMO\SQLLOADER\emp.csv'
BADFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_BAD.bad'
DISCARDFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_DISCARD.dis'
APPEND INTO TABLE XXX_EMP_STG
WHEN COMM!='NULL'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

NOTE - 

RECORD WANTED TO INSERT INTO XXX_EMP_STG TABLE WHERE AL READY SOME RECORD IS EXISTING.

==============

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

7370,SHAHNAWAZ,ENGG,7902,17-Dec-1980,8000,100,20

======================

MA ASALAAM

-----

PASSION FOR ORACLE

GENERATE THE DISCARD FILE EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH

TABLE STRUCTURE
================


create table XXX_EMP_STG
(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(20) NOT NULL,
    JOB VARCHAR(20) NOT NULL,
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(8,3),
    COMM NUMBER(8,3),
    DEPTNO NUMBER(2)
)


==============
CONTROL FILE 
=============


OPTIONS (SKIP=1)
LOAD DATA
INFILE 'D:\ORACLEDEMO\SQLLOADER\emp.csv'
BADFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_BAD.bad'
DISCARDFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_DISCARD.dis'
INSERT INTO TABLE XXX_EMP_STG
WHEN COMM != 'NULL'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

=======

CSV FILE

=======

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

7369,SMITH,CLERK,7902,17-Dec-1980,800,,20

7499,ALLEN,SALESMAN,7698,20-Feb-1981,1600,300,30

7521,WARD,SALESMAN,7698,22-Feb-1981,1250,500,30

7566,JONES,MANAGER,7839,2-Apr-1981,2975,,20

7654,MARTIN,SALESMAN,7698,28-Sep-1981,1250,1400,30

7698,BLAKE,MANAGER,7839,1-May-1981,2850,,30

7782,CLARK,MANAGER,7839,9-Jun-1981,2450,,10

7788,SCOTT,ANALYST,7566,19-Apr-1987,3000,,20

7839,KING,PRESIDENT,,17-Nov-1981,5000,,10

7844,TURNER,SALESMAN,7698,8-Sep-1981,1500,0,30

7876,ADAMS,CLERK,7788,23-May-1987,1100,,20

7900,JAMES,CLERK,7698,3-Dec-1981,950,,30

7902,FORD,ANALYST,7566,3-Dec-1981,3000,,20

7934,MILLER,CLERK,7782,23-Jan-1982,1300,,10

===================
DISCARD FILE GENERATED BECAUSE THE CONDITION MET INTO THE CONTROL FILE
=====================

7369,SMITH,CLERK,7902,17-Dec-1980,800,,20
7566,JONES,MANAGER,7839,2-Apr-1981,2975,,20
7698,BLAKE,MANAGER,7839,1-May-1981,2850,,30
7782,CLARK,MANAGER,7839,9-Jun-1981,2450,,10
7788,SCOTT,ANALYST,7566,19-Apr-1987,3000,,20
7839,KING,PRESIDENT,,17-Nov-1981,5000,,10
7876,ADAMS,CLERK,7788,23-May-1987,1100,,20
7900,JAMES,CLERK,7698,3-Dec-1981,950,,30
7902,FORD,ANALYST,7566,3-Dec-1981,3000,,20
7934,MILLER,CLERK,7782,23-Jan-1982,1300,,10

----

MA ASALAAM

PASSION FOR ORACLE

SQL LOADER WITH HEADING SKIP BAD FILE DISCARD FILE

AS SALAMO ALAIKUM WA RAHMATULLAH

TABLE STRUCTURE WHERE DATA IS INSERTED USING THE SQL LOADER FILE
=================
create table XXX_EMP_STG
(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(20) NOT NULL,
    JOB VARCHAR(20) NOT NULL,
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(8,3),
    COMM NUMBER(8,3),
    DEPTNO NUMBER(2)

)


==================
CONTROL FILE NAMED - LOADER.ctl
=================

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'D:\ORACLEDEMO\SQLLOADER\emp.csv'
BADFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_BAD.bad'
DISCARDFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_DISCARD.dis'
INSERT INTO TABLE XXX_EMP_STG
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

==================

 EXAMPLE EMP.csv FILE

===============

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

7369,SMITH,CLERK,7902,17-Dec-1980,800,,20

7499,ALLEN,SALESMAN,7698,20-Feb-1981,1600,300,30

7521,WARD,SALESMAN,7698,22-Feb-1981,1250,500,30

7566,JONES,MANAGER,7839,2-Apr-1981,2975,,20

7654,MARTIN,SALESMAN,7698,28-Sep-1981,1250,1400,30

7698,BLAKE,MANAGER,7839,1-May-1981,2850,,30

7782,CLARK,MANAGER,7839,9-Jun-1981,2450,,10

7788,SCOTT,ANALYST,7566,19-Apr-1987,3000,,20

7839,KING,PRESIDENT,,17-Nov-1981,5000,,10

7844,TURNER,SALESMAN,7698,8-Sep-1981,1500,0,30

7876,ADAMS,CLERK,7788,23-May-1987,1100,,20

7900,JAMES,CLERK,7698,3-Dec-1981,950,,30

7902,FORD,ANALYST,7566,3-Dec-1981,3000,,20

7934,MILLER,CLERK,7782,23-Jan-1982,1300,,10

MA ASALAAM

--

PASSION FOR ORACLE

SQL SERVER TRIGGER EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH


CREATE TABLE MSTH_COUNTRIES_XX (
COUNTRY_SYS_ID BIGINT,
COUNTRY_CODE NVARCHAR(24),
COUNTRY_NAME NVARCHAR(240),
COUNTRY_NAME_BL NVARCHAR(240),
COUNTRY_CR_UID NVARCHAR(24),
COUNTRY_CR_DT DATETIME,
COUNTRY_UPD_UID NVARCHAR(24),
COUNTRY_UPD_DT DATETIME,
COUNTRY_FLEX_01 NVARCHAR(240),
COUNTRY_FLEX_02 NVARCHAR(240),
COUNTRY_FLEX_03 NVARCHAR(240),
COUNTRY_FLEX_04 NVARCHAR(240),
COUNTRY_FLEX_05 NVARCHAR(240),
COUNTRY_DEL_UID NVARCHAR(24),
COUNTRY_DEL_DT DATETIME)

alter table MST_COUNTRIES_XX add COUNTRY_DEL_DT DATETIME;

CREATE TABLE MST_COUNTRIES_XX (
COUNTRY_SYS_ID BIGINT IDENTITY(1,1) NOT NULL,
COUNTRY_CODE NVARCHAR(24) NOT NULL,
COUNTRY_NAME NVARCHAR(240) NOT NULL,
COUNTRY_NAME_BL NVARCHAR(240),
COUNTRY_CR_UID NVARCHAR(24) NOT NULL,
COUNTRY_CR_DT DATETIME NOT NULL,
COUNTRY_UPD_UID NVARCHAR(24),
COUNTRY_UPD_DT DATETIME,
COUNTRY_FLEX_01 NVARCHAR(240),
COUNTRY_FLEX_02 NVARCHAR(240),
COUNTRY_FLEX_03 NVARCHAR(240),
COUNTRY_FLEX_04 NVARCHAR(240),
COUNTRY_FLEX_05 NVARCHAR(240),
CONSTRAINT uQ_01_COUNTRY_XX UNIQUE (COUNTRY_CODE),
CONSTRAINT PK_COUNTRY_XX PRIMARY KEY( COUNTRY_SYS_ID ));



Create trigger TRGUPDATE ON MST_COUNTRIES_XX
FOR UPDATE
AS
BEGIN
Declare @COUNTRY_SYS_ID bigint;
Declare @COUNTRY_CODE NVARCHAR(24);
Declare @COUNTRY_NAME NVARCHAR(240);
Declare @COUNTRY_NAME_BL NVARCHAR(240);
Declare @COUNTRY_CR_UID NVARCHAR(24);
Declare @COUNTRY_CR_DT DATETIME;
Declare @COUNTRY_UPD_UID NVARCHAR(24);
Declare @COUNTRY_UPD_DT DATETIME;
Declare @COUNTRY_FLEX_01 NVARCHAR(240);
Declare @COUNTRY_FLEX_02 NVARCHAR(240);
Declare @COUNTRY_FLEX_03 NVARCHAR(240);
Declare @COUNTRY_FLEX_04 NVARCHAR(240);
Declare @COUNTRY_FLEX_05 NVARCHAR(240);
Declare @COUNTRY_DEL_UID VARCHAR(24);
Declare @COUNTRY_DEL_DT DATETIME;

Select @COUNTRY_SYS_ID=xx.COUNTRY_SYS_ID  from inserted xx;
Select @COUNTRY_CODE=xx.COUNTRY_CODE from inserted xx;
Select @COUNTRY_NAME=xx.COUNTRY_NAME from inserted xx;
Select @COUNTRY_NAME_BL=xx.COUNTRY_NAME_BL from inserted xx;
Select @COUNTRY_CR_UID=xx.COUNTRY_CR_UID from inserted xx;
Select @COUNTRY_CR_DT=xx.COUNTRY_CR_DT from inserted xx;
Select @COUNTRY_UPD_UID=xx.COUNTRY_UPD_UID from inserted xx;
Select @COUNTRY_UPD_DT=xx.COUNTRY_UPD_DT from inserted xx;
Select @COUNTRY_FLEX_01=xx.COUNTRY_FLEX_01 from inserted xx;
Select @COUNTRY_FLEX_02=xx.COUNTRY_FLEX_02 from inserted xx;
Select @COUNTRY_FLEX_03=xx.COUNTRY_FLEX_03 from inserted xx;
Select @COUNTRY_FLEX_04=xx.COUNTRY_FLEX_04 from inserted xx;
Select @COUNTRY_FLEX_05=xx.COUNTRY_FLEX_05 from inserted xx;
Select @COUNTRY_DEL_UID=xx.COUNTRY_DEL_UID from inserted xx;
Select @COUNTRY_DEL_DT=xx.COUNTRY_DEL_DT from inserted xx;

Insert into MSTH_COUNTRIES_XX (COUNTRY_SYS_ID,COUNTRY_CODE,COUNTRY_NAME,COUNTRY_NAME_BL,COUNTRY_CR_UID,COUNTRY_CR_DT,COUNTRY_UPD_UID,COUNTRY_UPD_DT,
COUNTRY_FLEX_01,COUNTRY_FLEX_02,
COUNTRY_FLEX_03,
COUNTRY_FLEX_04,
COUNTRY_FLEX_05,
COUNTRY_DEL_UID,
COUNTRY_DEL_DT)

values (@COUNTRY_SYS_ID,@COUNTRY_CODE,@COUNTRY_NAME,@COUNTRY_NAME_BL,@COUNTRY_CR_UID,@COUNTRY_CR_DT,@COUNTRY_UPD_UID,@COUNTRY_UPD_DT,@COUNTRY_FLEX_01
,@COUNTRY_FLEX_02,@COUNTRY_FLEX_03,@COUNTRY_FLEX_04,@COUNTRY_FLEX_05,@COUNTRY_DEL_UID,@COUNTRY_DEL_DT);

PRINT 'AFTER UPDATE TRIGGER FIRED';
END;  


--======================== TRIGGER FOR DELETION
Create trigger TRGDELETE ON MST_COUNTRIES_XX
AFTER DELETE
AS
BEGIN
Declare @COUNTRY_SYS_ID bigint;
Declare @COUNTRY_CODE NVARCHAR(24);
Declare @COUNTRY_NAME NVARCHAR(240);
Declare @COUNTRY_NAME_BL NVARCHAR(240);
Declare @COUNTRY_CR_UID NVARCHAR(24);
Declare @COUNTRY_CR_DT DATETIME;
Declare @COUNTRY_UPD_UID NVARCHAR(24);
Declare @COUNTRY_UPD_DT DATETIME;
Declare @COUNTRY_FLEX_01 NVARCHAR(240);
Declare @COUNTRY_FLEX_02 NVARCHAR(240);
Declare @COUNTRY_FLEX_03 NVARCHAR(240);
Declare @COUNTRY_FLEX_04 NVARCHAR(240);
Declare @COUNTRY_FLEX_05 NVARCHAR(240);
Declare @COUNTRY_DEL_UID VARCHAR(24);
Declare @COUNTRY_DEL_DT DATETIME;

Select @COUNTRY_SYS_ID=xx.COUNTRY_SYS_ID  from deleted xx;
Select @COUNTRY_CODE=xx.COUNTRY_CODE from deleted xx;
Select @COUNTRY_NAME=xx.COUNTRY_NAME from deleted xx;
Select @COUNTRY_NAME_BL=xx.COUNTRY_NAME_BL from deleted xx;
Select @COUNTRY_CR_UID=xx.COUNTRY_CR_UID from deleted xx;
Select @COUNTRY_CR_DT=xx.COUNTRY_CR_DT from deleted xx;
Select @COUNTRY_UPD_UID=xx.COUNTRY_UPD_UID from deleted xx;
Select @COUNTRY_UPD_DT=xx.COUNTRY_UPD_DT from deleted xx;
Select @COUNTRY_FLEX_01=xx.COUNTRY_FLEX_01 from deleted xx;
Select @COUNTRY_FLEX_02=xx.COUNTRY_FLEX_02 from deleted xx;
Select @COUNTRY_FLEX_03=xx.COUNTRY_FLEX_03 from deleted xx;
Select @COUNTRY_FLEX_04=xx.COUNTRY_FLEX_04 from deleted xx;
Select @COUNTRY_FLEX_05=xx.COUNTRY_FLEX_05 from deleted xx;
Select @COUNTRY_DEL_UID=xx.COUNTRY_DEL_UID from deleted xx;
Select @COUNTRY_DEL_DT=xx.COUNTRY_DEL_DT from deleted xx;

Insert into MSTH_COUNTRIES_XX (COUNTRY_SYS_ID,COUNTRY_CODE,COUNTRY_NAME,COUNTRY_NAME_BL,COUNTRY_CR_UID,COUNTRY_CR_DT,COUNTRY_UPD_UID,COUNTRY_UPD_DT,
COUNTRY_FLEX_01,COUNTRY_FLEX_02,
COUNTRY_FLEX_03,
COUNTRY_FLEX_04,
COUNTRY_FLEX_05,
COUNTRY_DEL_UID,
COUNTRY_DEL_DT)

values (@COUNTRY_SYS_ID,@COUNTRY_CODE,@COUNTRY_NAME,@COUNTRY_NAME_BL,@COUNTRY_CR_UID,@COUNTRY_CR_DT,@COUNTRY_UPD_UID,@COUNTRY_UPD_DT,@COUNTRY_FLEX_01
,@COUNTRY_FLEX_02,@COUNTRY_FLEX_03,@COUNTRY_FLEX_04,@COUNTRY_FLEX_05,@COUNTRY_DEL_UID,@COUNTRY_DEL_DT);

PRINT 'AFTER DELETE TRIGGER FIRED';
END;

DELETE FROM MST_COUNTRIES_XX
WHERE COUNTRY_SYS_ID=2




--
MA ASALAAM
PASSION FOR ORACLE

Example of SQL DATA LOADER

As Salamo alaikum wa rahmatullah

I have created one control file by the name stk.ctl which has following parameter
---------------------------

LOAD DATA
INFILE "D:/upload/stk.csv"
BADFILE "D:/upload/stk.bad"
DISCARDFILE "D:/upload/stk.dsc"
Insert into table tbl_mst_stock 
Fields terminated by "," 

(stock_id,stk_code,stk_desc,created_dt date 'mm/dd/yyyy',updated_dt date 'mm/dd/yyyy')

After that open the command prompt and put the following command
c:/>sqlldr userid=scott@orcl/tiger  control=stk.ctl log=stk.log <etner>

--
MA Asalaam
Passion for Oracle

Create sequence in oracle

As salamo alaikum wa rahmatullah

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

--

Ma Asalaam

Passion for Oracle

Package Example in Oracle

As salamo alaikum wa rahmatullah

create or replace package pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2);
end pck_test;

create or replace package body pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) 
is
begin
insert into tbl_mst_stock(stk_code,stk_desc,created_dt,updated_dt)
values(stc,sdesc,sysdate,sysdate);
commit;
errcode:='0';
errbuf:='row inserted successfully';
exception
when dup_val_on_index then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_inst;

procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) is
begin
update tbl_mst_stock
set stk_code=stc, stk_desc=sdesc, updated_dt=sysdate where stock_id=id;
errcode:='0';
errbuf:='record updated successfully';
exception
when others then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_upd;

procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2) is
begin
delete from tbl_mst_stock 
where stock_id=id;
commit;
errcode:='0';
errbuf:='record deleted successfully';
exception
when others then
errcode:='1';
errbuf:='no row exist';
end prc_del;


end pck_test;

--

Ma Asalaam

Passion for Oracle