1. 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
2. What is a sequence ?
- 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
- 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
3. What is MERGE ?
Combination of INSERT and UPDATE
4. How to know PL/SQL compile parameters ?
- SHOW PARAMETERS PLSQL
- ALL_PLSQL_OBJECT_SETTINGS
Combination of INSERT and UPDATE
4. How to know PL/SQL compile parameters ?
- SHOW PARAMETERS PLSQL
- ALL_PLSQL_OBJECT_SETTINGS
5. Tell some tips to avoid performance problems in PL/SQL.
- 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
- 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
6. How do you handle exceptions for bulk operations ?
- 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
- 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
7. What are the advantages of Packages ?
- Encapsulation of code logic
- Privileges to objects can be controlled
- Loaded once into memory , used subsequently.
- Dependency simplified
- Public/private procs, functions, variables
- Encapsulation of code logic
- Privileges to objects can be controlled
- Loaded once into memory , used subsequently.
- Dependency simplified
- Public/private procs, functions, variables
8. What is a package spec and package body ? Why the separation ?
- 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.
- 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.
9. What are the advantages and disadvantages of DBMS_SQL ?
- 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.
- 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.
10. How is a PL/SQL program executed ?
- 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.
- 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.
