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.

Oracle Objects

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

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

Important Views Contains list of Objects

View, which contains the Description of user’s own relational tables
Description of the user functions/procedures/packages/types/triggers
View for showing Objects owned by the user
View of data dictionary for source, Source of stored objects accessible to the user
Columns of user’s tables, views and clusters
Is a view, same as user_tables
View for Description of the user’s own indexes
View for Constraint definitions on user’s own tables
Data dictionary which contains all types of objects details for all users.
Description of the user’s own sequences

Oracle Statement Type

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

Create, Alter, Drop, Truncate (Data Definition Lang)
Insert, Update, Delete, Merge (Data manipulation Lang)
Select (Data Query Language)
Grant , Revoke (Data control Lang)
Commit, Rollback, Savepoint (Transaction Control Lang)

Oracle Constraints

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

Important Question Asked in Java Interview

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.

Queries asked in Java Interview

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

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


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

5 thoughts

  1. It is very useful information. Concepts are explained in precise and easily understandable format. Like it a lot. This article is very useful for fresher as well as experienced professional. 🙂

    Thank you Vivekanand :).

  2. Hello Sir,
    I started reading your’s very nice and I can able to understand properly..It is very useful for beginners like me..Thank you very much for doing these things..

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top Search