Searched Case Statement

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  v_user_date DATE := TO_DATE('&user_date','DD-MON-YYYY');
BEGIN
  CASE
    WHEN TO_CHAR(v_user_date,'D') = '1' THEN
      dbms_output.put_line('Today is Monday');
    WHEN TO_CHAR(v_user_date,'D') = '2' THEN
      dbms_output.put_line('Today is Tuesday');
    WHEN TO_CHAR(v_user_date,'D') = '3' THEN
      dbms_output.put_line('Today is Wednesday');
    WHEN TO_CHAR(v_user_date,'D') = '4' THEN
      dbms_output.put_line('Today is Thursday');
    WHEN TO_CHAR(v_user_date,'D') = '5' THEN
      dbms_output.put_line('Today is Friday');
    WHEN TO_CHAR(v_user_date,'D') = '6' THEN
      dbms_output.put_line('Today is Saturday');
    WHEN TO_CHAR(v_user_date,'D') = '7' THEN
      dbms_output.put_line('Today is Sunday');
  END CASE;
END;
/
Enter value for user_date: 01-JAN-2012
Today is Sunday
PL/SQL procedure successfully completed.

CASE Statement

--Case Statement
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  v_user_date DATE := TO_DATE('&user_date','DD-MON-YYYY');
BEGIN
  CASE TO_CHAR(v_user_date,'D')
    WHEN '1' THEN
      dbms_output.put_line('Today is Monday');
    WHEN '2' THEN
      dbms_output.put_line('Today is Tuesday');
    WHEN '3' THEN
      dbms_output.put_line('Today is Wednesday');
    WHEN '4' THEN
      dbms_output.put_line('Today is Thursday');
    WHEN '5' THEN
      dbms_output.put_line('Today is Friday');
    WHEN '6' THEN
      dbms_output.put_line('Today is Saturday');
    ELSE
      dbms_output.put_line('Today is Sunday');
  END CASE;
END;
/
Enter value for user_date: 01-JAN-2011
Today is Saturday
PL/SQL procedure successfully completed.

How would you display time of the day

SET SERVEROUTPUT ON
BEGIN
       DBMS_OUTPUT.PUT_LINE('Today is '|| TO_CHAR( sysdate, 'Day, HH24:MI'));
END;
/
Today is Monday   , 10:21
PL/SQL procedure successfully completed.

IF-THEN-ELSIF-ELSE-END IF statement with Logical Operators

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_char CHAR(1) := '&user_input';
BEGIN
    IF ( v_char >= 'A'  AND v_char <= 'Z') OR  ( v_char >= 'a'  AND v_char <= 'z')  THEN
        DBMS_OUTPUT.PUT_LINE('User input is a letter');
    ELSIF ( v_char >= '0' and v_char <= '9' ) THEN
        DBMS_OUTPUT.PUT_LINE('User input is a number');
    ELSE
         DBMS_OUTPUT.PUT_LINE('User input is a special Character');
   END IF;
END;
/
Enter value for user_input: A
User input is a letter
PL/SQL procedure successfully completed.
SQL> /
Enter value for user_input: 3
User input is a number
PL/SQL procedure successfully completed.
SQL> /
Enter value for user_input: $
User input is a special Character
PL/SQL procedure successfully completed.

NESTED IF Statement

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
 v_num1 NUMBER := &num1;
 v_num2 NUMBER := &num2;
v_total NUMBER;
BEGIN
 IF v_num1 > v_num2 THEN
  DBMS_OUTPUT.PUT_LINE ('outer IF ');
  v_total := v_num1 - v_num2;
 ELSE
  DBMS_OUTPUT.PUT_LINE ('outer ELSE');
  v_total := v_num1 + v_num2;
  IF v_total < 0 THEN
   DBMS_OUTPUT.PUT_LINE ('Inner IF');
   v_total := v_total * (-1);
  END IF; 
 END IF;
 DBMS_OUTPUT.PUT_LINE ('v_total =  '||v_total);
