Oracle store procedures for java programmers

royjayd used Ask the Experts™
hi experts

I have an interview next week  on oracle store procedures and i would like to know what are the oracle 11i store procedure concepts used in realtime programming with a java front end. I am a java programmer so i dont have  to know hard core store procudures . I just need to know what Oracle Store procuedure Concepts a java programmer should know which will help me in the interview.
I have experience with SQL statements like SELECT/INSERT/UPDATE/DELETE.
But I have  little knowledge of store procedures , a little knowledge of using
explicit ref cursors.
Can someone outline the other important concepts i need to know?
Like Merge command?

thanks in advance,
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
MERGE is a sql command introduced in 9i combining the features of insert and update, and as of 10gR2  delete as well

as for stored procedures,  you should know how to declare in, out and in out parameters
how to map java types to oracle pl/sql types.

reference cursors definitely

collection types (associative arrays, varrays and nested tables)

packages vs functions vs procedures vs object types
Most Valuable Expert 2011
Top Expert 2012
I suggest a quick perusal of the packages and type reference...

some key packages to learn would be utl_file,  dbms_lob,  dbms_job and dbms_scheduler  you may also want to explore other specialized functionality for XML, spatial, text, web services, etc as appropriate for the apps you may be supporting.

and of course the pl/sql language itself
>> I just need to know what Oracle Store procuedure Concepts a java programmer should know which will help me in the interview.

If you are looking only for basic concepts in Oracle PL/SQL, which I believe , interviewer may not ask as advanced concepts is mainly for PL/SQL developers.

You may concentrate on these:
Difference between Procedure, Function and Packages
Advantages of Procedures over standard SQL
What is SQL Injection
types of joins
Advantage of Merge Command

agreed with ajexpert.

Focus on following:

1. Difference betn procedure and function (packages contains procedures as well as functions.).
2. how you can call procedure and function thru the java code.
3. How to pass the IN parameters and OUT parameters to procedures.
4. How to retrieve cursor from procedure and function
5. Difference betn statement and prepared statement and advantage over each other
6. Types of Joins with an examples
7. knowledge of Merge statement, not the syntax
8. Differenece betn Primary Key, Unique Key and Foreign Key.
9. Differenece betn Truncate and Delete.

The answer may vary based on your experience level. More experience you have, more detailed answer is expected.

Thanks for points, did you clear the interview?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial