PL SQL

Cursors:
A cursor holds records returned by the SQL statement, and the set of records it holds called 'Active Set', these can be implicit(non-named) and explicit(named).
          Implicit Cursor: Whenever oracle executes DML statements it internally create an active set without any name, we can use cursor attributes to know about set of rows it retrieved.
          Explicit Cursor: These are user defined or named cursor which are controlled by user using PL/SQL, this cursor type have definition, opening, fetching and closing.

Below is the example for explicit cursor:
                DEFINE
                      CURSOR <cursor name> IS < SELECT statement>;
                      l_cursor_var  datatype;
                BEGIN
                      OPEN <cursor name>;
                      FETCH <cursor name> INTO l_cursor_var;
                      ...
                      ...
                      ...
                      CLOSE <cursor name>;
                END;
Below are the cursor attributes which are used by both implicit and explicit cursors.
     %FOUND
     %NOTFOUND
     %ROWCOUNT
     %ISOPEN

Exception Handling:
        RAISE_APPLICATION_ERROR is used to raise user define exceptions in PL/SQL where the range is -20000 to -20999 ( 1000 errors), and this can be captured in WHEN OTHERS clause of EXCEPTION block.
        PRAGMA EXCEPTION_INIT is used to associate user defined message to a oracle error number.
        RAISE key word is used to raise an exception explicitly in pl/sql.
        EXCEPTION key word is used to declare user defined exception.
        Below are some pre-defined oracle exceptions
                 NO_DATA_FOUND
                 VALUE_TO_ERROR
                 TOO_MANY_VALUES
     
REF Cursor
         A REF CURSOR is a database memory address(pointer) of a result set of SQL statement which can be sent to any client application which will be opened and can be retrieved results in forward direction. SYS_REFCURSOR is a predefined by oracle from 9i where as REF CURSOR is user define by programmer, this can be two types strong and weak.
         l_variable     SYS_REFCURSOR;
         TYPE l_ref_cur IS REF CURSOR; -- Weak REF CURSOR
         TYPE l_strong_ref_cur IS REF CURSOR RETURN emp%ROWTYPE; -- Strong Ref cursor.

Data Collections
           Index by tables
           varrays
           Nested Tables
Bulk Collect
          Below is the example to use SAVE EXCEPTIONS clause to handle errors and proceed with next record from collections.
          FOR rec_emp IN 1..l_emp_table.COUNT SAVE EXCEPTIONS
                 INSERT INTO emp VALUES l_emp_table(i);
          ...
          EXCEPTION
               WHEN OTHERS THEN
                      l_no_of_errors := SQL%BULK_EXCEPTIONS.COUNT;
                      FOR i IN 1..l_no_of_errors
                      LOOP
                         l_error_code := SQL%BULK_EXCEPTIONS(i).error_code;
                         l_sql_errm := sqlerrm(-l_error_code);
                         l_err_idx := SQL%SQL_EXCEPTIONS(i).error_index;
                      END LOOP;
If there is any error when using SAVE EXCEPTIONS, oracle will raise ORA-24381 error in bulk collect processing.
External Tables
         These are used to import data from text files into oracle tables, this is an alternative approach for SQL * LOADER. Below are the important commands for external tables.

         CREATE DIRECTORY external_tab_directory '/user/bin/datafiles/';
         GRANT ON DIRECTORY external_tab_directory TO XXABC;
         CREATE TABLE <database_table> IS SELECT * FROM EMP;
         CREATE TABLE external_table(emp_no  CHAR(5), ename CHAR(35), dob DATE)
                          ORGANIZATION EXTERNAL
                          (TYPE ORACLE_LOADER
                            DEFAULT DIRECTORY external_tab_directory
                            ACCESS PARAMETERS
                            (RECORDS DELIMITED BY NEW LINE
                              FIELDS(empno   CHAR(2)
                                           ,ename   CHAR(20)
                                           ,dob       CHAR(10) date_format DATE mask "mm/dd/yyyy"
                                           )
                              )
                            LOCATION 'abc.dat'
          );
          INSET INTO emp (SELECT  * FROM external_table);

1. To convert number in words.

Eg: 100 --> Hundred.
      1000 --> One Thousand

FUNCTION ap_convert_number(in_numeral IN NUMBER) RETURN VARCHAR2;

Example:

 DECLARE
    l_in_words  VARCHAR2(1000);
 BEGIN
    -- intialize the apps here --
    l_in_words := AP_AMOUNT_UTILITIES_PKG.ap_convert_number(100);
    dbms_output.put_line('In words ==> '||l_in_words);
 END;

2. Dbms_Assert.Enquote_Literal will give the output in single quotes.

SELECT Dbms_Assert.Enquote_Literal('ABCDEF') FROM DUAL;

Output:
  'ABCDEF'

3. All privileges are stored in ALL_TAB_PRIVS ( this holds the privileges of packages as well).

HWM(High Water Mark) is the last data block that can be read for a table, when full table scan is done, it is the last data block that will be read. It is like EOF character in a file, which tells us file is ending here.

LOG ERRORS INTO ... is oracle version 10 Release 2 feature that will be used to log errors for DML operations.

ALTER SESSION ENABLE PARALLEL DML/DDL/QUERY - is used to enable parallel processing for oracle session for DML/DDL/QUERY operations. If use /*+parallel(table)*/ hint in a query it uses parallel processing for that query. If you would like to continue for other DML/DDL/QUERY operations, you can use ALTER SESSION FORCE PARALLEL DML/DDL/QUERY PARALLEL <integer> command.

/*+append(table)*/ use direct path to insert data into a table after the last data block, that means it does not use data block buffer cache which is used in normal case, it is very fastest mechanism to insert data into a table and useful in data warehousing.