What is Sequence and examples

Sequence:-
Sequence is a database object, it use to generate a numeric value only.

SYNTAX:-

CREATE SEQUENCE sequence_name
[INCREAMENT BY n]
[{START WITH n}]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{NOCYCLE | CYCLE}]
[{NOCACHE | CACHE n}]

INCREAMENT BY n:- Specifies the interval between sequence of number(n is interger).By default in sequence number increment by 1.

START WITH n:- Specific the first sequence of number.By default in sequence number start with 1.

MAXVALUE n:- Specifies the maximum value sequence can be generate.

NOMAXVALUE:- Specifies a maximum value is 10^27.

MINVALUE n:- Specific the minimum value.

NOMINVALUE:- Specifies a minimum value is -10^26.

CYCLE:- Specifies whether the sequence continue to generate value after reaching maximum or minimum.

NOCYCLE:- After reaching maximum or minimum, sequence may not generate value and it give error. NOCYCLE is default option.

CACHE n | NOCACHE:- :- Specifies how many value the oracle preallocates and keeps in memory.(BY default it is 20).

NEXTVAL:- It returns the next value of the sequence.

CURVAL:- It returns the current value of the sequence.

Example:-

CREATE SEQUENCE XXC05_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 100
MINVALUE 1
NOCYCLE
NOCACHE;

Modify Sequence:- We can modify sequence.

Syntax:-

ALTER SEQUENCE sequence_name
INCREAMENT BY n
MAXVALUE n
MINVALUE n
CYCLE | NOCYCLE
CACHE | NOCACHE;

Note:- we can not modify starting value of sequence. Maximum value cannot less current value.

Example:-

ALTER SEQUENCE XXC05_SEQ
INCREAMENT BY 10
MAXVALUE 1000
MINVALUE -1000
CYCLE
CACHE;

Drop Sequence:- We can drop a sequence.

Syntax:-

DROP SEQUENCE sequence_name;

Example:-

DROP SEQUENCE XXC05_SEQ;

—————————————————–

SYNONYM:-
Synonym is the another or alternate name of the database object.
We can create synonym of TABLE, VIEW, SEQUENCE, INDEX, PROCEDURE, FUNCTION, TRIGGER, PACKAGE etc.

Syntax:-

CREATE [PUBLIC] S
SYNONYM synonym_name
FOR object_name;

Example:-

CREATE SYNONYM XXC05_SEQ_SYN
FOR XXC05_SEQ;

Note:- XXC05_SEQ is the name of sequence.

Drop Synonym:-

DROP SYNONYM synonym_name;

Example:-

DROP SYNONYM XXC05_SEQ_SYN;

——————————————————-

Control File:-

The LOAD DATA statement is required at the beginning of the control file.

INFILE * specifies that the data is found in the control file and not in an external file.

The INTO TABLE statement is required to identify the table to be loaded (DEPT) into. By default, SQL*Loader requires the table to be empty before it inserts any records.

FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks.
Data types for all fields default to CHAR.

Specifies that the names of columns to load are enclosed in parentheses. Since no datatype is specified, the default is a character of length 255.

BEGINDATA specifies the beginning of the data.

put control file in that path———>    /u02/oracle/visappl/wip/11.5.0/bin

Attach those ctl file in concurrent program

Program name———->    ANY CONCURRENT PROGRAM NAME

Execute that command:-

sqlldr user=apps/apps@vis control= control_file_name.ctl

Example:-

We create a control file XXC05_CONTROL_EMP.ctl

sqlldr user=apps/apps@vis control=XXC05_CONTROL_EMP.ctl

Note:- When we execute sqlloder command 3 more file created i.e log file, bad file, discard file with the same name of ctl file name.

Log File:- In that file we see the log details of control file. File extension is “file_name.log”.

Bad File:- Which date have logical error (for example:- datatype mismatch, length, etc) goes in that file. File extension is “file_name.bad”.

Discard File:- Which data are not satisfy in when condition goes in that file.

