Oracle - Tips and Tricks

Its hard to live without database if u are a developer or even a normal user. You need to hav some place where data is to stored and retrieved efficiently with less hassles.

Here I am discussing about simple Oracle Tricks that can save your time and get u prepared for VIVA and exams(interviews, practical etc).

I will start with SQL Plus provided in ORacle 9i (though Oracle 11g is out but the comands remain the same). --------------------------------------------------------------------------
Tips & Tricks - Part 1

1) EASY EDITING TO UNDO MISTAKES
Ever wondered how much its a pain in the a** if u mistyped or forget a letter in a qurey and then again u hav to type it(or select,copy,paste), there is always a simpler option for this

Option 1:
If you are typing short commands then follow this:

Run "cmd"
type in command prompt

sqlplus scott/tiger

or

sqlplus scott/tiger@SID

where "SID" is the value u provided for SID during installation.

now enter any query and amend it by just using arrow keys like you always do in "cmd" prompt

Option 2:
If u are havind a list of query then its better to enter them in a sql script file and execute them with single @ (AKA run).

here is how to do it:

Do as listed in Option 1 to login
type this in "SQLplus prompt"

ed

It will bring up a file named AFIEDT.BUF (the default file to store SQL commands) in default text editor i.e.notepad. You can also use longer form of "ed", the "edit" to do the same.

NOTE:AFIEDT.BUF is expanded as Advanced Friendly Interface file. It is named as six chars cuz of odd naming convetions in older OSes.

Now enter any queries line by line then provide a "/" forward slash at the end.

close the file

You are back to "SQL Prompt"

To run the query listed in "AFIEDT.BUF" type

@

or

run

You are dont with it

2) GET RID OF WRAPING
Ever seen wrapping up of the output ? No, here is a snapshot when i ran this query

select * from emp;

Note: emp is a table that comes by default in oracle
to check what other tables are in ur database:

select * from tab;

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

So next question is how do we increase the linesize to avoid wrapping. Yeah u got it right we hav a "linesize" property to set.

set linesize to 120 or more as per ur preference

set linesize 120

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

Ok the problem is fixed, but wait its a temporary fix and u wanna make it permanent ? yeah then keep on reading:

Open the file named glogin inside Oracle installation using explorer or if u feel geeky then make thru command prompt:

C:\oracle\ora90\sqlplus\admin\glogin.sql

Where "C" is the drive where oracle is installed. It may be "D" or "E" depending upon where ur oracle installation.

Now add this line to this file:

set linesize 120

to make it permanent for ur SQL Plus environment. Now no need to run "set linesize 120" everytime u login to Sql Plus

3) WRITE TO A FILE
"can u write to a file using SQL Plus?" This was one of the question asked in VIVA by some industry expert(30 years experience).

Here is the simplest way and the one I know to write to a file:

Actually what u hav to do is to spool the user activities and record them to a file.

Enter this at SQL Plus prompt:

spool record.txt

It will start spooling the user activity to a file named "record.txt" which is saved in bin
folder(C:\oracle\ora90\BIN\).

Now enter any query to record in the given file, say, for example i entered

select * from emp

and got output

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

now enter

spool off

this will end the spooling

check out if the things are copied to the record file succesfully or not ?

ed record.txt

4) AUTO GENERATE PRIMARY KEY
It was one of the key element in making my BE I sem project(web application on Roster Manaement).

Usually its easier to do it in MYSQL and MS Access but Oracle is a tad bit lenghty.

You need to learn two things
a) Sequence (to generate number used as primary key)
b) Trigger (To input numbers into desired table automatically as u insert values in other attributes(columns))

Short tutorial for Sequences:
A sequence is a database object that generates integers according to the rules specified by its creation time.

e.g.:

create sequence sequence_name
start with 20
increment by 1
maxvalue 100
minvalue 20;

where

"create sequence" is obvious

"start with" provides an option to set initial values

"increment by" provides the increase amount

"maxvalue" is the highest possible value

"minvalue" is obvious

To use this object u use two methods:
a) "sequence_name.nextval"

It gives current value and increments the value for later use.Its like (i++) in C/C++.

b) "sequence_name.currval"

It provides current value without incrmenting the value for later use.

To extract value from Sequence

write

select sequence_name.nextval from dual;

Where dual is a virtual dummy table provided in Oracle. It contains no data.
NOTE: try this command to get the jist of dual

select 2+4 from dual;
select 'autmun' || ' to ashes' from dual;
select round(5.67)from dual;
select sysdate from dual;

