More Oracle Interview Questions : SQL and PL/SQL
86I can say Oracle database is a box of chocolates. You never know what you're gonna get. The more I learn about it, the more there is to learn. It's getting so much better with each new release too. See what Oracle 10g has to give us.. recycle bin, flashback queries, regular expressions ... to name a few. It's intriguing and interesting. Many people found my previous hub on PL/SQL very useful. This hub additionally has questions in SQL along with PL/SQL. Some of these are job interview questions while some others are just interesting information ( which of course could be asked in interviews too ), if you want to be an Oracle developer. The answers that I provided are only for reference and are open to correction.
What does Opening a cursor do ?
- It executes the query and identifies the Result set
What does Fetching a cursor do ?
- It reads the Result Set row by row.
What does Closing a cursor do ?
- It clears the private SQL area and de-allocates the memory.
What are Cursor Variables ?
- Also called REF CURSORS.
- They are not tied to a single SQL. They point to any SQL area dynamically.
- Advantage is : You can declare variables at Client side and open them Server side. You can thus centralize data retrieval.
Why use Cursor Variables?
- You can pass cursor RESULT SETS between PL/SQL stored programs and clients.
What are SQLCODE and SQLERRM ?
- Oracle Error code and detailed error message
- They are actually functions with no arguments, that can be used only in procedural statements ( not SQL)
What are Pseudocolumns ?
- They are not actual columns. They are like Functions without arguments.
- They typically give a different value for each row.
- Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME
Why use Truncate over Delete while deleting all rows ?
- Truncate is efficient. Triggers are not fired.
- It deallocates space (Unless REUSE STORAGE is given).
What is a ROWID composed of ?
- It's a hexadecimal string representing the address of a row. Prior to Oracle 8, it's a restricted rowid comprising block.row.file. Extended rowid ( the default on higher releases) comprises data object number as well ( comprising the segment number ).
What is the use of a ROWID ?
- Retrieve data faster with ROWID.
- Shows you the physical arrangement of rows in the table.
- Also unique identifier for each row.
Can rows from two different tables have the same ROWID?
- Possible, if they are in a Cluster
What is ROWNUM and ROW_NUMBER ?
- ROWNUM is a pseudocolumn which is the number assigned to each row retrieved.
- ROW_NUMBER is an analytic function which does something similar, but has all the capabilities of PARTITION BY and ORDER BY clauses..
What is an inline view?
- It's not a schema object
- It's a subquery in the FROM clause with an alias that can be used as a view within the SQL statement.
What are Nested and Correlated subqueries ?
- The subquery used in WHERE clause is a nested subquery.
- If this subquery refers to any column in the parent statement, it becomes a correlated subquery.
How do you retrieve a dropped table in 10g?
- FLASHBACK table <tabname> to BEFORE DROP
What are PSPs?
- PL/SQL Server Pages. Web pages developed in PL/SQL
What is an index-organized table?
- The physical arrangement of rows of this table changes with the indexed column.
- It's. in-short, a table stored like an index itself.
What is an implicit cursor?
- Oracle opens an implicit cursor to process each SQL statement not associated with an explicit cursor.
Name a few implicit cursor attributes.
- %FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS
That's from me now.
COMMENTS AND SUGGESTIONS ARE WELCOME !
vote upvote downshareprintflag
- Useful (87)
- Funny (12)
- Awesome (14)
- Beautiful (11)
- Interesting (3)
CommentsLoading...
Hi This is very helpfull for intervivewes
Many thanks for thiis tutorial.
Great set of Q&A's. I found this very very helpful! Thanks so much for posting this stuff!
Thanks a lot for the questions. They are very helpful.
Very much helpful to the oracle developers
thanks for your tips, it is very short and clear tips. Brief and good for oracle newbie and pro one.
Thanks a Ton for this gr8 collection.
its really helpful.
thnx...really gud..n also reffered to my frnds...
You have a knack for making a very hard subject easy to understand. It is so nice to write like this.
Best of luck!
thanks a lot for the q&a's. It was really informative
THNAX .....! I THINK VERY GOOD AND VERY AFFECTIVE INFO
hi
thi is govardhan please send mme oracle sql & pl/sql interview questions.
i am looking for job on pl/sql development:
please send questions to: govardhan.kurra@gmail.com
Keep up the good work your q ans a in very useful thanks a lot
Thanx a lot !! :)
Thanks , these questions are very useful for interviews, it helps a lot.
Good list... one comment about using truncate versus "delete all"... truncate is much faster because it does not put data in the UNDO area of the database. It also means that recovery for a mistakenly done command is a little more involved than a delete all.
it's very helpful
It is really helpful and i got through the interview..
thanks man !!!
good post..informative
Thanks a lot, really useful.
Thanks.
Good one... Really good...Tnanks
Its really helpfull
Nice Selections of Questions! Really Useful
Really good one. thanks
its all r gud for interview
nice faq's
Really without any exaggerating, you are excellent
You did Wonderful job! keep it up
you can updated with some real scenarios it would be help
ful.
Thanks
Thanks, it helped me in interview
Thanks
Thanks
Hey, this is very helful...Thanks for posting it!
Hey, very useful, thanks for posting
It is really helpful and i got through the interview.
Very helpfull for oracle INTERVIEW
Fine . Really gr8 . Presentation is nice. Please update many useful Q/A like tis, which helps a lot for learners like me.
is this question for PL/SQL developer..... I think it is also useful for JAVA programmer..... in interview they expect deep knowledge of oracle..
it is really good for learners.
i want to know more ques in oracle.......becoz i want to start up my career with it.
thnks a lot
Very useful oracle tips.
Please add more QA!
Hi Rakesh,
Just now only I come to know about "ROW_NUMBER"
Thanks for your useful post.
You can check this blog for more Interview related QA at
Very informative
it is very useful
hi,
this is pvram reddy and looking for job on sql/plsql, so please send me some interview quations and answers to pvramireddy2000@yahoo.com
thanking you
Please also send me the interview questions to sudhir.220650@gmail.com.
Thanks Very Much Friends,
Sudheer C
the question answers r very useful....expect more of those...
THANKS,very nice questions with very nice answers,
PLz. suggest some more questions with answers and
CASE STUDIES TYPE questions n answers TOO.
questions and answers are very useful....
good concepts, very useful in I/N
Ha ... ha ... ha ...
I was interviewed on the interview on these issues yesterday. But I found it only today. 100% coincidence with those issues on which I was asked.
Wow its really nice
Short n perfect answers...
I found it very helpful.
Thanks!!!!!!!!!!!!!!!
good collections
for more: Oracle Interview Questions and
Answers
Regards,
Akaas Developer
Good work.. thanks for it..
Short and Perfect Answers.
regards@sadashiv
Thanks ,, It's So Amazing
good one. Very helpful
really very good answer and very helpfull
Thank's for the Above information, Cool Job ... Keep it!!!!
nice it is usefully for me
tank you man It is very useful for us
regards
mohankaram
really Good stuff in this material
Can anyone help me with PL-SQL interview questions. I mean actual working environment questions, as to what tools are used in everyday PL/SQL work and what happens after every step of coding etc. Thanks
Gopal
gopalseri@gmail.com
I am fresher in oracle d2k. Please help me . If u have a job for fresher then please inform me on my no. 09953435317.or mail to me on sandeepseo2010@gmail.com.I would be thankful to you for this.plzzzzzzz help me i need a job.
its very usefull,,,,,,,,, Thanks for help
good..very useful..try to post more questions
Excellent !!
Good One, Useful One
thanks!
KK 4 years ago
I found the questions very useful. Thanks.