T

Oracle - Tips and Tricks

December 7, 2007 – 2:13 am

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 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 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