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

Important Views Contains list of 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 Statement Type

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 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”
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
By | 2017-07-21T21:45:53+00:00 April 28th, 2012|Oracle|5 Comments

About the Author:

5 Comments

  1. kshashikanth June 19, 2012 at 3:54 am - Reply

    Nice Article,

  2. Bali January 1, 2013 at 5:57 pm - Reply

    concepts are explained in sweet and simple, this is the best one i seen ever thank you so much:)

  3. ashok October 27, 2014 at 3:36 pm - Reply

    its very nice tutorial for freshers nd experience people

  4. Vijay June 25, 2015 at 12:51 pm - Reply

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

  5. Mathan September 26, 2015 at 12:30 am - Reply

    Hello Sir,
    I started reading your tutorials..it’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 Comment