******************************************** 1 ****************************

LOad data
infile *
INSERT/APPEND/DELETE/TRUNCATE
into table XXC05_EMP
when dept_name=’HR’
fields terminated by ‘ ‘
(emp_id position(1:2),last_name position(4:5),salary position(7:9),dept_name position(11:12))
into table XXC04_emp2
when dept_name=’FF’
(emp_id position(1:2),dept_name position(11:12))
begindata
10 AA 200 HR
20 BB 300 HR
30 CC 400 MG
40 DD 500 FF

******************************************** 2 ****************************

Option (skip=1)
load data
infile *
insert into table XXC05_EMPLOYEES
fields terminated by ‘ ‘
(emp_id,last_name,dept_name FILLER POSITION(1),salary)
begindata
10 HR 200
20 GG 300
30 AA 400

******************************************** 3 ****************************

load data
infile *
insert into table XXC05_EMPLOYEES
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 4 ****************************

load data
infile *
insert into table XXC05_EMPLOYEES
when (1:1)=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 5 ****************************

load data
infile *
replace into table XXC05_EMPLOYEES
when (1:1)!=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name,dept_name,rec_no recnum)
begindata
H,A
HR,B
AA,C
BB,D

******************************************** 6 ****************************

options(skip=2)– skip first 2 lines from the top
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ‘,’
(
image_id   INTEGER(5),
file_name  CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg

******************************************** 7 ****************************

LOAD DATA
INFILE ‘/u02/oracle/visappl/wip/11.5.0/data/XXC05_DATA_FILE’
TRUNCATE
INTO TABLE XXC05_EMPLOYEES
FIELDS TERMINATED BY “,”  trailing nullcols
(
c1,
field2 BOUNDFILLER,
field3 BOUNDFILLER,
field4 BOUNDFILLER,
field5 BOUNDFILLER,
c2     “:field2 || :field3”,
c3     “:field4 + :field5”
)

******************************************** 8 ****************************

LOAD DATA
INFILE *
INSERT
INTO TABLE XXC05_EMPLOYEES
(
LAST_NAME  position(1:7)  CHAR “UPPER(:LAST_NAME)”,
FIRST_NAME position(8:15) CHAR “LOWER(:FIRST_NAME)”
)

BEGINDATA
Locke Phil
Gorman Tim

————————————————————————–

Multiple Insert Statement:-
In multiple INSERT statement, you insert data into more then one tables as a part of single DML statement.

Type of Multiple Insert Statement:

1.  Unconditional INSERT
2.  Conditional INSERT
3.  Conditional FIRST INSERT
4.  Pivoting INSERT

Syntax:-

INSERT [ALL] [CONDITION_INSERT_CLAUSE]
[insert_into_clause values_clause ] (subquery);

=> Conditional_insert_clause

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[else] [insert_into_clause values_clause];

Unconditional INSERT ALL:-
This INSERT statement is referred to as as unconditional INSERT because no further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
INTO XXC05_EMP1(EMP_ID, L_NAME, SAL)
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Note:- Insert data into XXC05_EMP1, XXC05_EMP2 tables by a single SELECT statement.

Conditional INSERT ALL:-
This INSERT statement is referred to as as conditional INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
WHEN EMPLOYEE_ID 200
THEN
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
VALUE(EMPLOYEE_ID, FIRST_NAME, MANAGER_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Conditional FIRST INSERT:-
This INSERT statement is referred to as as conditional FIRST INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement. In that statement first true condition find and insert data in those table, rest of conditions or statement are switched.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= 24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = 2388
THEN
INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example First condition is true so data insert into only EMPLOYEE1 table.It do not check any other conditions.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= -24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = -2388
THENINTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example THIRD condition is true so data insert into only EMPLOYEE3 table. It do not check FORTH condition.

Pivoting INSERT:-
Using pivoting INSERT, convert the set of sales records from nonrelational database table to relational format.

Advertisements