Short Tutorial for Triggers:
Triggers are PL/SQL blocks in Oracle and are executed then a DML(Data Modification Language like insert,update,delete) activity occurs on a table to which the trigger is associated.

So they are handy in case u want to do some automatic operation like filling other table with same fields and create a log of the operations on a particular table.

Syntax:

create or replace trigger trigger_name
after or before insert/delete/update on table_name
on each row
begin
...............................
end trigger_name;

where

"create or replace" means if the trigger is not created with same name then please create it or else a copy exists already then overwrite(replace) it.

"after or before" means u hav to choose either of these so pretty much defines whether the change is to be made before exectuing DML queries on table or after

"insert or delete or update on table_name" specifies the event on a table for which the trigger should execute.

"on each row" to execute trigger for each row affected/updated.

"begin" and "end" these are PL/SQL block to carry out queries or mathematical operations.

The Real Example

I have this table named "team" and it has 3 attributes:
team_id, team_name, team_rating

here is the query to make this table

create table team ( team_id number(5) primary_key, team_name varchar2(25), team_rating number(5));

"number" is a datatype to store integer and float.

"varchar2" is a datatype to store string

Now to get the schema(basic structure) of the table try this:

desc team;

Output:

Name Null? Type
----------------------------------------- -------- -------------------
TEAM_ID NOT NULL NUMBER(5)
TEAM_NAME VARCHAR2(25)
TEAM_RATING NUMBER(5)

Note our objective is to fill in the "team_name" and "team_rating" only, the "team_id" will be automatically filled using sequence "teamseq" and trigger "teamtrig" for every entry of "team_name" and "team_id"

Now execute the code for sequence and trigger in the order.

Here are the
teamseq

create sequence teamseq
start with 1000
increment by 1;

and

teamtrig

create trigger teamtrig
before insert on team
for each row
begin
select teamseq.nextval into :new.team_id from dual;
end teamtrig;
/

this is easy just for the thing that comes between begin and end block.

actually we are extracting "teamseq.nextval"(1000 for the first time) from a dummy table "dual" and inserting into ":new.team_id". ":new.team_id" will be copied to "team_id" column in the table "team"

try inserting some values in table "team".

like this

insert into team (team_name,team_rating) values ('India',1);
insert into team (team_name,team_rating) values ('Australia',2);

Now to check if it worked, lets see the table

select * from team;

Output:

TEAM_ID TEAM_NAME TEAM_RATING
-------- ------------------------- -----------
1000 India 1
1001 Australia 2

OMG it worked You see u havent actually hav to input id.

Thats all

more to come

Great stuff, this would've helped when I was doing this at school.

Can you do up a guide for actually installing Oracle? It took me about 7-10 tries to get it to work, and it was hell every step of the way. What I'm referring to is a plain installation with proper choice of options just so you can try out the commands you've mentioned (further description wouldn't hurt).

Sykora said:
Great stuff, this would've helped when I was doing this at school.

Can you do up a guide for actually installing Oracle? It took me about 7-10 tries to get it to work, and it was hell every step of the way. What I'm referring to is a plain installation with proper choice of options just so you can try out the commands you've mentioned (further description wouldn't hurt).

yeah its in my blog(though not very descriptive).

Will post soon when am done with work

Sykora said:
Great stuff, this would've helped when I was doing this at school.

Can you do up a guide for actually installing Oracle? It took me about 7-10 tries to get it to work, and it was hell every step of the way. What I'm referring to is a plain installation with proper choice of options just so you can try out the commands you've mentioned (further description wouldn't hurt).

Better way to go for Oracle 10g or 11g.

They have single exe and one click install !

That's not what I'm taklking about. within the installation, you'll have to choose the type of database, the usernames and passwords, etc etc. What to choose there?

Sykora said:
That's not what I'm taklking about. within the installation, you'll have to choose the type of database, the usernames and passwords, etc etc. What to choose there?

leave everything as default, and dont provide any username/password, just go default way.

I just had to enter the database name and SID. (enter any word u can rememeber)

Am talking about oracle 9i

^^^ And that's something I figured out after the 8th or so try

Sykora said:
^^^ And that's something I figured out after the 8th or so try

you could hav sought someone experienced near ur locality.

Or posted it on some tech forum.

F

That's not what I'm taklking about. within the installation, you'll have to choose the type of database, the usernames and passwords, etc etc. What to choose there?

You shouldnt be facing any problems installing Oracle at all. The default settings should work with no problems. Well at least the Oracle 10 Express Edition does.

Does anyone have the Oracle Linux 64 bit Express Edition. Id love to get a copy of this