What is export and import in Oracle?

Export and Import are two utilities in Oracle database used to extract data from database into a single file, and also extract data from that single file into a database. We normally call that file as dump file. It is widely used during backup and transfer data from one database to another.

1. Use command below to show out the parameters that can be used in Export:
   exp help=y
2. To export everything in database:
   exp [username]/[password]@[database] full=y file=[dump file] log=[log file]
3. To export everything in one single schema:
   exp [username]/[password]@[database] owner=[schema] file=[dump file] log=[log file]
4. To export particular tables in database:
   exp [username]/[password]@[database] tables=([table],[table]) file=[dump file] log=[log file]
5. To show parameters that can be used in Import:
   imp help=y
6. To import everything in a dump file:
   imp [username]/[password]@[database] full=y file=[dump file] log=[log file]
7. To import particular schema in a dump file:
   imp [username]/[password]@[database] fromuser=[schema in dump file] touser=[schema in db] file=[dump file] log=[log file]
8. To import particular tables in dump file:
   imp [username]/[password]@[database] tables=([table],[table]) file=[dump file] log=[log file]
9. To import table structure only, add the following parameter:
   rows=n
10. To import table (or other objects) that already exist in database, add following parameter:
    ignore=y
11. Normally import the whole database cannot be done in one shot, need to import objects of different type one by one:
    imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=y constraints=n indexes=n grants=n
imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=n constraints=y indexes=n grants=n
imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=n constraints=n indexes=y grants=n
imp [username]/[password]@[database] full=y file=[dump file] log=[log file] ignore=y rows=n constraints=n indexes=n grants=y

Cursor and for loop in PL/SQL

In PL/SQL, we can use method below to query data from database:
select [column] into [variable]
from [table];

However method above can only retrieve one single row of data. To retrieve data more than 1 row, we can use cursor instead. First we need to declare the cursor at the declaration section:
cursor [cursor name] is [select statement];

For example:
cursor c is select id, name
from emp;

Then at the operation section, we can use a special for loop to retrieve the data:
for [row variable] in [cursor name] loop
do something;
end loop;

For example:
for r in c loop
insert into temp values (r.id, r.name);
end loop;


Basic PL/SQL: Part 3 (Alternate and Loop)

IF structure syntax:

if [condition] then
[do something];
elsif [condition] then
[do something];
else
[do something];
end if;

LOOP syntax:

loop
[do something];
exit when (condition);
end loop;

WHILE LOOP syntax:

while (condition) loop
[do something];
end loop;

FOR LOOP syntax:

for [counter] in [start] .. [end] loop
[do something];
end loop;


Basic PL/SQL: Part 2 (Function, Produre and Trigger)

Syntax to create Function:
CREATE OR REPLACE FUNCTION [function name]([parameter] [IN|OUT] [data type])

RETURN [data type]
AS
[declare];


BEGIN

[do something];

RETURN [something];

END;

/

Syntax to create Procedure:
CREATE OR REPLACE PROCEDURE [procedure name]([parameter] [IN|OUT] [data type]) AS

[declaration];

BEGIN

[do something];

END;

/

Syntax to create Trigger:
CREATE OR REPLACE TRIGGER [trigger name]

[AFTER | BEFORE] [INSERT OR UPDATE OR DELETE] ON [table name]

REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
IF INSERTING THEN
[do something];

END IF;

IF UPDATING THEN
[do something];

END IF;

IF DELETING THEN
[do something];

END IF;
END;
/


Basic PL/SQL: Part 1 (Anonymous)

SQL is only used to interact with database, and itself cannot be coded in procedural structure (which means no alternate or looping). To solve this weakness, Oracle provides another language called PL/SQL (Procedural Language/Structured Query Language). 4 situations that we can use PL/SQL:

1. Anonymous, it is written in script form and saved as file, not a database object and cannot pass in any parameters or return any value.
2. Procedure
3. Function
4. Trigger

Basic syntax to write an anonymous PL/SQL:

declare
[variable name] [data type] := [default value];
begin
[action to be performed];
exception

while others then
[exception handler];
end;
/


For example:

declare
v_name varchar(50) := '';
begin
select name into v_name

from emp

where id = 1;
exception
while others then
raise;
end;
/


Functions in Oracle

Oracle provides some predefined functions that can be used in SQL:

Date function:

months_between(date1, date2), return months between date1 and date2. For example months_between('1/jan/09', '2/feb/09') will return 1.

sysdate will return today's date.

String function:

lpad(str, l, chr), left padding str with chr in length l. For example lpad('1', 3, '0') will return '001'.

rpad(str, l, chr), right padding str with chr in length l. For example rpad('1', 3, '0') will return '100'.

length(str), return the length of str. For example length('1234') will return 4.

substr(str, s, l), return portion of str, start from position s, follow by length l. For example substr('123456', 2, 2) will return 23.

Conversion function:

to_date(str, f), convert str to date in the format of f. For example to_date('1/7/2009', 'dd/mm/yyyy').

to_char(num), convert num to string.

to_number(str), convert str to number.

Group function:

count(c), return number of row where c is the column name.

sum(c), return the sum of value in column c where c should be a numeric column.

max(c), return the maximum value in column c.

min(c), return the minimum value in column c.

Others:

nvl(val, ret), return value of ret if val is null.

decode(val, cond1, ret1, cond2, ret2, ret3), same as:

if (val == cond1) return ret1

else if (val == cond2) return ret2

else return ret3

Data Types in Oracle

Data types in Oracle:

1. char(l), is used to store fixed length string where l is the maximum length of the string.
   
2. varchar2(l), is used to store variable length string where l is the maxium length of the string. Different of varchar2 and char is that: no matter how long the string store in char, it will reserve space of length defined in char, while varchar2's length can be flexible.
   
3. varchar(l), is same as varchar2 but varchar2 have a higher string length capacity.
   
4. number(p, s), is used to store either integer or double, where p (precision) is the length of the number and s (scale) is the decimal of the number.
   
5. date, is used to store date and time, where the default date time format is dd/mon/yy hh24:mm:ss.
   
6. blob is used to store binary object like image, document or audio file. lob is stand for Large Object.
   
7. long is used to store string, which has longer capacity than varchar2.
8. raw is used to store binary object like blob, and actually is obsolete.

Basic SQL: Alter (Part 7)

Alter statement is used to alter the structure of database objects, and like Create statement, different objects have different syntax. Below are example to alter a table:

1. Add column:
alter table [table name]
add [column name] [data type]

   Example:
alter table temp
add test varchar(10);


2. Remove column:
alter table [table name]
drop column [column name]

   Example:
alter table temp
drop column test;


3. Add constraint:
alter table [table name]
add constraint [constraint name] [constraint type] [constraint details]

   Example:
alter table temp
add constraint temp_uk unique (name);


4. Remove constraint:
alter table [table name]
drop constraint [constraint name]

   Example:
alter table temp
drop constraint temp_uk;

   

To change password of a user, use syntax below:
alter user [user name]
identified by [new password]

Example:
alter user james
identified by new_password;