Live ORACLE

BEGIN
 -- Inefficient, calls function for every row
 FOR item IN (SELECT DISTINCT (Sqrt(department_id)) col_alias
              FROM   employees)
 LOOP
   dbms_output.Put_line(item.col_alias);
 END LOOP;
 -- Efficient, only calls function once for each distinct value.
 FOR item IN (SELECT Sqrt(department_id) col_alias
              FROM   (SELECT DISTINCT department_id
                      FROM   employees))
 LOOP
   dbms_output.Put_line(item.col_alias);
 END LOOP;
END;
/  

OUTPUT:-

7.07106781186547524400844362104849039285
9.48683298050513799599668063329815560116
8.36660026534075547978172025785187489393
10.48808848170151546991453513679937598475
3.16227766016837933199889354443271853372
6.32455532033675866399778708886543706744
5.47722557505166113456969782800802133953
4.47213595499957939281834733746255247088
7.74596669241483377035853079956479922167
8.94427190999915878563669467492510494176
10
10
5.47722557505166113456969782800802133953
4.47213595499957939281834733746255247088
8.36660026534075547978172025785187489393
9.48683298050513799599668063329815560116
10.48808848170151546991453513679937598475
7.07106781186547524400844362104849039285
6.32455532033675866399778708886543706744
8.94427190999915878563669467492510494176
3.16227766016837933199889354443271853372
7.74596669241483377035853079956479922167

PL/SQL procedure successfully completed.

Nesting a Query to Improve Performance

-- When warnings disabled, the following procedure compiles with no warnings
CREATE OR REPLACE PROCEDURE Unreachable_code
AS
x  CONSTANT BOOLEAN := true;
BEGIN
IF x THEN
dbms_output.Put_line('TRUE');
ELSE
dbms_output.Put_line('FALSE');
END IF;
END unreachable_code;
/

-- enable all warning messages for this session
CALL DBMS_WARNING.set_warning_setting_string('ENABLE:ALL' ,'SESSION');

-- Check the current warning setting
SELECT dbms_warning.Get_warning_setting_string()
FROM   dual;

-- Recompile the procedure and a warning about unreachable code displays
ALTER PROCEDURE unreachable_code COMPILE;
SHOW ERRORS; 

OUTPUT:-



you could have used the following ALTER PROCEDURE without the call to DBMS_WARNINGS.set_warning_setting_string:

ALTER PROCEDURE unreachable_code COMPILE PLSQL_WARNINGS = 'ENABLE:ALL' REUSE SETTINGS;

For more information, see ALTER PROCEDURE in Oracle Database SQL Reference,
DBMS_WARNING package in Oracle Database PL/SQL Packages and Types Reference, and
PLW- messages in Oracle Database Error Messages.

Using the DBMS_WARNING Package to Display Warnings

-- To focus on one aspect
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';

-- Recompile with extra checking
ALTER PROCEDURE loc_var COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE'
REUSE SETTINGS;

-- To turn off all warnings
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

-- Display 'severe' warnings, don't want 'performance' warnings, and
-- want PLW-06002 warnings to produce errors that halt compilation
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE',
'ERROR:06002';

-- For debugging during development
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Controlling the Display of PL/SQL Warnings

CREATE OR REPLACE PROCEDURE Loc_var
AS
stmt_no  NUMBER;
NAME     VARCHAR2(100);
BEGIN
stmt_no := 1; -- designates 1st SELECT statement
SELECT table_name
INTO   NAME
FROM   user_tables
WHERE  table_name LIKE 'ABC%';
stmt_no := 2; -- designates 2nd SELECT statement
SELECT table_name
INTO   NAME
FROM   user_tables
WHERE  table_name LIKE 'XYZ%';
EXCEPTION
WHEN no_data_found THEN
  dbms_output.Put_line('Table name not found in query '
                       ||stmt_no);
END;
/  

OUTPUT:-

Procedure created.

CALL loc_var(); 

OUTPUT:-

Table name not found in query 1

