Oracle PLSQL Interview Questions. Additional Concepts Every Developer Should Know.

The best way of learning something is by asking questions. That's why I've organized this hub in a question-answer format.

The questions range from Basic to Advanced. I gave a concise answer too, only for quick reference and revision. For detailed answer to each question, refer to any Oracle PL/SQL online documentation.

Some of these questions were actually asked in my Job Interviews in the US.

I have added some others which I found interesting. Very useful to know.

What is a cursor ? ( Basic)

- Name or handle to a private SQL area where Oracle parses and fetches query results.

How to control how many cursors are open ?(Intermediate)

- Set OPEN_CURSORS parameter in initialization parameters.

What is shared SQL ? (Intermediate)

-Oracle recognizes similar statements. The SQL area is used many times for similar statements.

What is Parsing ? (Intermediate)

- Syntax checking, privileges checking, allocating Private SQL Area.

What is the difference between anonymous blocks and stored procedures ? ( Basic)

- Anonymous block is compiled only when called.

- Stored procedure is compiled and stored in database with the dependency information as well.

- Former is PL/SQL code directly called from an application. Latter is stored in database.

- Former has declare statement.Latter doesnt.

What are the advantages of procedures ? ( Basic)

- Loaded once and used many times

- Performance better coz all SQL stmts are sent in one go from the application to the database

- Security ( no object privileges are given directly )

- Invoker's rights possible

- Data integrity, productivity

What are standalone procedures ? (Basic)

- Those that are not part of package

How is a PL/SQL program stored in database ? (Advanced)

- Parsed code is stored. It's called P-code

How is a PL/SQL program executed ?(Advanced)

- Prior to Oracle 9i, we have only bytecode and a virtual machine in the database runs it. Later versions have faster native code execution.

- PL/SQL engine is the main component that executes procedural stmt and passes the SQL to the SQL statement executor.

What are the advantages and disadvantages of DBMS_SQL ? (Intermediate)

- It has all the advantages of dynamic sql .. like runtime construction of sql, DDL statements can be executed.

- Its advantage over EXECUTE IMMEDIATE is it can Describe objects

- It's kind of bulky and difficult compared to EXECUTE IMMEDIATE.

What is a package spec and package body ? Why the separation ? ( Basic)

- Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs

- Separation helps make development easier

- Dependency is simplified. You can modify body without invalidating dependent objects.

What are the advantages of Packages ? ( Basic)

- Encapsulation of code logic

- Privileges to objects can be controlled

- Loaded once into memory , used subsequently.

- Dependency simplified

- Public/private procs, functions, variables

How do you handle exceptions for bulk operations ? (Intermediate)

- Use the SAVE EXCEPTIONS clause ( FORALL index IN bound_clause SAVE EXCEPTIONS LOOP ... END LOOP )

- Use 'Exceptions When Others' to handle the exceptions

- SQL%BULK_EXCEPTIONS(i).ERROR_CODE,

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX

SQL%BULK_EXCEPTIONS.COUNT

Tell some tips to avoid performance problems in PL/SQL. (Intermediate to Advanced)

- Use FORALL instead of FOR, and use BULK COLLECT to avoid looping many times

- Tune SQL statements to avoid CPU overhead

- Use NOCOPY for OUT and IN OUT if the original value need not be retained. Overhead of keeping a copy of OUT is avoided.

- Reorder conditional tests to put least expensive ones first

- Minimize datatype conversions => Assign data to exact same type variables

- Use PLS_INTEGER for computation intensive code. NUMBER, INTEGER maintain precision and scale but not optimized for performance as additional checks are made to maintain precision and scale.

- Do not use subtypes like POSITIVE, NATURAL, INTEGER as they have additional checks

- Use BINARY_FLOAT, BINARY_DOUBLE

- EXECUTE IMMEDIATE is faster than DBMS_SQL

How to know PL/SQL compile parameters ?(Advanced)

- SHOW PARAMETERS PLSQL

- ALL_PLSQL_OBJECT_SETTINGS

What is MERGE ?( Basic)

- Combination of INSERT and UPDATE

Tell some new features in PL/SQL in 10g (Intermediate to Advanced)

- Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR

- Compile time warnings

- Conditional compilation

- Improvement to native compilation

- BINARY_INTEGER made similar to PLS_INTEGER

- INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices

- Quoting mechanism . Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes.

Ex: q'!I'm a string, you're a string.!'

- Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN

- Implicit conversion between CLOB and NCLOB

- Improved Overloading

- New datatypes BINARY_FLOAT, BINARY_DOUBLE

- Global optimization enabled

- PLS_INTEGER range increased to 32bit

- DYNAMIC WRAP using DBMS_DDL

What is a sequence ? (Basic)

- A database object that offers high-speed access to an integer value

- Guaranteed to be unique (within that sequence).

-Used commonly to generate Primary key values

Folks, check out http://hubpages.com/hub/oracle_sql_plsql for more questions.

COMMENTS AND SUGGESTIONS ARE WELCOME !!!!