Oracle Introduction for Java Beginners

Here we will discuss the some key areas of Oracle which will be usefull for Java beginners(Not Oracle Expert).

Oracle has Schemas which is nothing but User. Whenever a user has been created a new schema is also created. If it has grant of session then User can login to Oracle.
Objects are also there in Oracle as in Java.

1. Procedure
2. Function
3. Collection
4. Type
5. Trigger
6. Cursor
7. RefCursor
8. Record
9. Views
10. Package
11. Synonym
12. Sequence

There are several Table which contains the details of these objects. Oracle Provides Views to see available Objects.

USER_TABLES

View, which contains the Description of user’s own relational tables

USER_PROCEDURES

Description of the user functions/procedures/packages/types/triggers

USER_OBJECTS

View for showing Objects owned by the user

USER_SOURCE

View of data dictionary for source, Source of stored objects accessible to the user

USER_TAB_COLUMNS

Columns of user’s tables, views and clusters

TAB

Is a view, same as user_tables

USER_INDEXES

View for Description of the user’s own indexes

USER_CONSTRAINTS

View for Constraint definitions on user’s own tables

ALL_OBJECTS

Data dictionary which contains all types of objects details for all users.

USER_SEQUENCES

Description of the user’s own sequences

Oracle has set of statements which can be divided in different types.

DDL

Create, Alter, Drop, Truncate (Data Definition Lang)

DML

Insert, Update, Delete, Merge (Data manipulation Lang)

DQL

Select (Data Query Language)

DCL

Grant , Revoke (Data control Lang)

TCL

Commit, Rollback, Savepoint (Transaction Control Lang)

Oracle supports different type of constraints which can be applied to different Objects.

1. P – Primary
2. U – Unique
3. R – Foreign Key
4. O – Created on Views – Used for Read Only Views
5. C – Check Constraints
6. V – View Checks Constraint

Que: Difference between Delete and Truncate?
Ans: Differences are as below

1. Delete is DML statement while Truncate is a DDL statement.
2. After deleting data will go to rollback block, while data will not go to rollback block in case of Truncate.
3. Size of the table will be reduced in case of Truncate, In case of Delete size will not be reduced.
4. Auto commit will happen in case of Truncate which is not true in case of Delete.

Que: Difference between Procedure & Function?
Ans: Differences are

1. Function needs to have return keyword but procedure do not have any Return keyword.
2. Function can be used in select query but procedure can not be.
3. Procedure can be used with exec but function can not be.
4. Function can be used with call but procedure can not be.
5. Procedure may or may not return any value but funnction has to return some value.

Que: Type of Views?
Ans: Types are

• Simple Views
• Complex Views
• Materialized VIews

Que: Type of Collection Oracle?
Ans: Types are

1. VARRAY – Size is fixed and stored in DB
2. NestedTable – Variable Sized, Needs to be initialized, Stored in DB
3. Binary Table – Variable Sized, Local Scope, Can not be stored in DB.

1- Query to fetch “Top sallary or Second Top sallary”
Way1:

	  SELECT *
	    FROM (SELECT tab.*, ROWNUM rn
	     FROM (SELECT   e.*
		FROM employees e
	    ORDER BY sallary desc) tab)
	   WHERE rn = 2

Way2:

         select * from
      	   (
	   select a.*, rank()  over(order by sallary desc) rn from employees a
	   )
	 where rn = 2