END;
/

IF-THEN-ELSIF-ELSE-END IF statement

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_empno NUMBER := &emp_number;
    v_sal        NUMBER;
BEGIN
    SELECT sal INTO  v_sal FROM emp
        WHERE empno = v_empno;
    IF v_sal BETWEEN 0 AND 1000 THEN
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting LOW salary' );
    ELSIF  v_sal BETWEEN 1001 AND 2000 THEN 
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting NORMAL salary' );
    ELSIF  v_sal BETWEEN 2001 AND 3000 THEN 
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting HIGH salary' );
    ELSE
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  salary is more  than 3000 INR' );
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(' Emp No  '|| v_empno || ' does not exist' );
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error  '|| sqlcode ||' - '||sqlerrm);
END;
/
Enter value for emp_number: 7566
Emp No  7566  is getting HIGH salary
PL/SQL procedure successfully completed.

IF-THEN-ELSIF-ELSE-END IF statement

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
 v_user_input NUMBER := &user_input_number;
BEGIN
 IF v_user_input < 0 THEN
  DBMS_OUTPUT.PUT_LINE ( v_user_input || ' is a negative number' );
 ELSIF  v_user_input = 0 THEN
  DBMS_OUTPUT.PUT_LINE ( v_user_input || ' is equal to Zero' );
 ELSE
 DBMS_OUTPUT.PUT_LINE ( v_user_input || ' is a positive number' );
 END IF;
END;
/
Enter value for user_input_number: -24
-24 is a negative number
PL/SQL procedure successfully completed.

IF-THEN-ELSE-END IF Statement

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
 v_user_input_date  DATE  :=  TO_DATE('&user_input_date', 'DD-MON-YYYY');
 v_day VARCHAR2(10);
BEGIN
 v_day :=  TO_CHAR(v_user_input_date,'DAY');
 IF  v_day like 'S%'  THEN
  DBMS_OUTPUT.PUT_LINE( v_user_input_date || ' falls on weekend' );
 ELSE
  DBMS_OUTPUT.PUT_LINE( v_user_input_date || ' does not fall on weekend');
 END IF;
END;
/
Enter value for user_input_date: 18-DEC-2010
18-DEC-10 falls on weekend
PL/SQL procedure successfully completed.

IF-THEN-ELSE-END IF Statement

/*  Test user provided numbe is even or odd */
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
 v_user_input  NUMBER  := &user_input;
BEGIN
 -- Test user provided number is even
 IF  MOD(v_user_input, 2)   = 0 THEN
  DBMS_OUTPUT.PUT_LINE( v_user_input || ' is  a even number');
 ELSE
  DBMS_OUTPUT.PUT_LINE( v_user_input || ' is  a odd number');
 END IF;
END;
/
Enter value for user_input: 23
23 is  a odd number
PL/SQL procedure successfully completed.

IF-THEN-END IF Statement

SET SERVEROUTPUT ON
DECLARE
 v_num1  NUMBER  := 40;
 v_num2  NUMBER  := 30;
 v_temp   NUMBER;
BEGIN
 -- If num1 is greater than num2 rearrange their values
 IF  v_num1 > v_num2 THEN
  v_temp := v_num1;
  v_num1 := v_num2;
  v_num2 := v_temp;
 END IF;
 -- Display rearranged values of v_num1 and v_num2
 DBMS_OUTPUT.PUT_LINE( 'v_num1 = '|| v_num1);
 DBMS_OUTPUT.PUT_LINE( 'v_num2 = '|| v_num2);
END;
/
v_num1 = 30
v_num2 = 40
PL/SQL procedure successfully completed.

Accept User Input (substitute variable) in Anonymous PL/SQL Block

