SQL Sequences

SQL Sequences

Sequences are frequently used in databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them

How to find sequences in SQL:

Syntax: desc user_sequences;

How to create the sequence:

Syntax:

CREATE SEQUENCE  schema_name

[INCREMENT BY interval]

[START WITH the first number]

[MAXVALUE max value | NOMAXVALUE]

[MINVALUE min value | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE cache size | NOCACHE]

[ORDER | NOORDER];

 

Ex:

Create Table

 

Now, Create a sequence

 

 

Sequence created with conditions needs to be performed.

 

To See sequence’s current value:

Syntax: select seq_name.currvalue from dual;

Ex:

How to Alter sequence:

Syntax: Alter sequence seq_name

(conditions…….);

Ex:

How to Drop Sequence: 

Syntax: Drop sequence sequence_name

Ex:

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SQL LOADER

DESIGNATION:

  • SQL * LOADER loads data from external files into tables of an oracle database.
  • We do not insert the data into the table using SQL * LOADER directly.
  • We need a control file for inserting the data.
  • SQL * LOADER is used to just execute the commands of the control file.
  • The flatfile saving process is normally we will save .txt files.
  • The Control file saving process is normally we will save .ctl files.

BAD FILE:

  • A bad file is used to store the rejected records.
  • The rejected records will store in a bad file automatically.
  • If you wrongly type the data into a flat file, you will surely get some errors. So that error during SQL * LOADER process which stores in bad file.

TYPES OF FLAT FILE:

Variable record flatfile

This is with delimeter

eg., 101, abc, 2000
Fixed record flatfile

This is without delimeter

eg., 101abc2000

The following picture illustrates the SQL * LOADER process:

sql loadet

 

SYNTAX:

$sqlldr <space> userid = username / password

control = ‘path of the controlfile’ <space> log = test.log

Creating a controlfile for variable record flatfile:

BY USING INSERT LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

insert / append / truncate / replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

OVERALL STEPS:

To execute the SQL * LOADER tool, you need at least three files.

  • The input data files store delimited data
  • The parameter file stores the location of the input and output files.
  • The control file contains the specification on how data is loaded.

Step 1: Create the table at the database level

View the table :

Step 2: Create the text document by using the vi editor in os level

View the text file

Step 3: Create the control file by using the vi editor in the os level

View the control file 

Step 4: Execute the SQL * LOADER syntax

Step 5: View the  table

 

BY USING APPEND LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

append

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the APPEND output:

BY USING TRUNCATE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

truncate

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the TRUNCATE output:

BY USING REPLACE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the REPLACE output:

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8