# Oracle - Tips and Tricks



## Faun (Dec 4, 2007)

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
> ----------
> ...


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
> ...


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
> ...



 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


----------



## Sykora (Dec 4, 2007)

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).


----------



## Faun (Dec 4, 2007)

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


----------



## Garbage (Dec 4, 2007)

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 !


----------



## Sykora (Dec 4, 2007)

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?


----------



## Faun (Dec 4, 2007)

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

*


----------



## Sykora (Dec 4, 2007)

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


----------



## Faun (Dec 4, 2007)

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.


----------



## FilledVoid (Dec 5, 2007)

> 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


----------