Oracle PL/SQL block accepts user input information with the help of substitution variable. Substitute variable can not be used to output values, because memory is not allocated to substitute variable. Substitute variable is prefixed with ampersand(&) or double ampersand (&&) character. Below example is using Oracle provided HR schema to demonstrate the use of substitute variable

SET SERVEROUTPUT ON
DECLARE
  v_employee_id  NUMBER := &s_employee_id;
  v_first_name  VARCHAR2(20);
  v_last_name  VARCHAR2(25);
BEGIN
  SELECT first_name, last_name
   INTO v_first_name, v_last_name
  FROM hr.employees
  WHERE employee_id = v_employee_id;
 
  DBMS_OUTPUT.PUT_LINE ( ' Employee Name is '|| v_first_name ||'  '|| v_last_name );

EXCEPTION
  WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('Supplied employee id does not exists');
END;
/
Enter value for s_employee_id: 123
old   2:  v_employee_id  NUMBER := &s_employee_id;
new   2:  v_employee_id  NUMBER := 123;
Employee Name is Shanta  Vollman

PL/SQL procedure successfully completed

SQL Support in PL/SQL

PL/SQL fully supports Data Manupulation statement (excluding EXPLAIN PLAN), transactional control statement, funtion, pseudocolumn and operators.

In addition to static SQL, PL/SQL also support dynamic SQL, which enables to execute Data defination, data control and session controla statements.

Data Manupulation with PL/SQL

CREATE TABLE emp_temp (
      empno         NUMBER(4) PRIMARY KEY,
      first_name    VARCHAR2(20),
      last_name     VARCHAR2(20)
      );
    
DECLARE
      v_first_name    emp_temp.first_name%TYPE;
      v_last_name     emp_temp.last_name%TYPE;
BEGIN
      INSERT INTO emp_temp ( empno,first_name,last_name)
                VALUES       (9999,'JOHN',  'ABRAHAM');
      DBMS_OUTPUT.PUT_LINE( 'SQL count after insert statement  - ' || SQL%ROWCOUNT);
                
      UPDATE emp_temp SET first_name = 'SANJAY',last_name   = 'DUTT'
                     WHERE empno      = 9999;

      DBMS_OUTPUT.PUT_LINE( 'SQL count after update statement  - ' || SQL%ROWCOUNT);          
    
      DELETE FROM emp_temp WHERE empno = 9999
      RETURNING first_name, last_name INTO v_first_name,v_last_name;

      DBMS_OUTPUT.PUT_LINE( 'SQL count after delete statement  - ' || SQL%ROWCOUNT);
    
      COMMIT;
    
      DBMS_OUTPUT.PUT_LINE( 'SQL count after commit statement  - ' || SQL%ROWCOUNT);
      DBMS_OUTPUT.put_line( v_first_name ||'  '||v_last_name || ' record deleted from emp_temp table'  );
EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( 'SQL Error - ' || sqlerrm);
END;

Output :

SQL count after insert statement  - 1
SQL count after update statement  - 1
SQL count after delete statement  - 1
SQL count after commit statement  - 0
SANJAY  DUTT record deleted from emp_temp table

    
DROP TABLE emp_temp;

 Pseudocolumn in PL/SQL

CREATE TABLE emp_temp (
      empno         NUMBER(4) PRIMARY KEY,
      first_name    VARCHAR2(20),
      last_name     VARCHAR2(20)
      );
 
CREATE  SEQUENCE empno_seq
       START  WITH 1
       INCREMENT BY 1;
      
     
DECLARE
      v_first_name    emp_temp.first_name%TYPE;
      v_last_name     emp_temp.last_name%TYPE;
      v_empno         emp_temp.empno%TYPE;
      v_rowid           varchar2(18);