Call completed.

Using a Locator Variable to Identify the Location of an Exception

DROP TABLE results CASCADE CONSTRAINTS PURGE;


CREATE TABLE results (
 res_name   VARCHAR(20),
 res_answer VARCHAR2(3));



DROP INDEX res_name_ix;



CREATE UNIQUE INDEX res_name_ix ON results (
     res_name);



INSERT INTO results
VALUES     ('SMYTHE',
           'YES');



INSERT INTO results
VALUES     ('JONES',
           'NO');



DECLARE
 NAME    VARCHAR2(20) := 'SMYTHE';
 answer  VARCHAR2(3) := 'NO';
 suffix  NUMBER := 1;
BEGIN
 FOR i IN 1.. 5 LOOP -- try 5 times
   BEGIN -- sub-block begins
     SAVEPOINT start_transaction; -- mark a savepoint
     /* Remove rows from a table of survey results. */
     DELETE FROM results
     WHERE       res_answer = 'NO';
     /* Add a survey respondent's name and answers. */
     INSERT INTO results
     VALUES     (NAME,
                 answer);
     -- raises DUP_VAL_ON_INDEX if two respondents have the same name
     COMMIT;
     EXIT;
   EXCEPTION
     WHEN dup_val_on_index THEN
       ROLLBACK TO start_transaction; -- undo changes
       suffix := suffix
                   + 1; -- try to fix problem
       NAME := NAME
               ||To_char(suffix);
   END; -- sub-block ends
 END LOOP;
END;
/  

Retrying a Transaction After an Exception

DROP TABLE employees_temp CASCADE CONSTRAINTS PURGE;


CREATE TABLE employees_temp
 AS
 SELECT employee_id,
        salary,
        commission_pct
 FROM   employees;



DECLARE
 sal_calc  NUMBER(8,2);
BEGIN
 INSERT INTO employees_temp
 VALUES     (301,
             2500,
             0);
 SELECT salary
          / commission_pct
 INTO   sal_calc
 FROM   employees_temp
 WHERE  employee_id = 301;
 INSERT INTO employees_temp
 VALUES     (302,
             sal_calc
               / 100,
             .1);
EXCEPTION
 WHEN zero_divide THEN
   NULL;
END;
/

DECLARE
 sal_calc  NUMBER(8,2);
BEGIN
 INSERT INTO employees_temp
 VALUES     (303,
             2500,
             0);
 BEGIN -- sub-block begins
   SELECT salary
            / commission_pct
   INTO   sal_calc
   FROM   employees_temp
   WHERE  employee_id = 301;
 EXCEPTION
   WHEN zero_divide THEN
     sal_calc := 2500;
 END; -- sub-block ends
 INSERT INTO employees_temp
 VALUES     (304,
             sal_calc
               / 100,
             .1);
EXCEPTION
 WHEN zero_divide THEN
   NULL;
END;
/  

Continuing after an Exception Is Raised

CREATE TABLE errors (
 code     NUMBER,
 message  VARCHAR2(64),
 happened TIMESTAMP);


DECLARE
 NAME    employees.last_name%TYPE;
 v_code  NUMBER;
 v_errm  VARCHAR2(64);
BEGIN
 SELECT last_name
 INTO   NAME
 FROM   employees
 WHERE  employee_id = -1;
EXCEPTION
 WHEN OTHERS THEN
   v_code := SQLCODE;
   v_errm := Substr(sqlerrm,1,64);
   dbms_output.Put_line('Error code '
                        ||v_code
                        ||': '
                        ||v_errm);
   -- Normally we would call another procedure, declared with PRAGMA
   -- AUTONOMOUS_TRANSACTION, to insert information about errors.
   INSERT INTO errors
   VALUES     (v_code,
               v_errm,
               systimestamp);
END;
/  

OUTPUT:-

Error code 100: ORA-01403: no data found

PL/SQL procedure successfully completed.

Displaying SQLCODE and SQLERRM

How PL/SQL Exceptions Propagate