BEGIN
      SELECT empno_seq.NEXTVAL INTO v_empno FROM DUAL;
      DBMS_OUTPUT.PUT_LINE ('Next Value : '||v_empno);

      SELECT empno_seq.CURRVAL INTO v_empno FROM DUAL;
      DBMS_OUTPUT.PUT_LINE ('Current Value : '||v_empno);
     
      INSERT INTO emp_temp ( empno,first_name,last_name)
             VALUES(empno_seq.nextval,'Sanjay','Dutt');
     
      SELECT ROWIDTOCHAR(rowid), empno,first_name,last_name
             INTO v_rowid,v_empno,v_first_name,v_last_name
      FROM emp_temp
      WHERE ROWNUM < 2;
     
      DBMS_OUTPUT.PUT_LINE (v_first_name ||' ' || v_last_name ||' EMP No ' || v_empno  || ' ROWID : ' || v_rowid);
     
      ROLLBACK;
EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( 'SQL Error - ' || sqlerrm);
END;

Result :
       Next Value : 1
       Current Value : 1
       Sanjay Dutt EMP No 2 ROWID : AAAM1IAAEAAAAG8AAA
     
DROP TABLE emp_temp;

DROP SEQUENCE empno_seq;

Raise Application Error

RAISE_APPLICATION_ERROR procdure defined in STANDARD package and it can only invoked from stored subprogram.This procedure is used to raise a error code and error message

      RAISE_APPLICATION_ERROR(error_code, message [,{TRUE|FALSE}]);

If TRUE specified, PL/SQL put error_code on the top of the error stack otherwise error stack replaced with the error_code

CREATE PROCEDURE training_status ( l_training_id VARCHAR2(10)) IS
   v_no_of_student NUMBER(3);
BEGIN
   SELECT COUNT(1) INTO V_no_of_student
         FROM training
         WHERE training_id = l_training_id;

    IF v_no_of_student >= 40 THEN --explicitly raise error
           RAISE_APPLICATION_ERROR(-20000,'Training class full');
    END IF;
END;
/
DECLARE
   e_trainign_full EXCEPTION;  --declare exception
   PRAGMA EXCEPTION_INIT e_trainign_full(-20000,e_class_full);
BEGIN
    training_status('ORACLE');
EXCEPTION
   WHEN e_trainign_full THEN
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/
Result:
    ORA-20000: Training Class full

SQL *Loader

SQL *Loader utility is used to load data from external files into oracle tables.  These files can be local files, network files, tape or names pipe. You can SQL *Loader to do the following:

• Load multiple data files in single load session
• Load multiple tables in a single load session
• Selective data load
• Manupulate data before loading using SQL function
• Can generate sequential key value
• Can load LOB and collection using secondary data files

SQL loader supports direct path loading that improves loading performance over conventional path loading.   

SQL *Loader supports:

• Nested Table ( Collection )
• VARRAY ( Collection )
• BLOB, CLOB, NBLOB, NCLOB ( LOB type )
• Partitioned Objects ( partitioned and none partitioned tables)

SQL *Loader uses control file which control SQL loader behavior and one or more data file. The output of SQL *Loader is log file, bad file and a possibly a discard file. SQL *Loader uses command line parameters, if these parameter are not frequently change then these parameter can be grouped together in a parameter file. Certain parameters can also be placed in control file using the OPTION clause.

SQL *Loader Control File

It is simple text file uses SQL *Loader understandable format. This file contains three section, first section more on session wide information

• Where to find data ( INFILE clause specify the location of input data )
• Global Option such as skip records, rows and bind size etc)

Second section consist of one or more INTO TABLE. Each INTO TABLE tells about columns and table where to be data loaded.

Third section is optional and normally this section contains the input data.

Input Data or Data file

Data file can be fixed record format, variable record format, or stream record format. You can specify record format using INFILE parameter. if no record format is specified then by default stream record format used. During loading process data fields in data files are converted into columns in the database. This is conceptually similar for conventional and direct path load but implementation are different.

Bad File

These files contains records that are not inserted into database. Bad file contains the records rejected either by SQL *Loader or Database. SQL loader rejects the records for invalid input format such as delimiter missing or field exceeds its maximum length.  Database rejects records if it violating constraint such as NOT NULL column is trying to populate with NULL. In both the scenario SQL *Loader or Database rejection, SQL *Loader put this record in bad file.

Discard File

SQL *Loader execution may create Discard file, this file is created only when it requires. SQL *Loader put filtered records (if filter condition specified in control file) in discard file. In simple words, Discard file contains record that are neither inserted into a table nor rejected.You can specify maximum discarded record limit.

Log File

SQL * Loader execution create the log file, if it can not create log file then SQL Loader process gets terminated. Log file contains detail summary of load and error, if any.

External table Vs SQL *Loader

SQL Loader and External Loader using the very similar in record parsing, so there is not major difference in term of performance. Due to architectural difference one is better than other are as follows:

• External table works better if you want to transform the data or you want get the benefit of parallel processing without splitting the data file physically.
• SQL *Loader works better when you don’t want to transform the data or want to load data remotely.

Raise Pre-defined exception explicitly

You can raise pre-defined exception explicitly.

SET SERVEROUTPUT ON
DECLARE
       deptno NUMBER(3) := 40;
BEGIN
        IF deptno NOT IN ( 10,20,30 ) THEN
             RAISE INVALID_NUMBER;
        END IF;
EXCEPTION
     WHEN INVALID_NUMBER THEN
         DBMS_OUTPUT.PUT_LINE (' Invalid Deptno supplied');
         ROLLBACK;
END;

Raise Exception using RAISE statement

PL/SQL block or subprogram should raise error message when undesirable or impossiable to finish processing. You can place RAISE statement for an exception within the scope of that exception.

SET SERVEROUTPUT ON
DECLARE
       out_of_stock  EXCEPTION;
       stock_in_hand   NUMBER(10)  := 100
BEGIN
       IF stock_in_hand < 1000 THEN
           RAISE out_of_stock;
       END IF;
EXCEPTION
     WHEN out_of_stock THEN
       DBMS_OUTPUT.PUT_LINE ('Stock in hand is less than 1000');
END;

PRAGMA EXCEPTION_INIT

To handle error condition that dont have predefined name, you must use OTHERS handler or Pragma EXECEPTION_INIT. A pragma is compiler directive that is process at compile time, not run time

In PL/SQL pragma EXCEPTION_INIT is declared in DECLARE section and it associate with oracle error number

SET SERVEROUTPUT ON
DECLARE
  invalid_depto EXCEPTION;
  --check constraint violated
  PRAGMA EXCEPTION_INIT (invalid_depto, -2290);
BEGIN
 INSERT INTO EMP (empno, ename,salary,deptno)
  VALUES (90,''Justin',2000,'X');
 COMMIT;
EXCEPTION
 WHEN invalid_depto THEN
  DBMS_OUTPUT.PUT_LINE('Dept No Check constraint violated');
END;

Difference between DELETE and TRUNCATE Command

 DELETE and TRUNCATE command used to remove table data. TRUNCATE is more efficient compare to DELETE statement with some side effects. Truncate is DDL statement, so there is no option to rollback the truncated data.You can’t TRUNCATE two tables as a single transaction.

When truncating a table, by default all the space is de-allocated for a table except the space defined by the MINEXTEND in table storage parameters. If you don’t want to de-allocate the currently allocated extend then use REUSE STORAGE clause with TRUNCATE statement.

     TRUNCATE TABLE <table name> REUSE STORAGE;

If you have chance to rollback or want to remove selective data, then you should use the DELETE statement. Delete statement generates a great amount of undo and redo information. This side effect causes DELETE statement takes more time compare to TRUNCATE

Truncate statement sets the high water mark of a table back to zero. On the other hand, DELETE statement does not change the high water mark.

You can’t TRUNCATE primary key defined parent table that is referenced by enabled foreign key constraints in child table – even the child table contains zero row. In this case oracle throw an error.