Live ORACLE

Oracle Magazine - March/April 2010

2010-02-20T17:58:00.001+02:00

March/April 2010

Declaring and Assigning Values to Variables

2009-12-22T21:51:00.003+02:00

DECLARE
wages         NUMBER;
hours_worked  NUMBER := 40;
hourly_salary  CONSTANT NUMBER := 17.50; -- constant value does not change
country       VARCHAR2(64) := 'UNKNOWN';
unknown       BOOLEAN;
TYPE comm_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
commissions   COMM_TAB;
TYPE jobs_var IS VARRAY(10) OF employees.job_id%TYPE;
jobids        JOBS_VAR;
CURSOR c1 IS
  SELECT department_id
  FROM   departments; -- cursor declaration
deptid        departments.department_id%TYPE;
emp_rec       employees%ROWTYPE; -- do not need TYPE declaration in this case

BEGIN
/* the following are examples of assignment statements */
wages := hours_worked
           * hourly_salary; -- compute wages
country := Upper('italy');
unknown := (country = 'UNKNOWN');
Commissions(5) := 20000
                    * 0.15;
Commissions(8) := 20000
                    * 0.18;
jobids := Jobs_var('ST_CLERK');
jobids.Extend(1);
Jobids(2) := 'SH_CLERK';
OPEN c1;
FETCH c1 INTO deptid;
CLOSE c1;
emp_rec.department_id := deptid;
emp_rec.job_id := Jobids(2);
END;
/

Oracle Magazine - January/February 2010

2009-12-21T21:29:00.001+02:00

January/February 2010

TEAMS Package Using Dynamic SQL for Object Types and Collections

2009-12-19T12:38:00.004+02:00

--Cleanup ...........
DROP PACKAGE teams
/

DROP TYPE person_typ
/

DROP TYPE hobbies_var
/

-- Creating ..........
CREATE TYPE person_typ AS OBJECT
(NAME VARCHAR2(25),
age NUMBER
)
/

CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25)
/

CREATE OR REPLACE PACKAGE teams
AUTHID current_user
AS
PROCEDURE create_table(tab_name  VARCHAR2);

PROCEDURE insert_row(tab_name  VARCHAR2,p  PERSON_TYP,h  HOBBIES_VAR);

PROCEDURE print_table(tab_name  VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY teams
AS
PROCEDURE Create_table
   (tab_name  VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '
                  ||tab_name
                  ||' (pers person_typ, hobbs hobbies_var)';
END;

PROCEDURE Insert_row
   (tab_name  VARCHAR2,
    p         PERSON_TYP,
    h         HOBBIES_VAR)
IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO '
                  ||tab_name
                  ||' VALUES (:1, :2)'
 USING p,h;
END;

PROCEDURE Print_table
   (tab_name  VARCHAR2)
IS
TYPE refcurtyp IS REF CURSOR;
v_cur  REFCURTYP;
p      PERSON_TYP;
h      HOBBIES_VAR;
BEGIN
OPEN v_cur FOR 'SELECT pers, hobbs FROM '
               ||tab_name;
LOOP
  FETCH v_cur INTO p,h;
  EXIT WHEN v_cur%NOTFOUND;
  -- print attributes of 'p' and elements of 'h'
  dbms_output.Put_line('Name: '
                       ||p.NAME
                       ||' - Age: '
                       ||p.age);
  FOR i IN h.FIRST.. h.LAST LOOP
    dbms_output.Put_line('Hobby('
                         ||i
                         ||'): '
                         ||H(i));
  END LOOP;
END LOOP;
CLOSE v_cur;
END;
END;
/

OUTPUT: Click Here

Calling Procedures from the TEAMS Package

DECLARE
 team_name  VARCHAR2(15);
BEGIN
 team_name := 'Notables';
 teams.Create_table(team_name);
 teams.Insert_row(team_name,Person_typ('John',31),Hobbies_var('skiing','coin collecting','tennis'));
 teams.Insert_row(team_name,Person_typ('Mary',28),Hobbies_var('golf','quilting','rock climbing','fencing'));
 teams.Print_table(team_name);
END;
/

OUTPUT: Click Here

Using BULK COLLECT with Nested Tables

2009-12-18T01:15:00.003+02:00

-- Cleanup before Creating
DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

-- Creating ........
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30)
/

CREATE TABLE depts (
 region     VARCHAR2(25),
 dept_names DNAMES_VAR)
/

BEGIN
 INSERT INTO depts
 VALUES     ('Europe',
             Dnames_var('Shipping','Sales','Finance'));
 INSERT INTO depts
 VALUES     ('Americas',
             Dnames_var('Sales','Finance','Shipping'));
 INSERT INTO depts
 VALUES     ('Asia',
             Dnames_var('Finance','Payroll','Shipping','Sales'));
 COMMIT;
END;
/

DECLARE
 TYPE dnames_tab IS TABLE OF DNAMES_VAR;
 v_depts  DNAMES_TAB;
BEGIN
 SELECT dept_names
 BULK COLLECT INTO v_depts
 FROM   depts;
 dbms_output.Put_line(v_depts.COUNT); -- prints 3

END;
/

OUTPUT: Click Here

Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays

2009-12-15T20:24:00.007+02:00

DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

-- By using a varray, we put an upper limit on the number of elements
-- and ensure they always come back in the same order
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/

CREATE TABLE depts (
region     VARCHAR2(25),
dept_names DNAMES_VAR);

BEGIN
INSERT INTO depts
VALUES     ('Europe',
           Dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts
VALUES     ('Americas',
           Dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts
VALUES     ('Asia',
           Dnames_var('Finance','Payroll','Shipping','Sales'));
COMMIT;
END;
/

DECLARE
new_dnames   DNAMES_VAR := Dnames_var('Benefits','Advertising','Contracting','Executive',
                                     'Marketing');
some_dnames  DNAMES_VAR;
BEGIN
UPDATE depts
SET    dept_names = new_dnames
WHERE  region = 'Europe';
COMMIT;
SELECT dept_names
INTO   some_dnames
FROM   depts
WHERE  region = 'Europe';
FOR i IN some_dnames.FIRST.. some_dnames.LAST LOOP
 dbms_output.Put_line('dept_names = '
                      ||Some_dnames(i));
END LOOP;
END;
/

OUTPUT: Click Here

Using INSERT, UPDATE, DELETE, and SELECT Statements With Nested Tables

2009-12-14T18:47:00.000+02:00

CREATE TYPE dnames_tab AS TABLE OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_tab)
NESTED TABLE dept_names STORE AS dnames_nt;
BEGIN
INSERT INTO depts VALUES('Europe', dnames_tab('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_tab('Sales','Finance','Shipping'));
INSERT INTO depts VALUES('Asia', dnames_tab('Finance','Payroll'));
COMMIT;
END;
/
DECLARE
-- Type declaration is not needed, because PL/SQL can access the SQL object type
-- TYPE dnames_tab IS TABLE OF VARCHAR2(30); not needed
-- Declare a variable that can hold a set of department names
v_dnames dnames_tab;
-- Declare a record that can hold a row from the table
-- One of the record fields is a set of department names
v_depts depts%ROWTYPE;
new_dnames dnames_tab;
BEGIN
-- Look up a region and query just the associated department names
SELECT dept_names INTO v_dnames FROM depts WHERE region = 'Europe';
FOR i IN v_dnames.FIRST .. v_dnames.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Department names: ' || v_dnames(i));
END LOOP;
-- Look up a region and query the entire row
SELECT * INTO v_depts FROM depts WHERE region = 'Asia';
-- Now dept_names is a field in a record, so we access it with dot notation
FOR i IN v_depts.dept_names.FIRST .. v_depts.dept_names.LAST LOOP
-- Because we have all the table columns in the record, we can refer to region
DBMS_OUTPUT.PUT_LINE(v_depts.region || ' dept_names = ' ||
v_depts.dept_names(i));
END LOOP;
-- We can replace a set of department names with a new collection
-- in an UPDATE statement
new_dnames := dnames_tab('Sales','Payroll','Shipping');
UPDATE depts SET dept_names = new_dnames WHERE region = 'Europe';
-- Or we can modify the original collection and use it in the UPDATE.
-- We'll add a new final element and fill in a value
v_depts.dept_names.EXTEND(1);
v_depts.dept_names(v_depts.dept_names.COUNT) := 'Finance';
UPDATE depts SET dept_names = v_depts.dept_names
WHERE region = v_depts.region;
-- We can even treat the nested table column like a real table and
-- insert, update, or delete elements. The TABLE operator makes the statement
-- apply to the nested table produced by the subquery.
INSERT INTO TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
VALUES('Sales');
DELETE FROM TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
WHERE column_value = 'Payroll';
UPDATE TABLE(SELECT dept_names FROM depts WHERE region = 'Americas')
SET column_value = 'Payroll' WHERE column_value = 'Finance';
COMMIT;
END;
/

Performing Operations on PL/SQL Nested Tables With CAST

2009-12-11T14:19:00.004+02:00

DROP TABLE department CASCADE CONSTRAINTS PURGE
/

DROP TYPE CourseList
/

DROP TYPE Course
/

CREATE TYPE Course AS OBJECT
(course_no NUMBER,
title VARCHAR2(64),
credits NUMBER);
/

CREATE TYPE CourseList AS TABLE OF course;
/

CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab
/

INSERT INTO department VALUES ('English', 'June Johnson', '491C',
CourseList(
Course(1002, 'Expository Writing', 4),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 4)))
/

DECLARE
revised CourseList :=
CourseList(Course(1002, 'Expository Writing', 3),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 5));
num_changed INTEGER;
BEGIN
SELECT COUNT(*) INTO num_changed
FROM TABLE(CAST(revised AS CourseList)) new,
TABLE(SELECT courses FROM department
WHERE name = 'English') old
WHERE new.course_no = old.course_no AND
(new.title != old.title OR new.credits != old.credits);
DBMS_OUTPUT.PUT_LINE(num_changed);
END;
/

OUTPUT: Click Here

Nested Table Constructor Within a SQL Statement

2009-12-08T21:37:00.000+02:00

20:46:31 SQL> DROP TABLE sophomores CASCADE CONSTRAINTS PURGE
20:46:32   2  /
DROP TABLE sophomores CASCADE CONSTRAINTS PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


Elapsed: 00:00:00.01
20:46:32 SQL> DROP TYPE student
20:46:32   2  /
DROP TYPE student
*
ERROR at line 1:
ORA-04043: object STUDENT does not exist 


Elapsed: 00:00:00.00
20:46:32 SQL> DROP TYPE CourseList
20:46:32   2  /
DROP TYPE CourseList
*
ERROR at line 1:
ORA-04043: object COURSELIST does not exist 


Elapsed: 00:00:00.00
20:46:32 SQL> CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)
20:46:32   2  /

Type created.

Elapsed: 00:00:00.03
20:46:32 SQL> CREATE TYPE student AS OBJECT (
20:46:32   2  id_num INTEGER(4),
20:46:32   3  name VARCHAR2(25),
20:46:32   4  address VARCHAR2(35),
20:46:32   5  status CHAR(2),
20:46:32   6  courses CourseList)
20:46:32   7  /

Type created.

Elapsed: 00:00:00.06
20:46:32 SQL> CREATE TABLE sophomores of student NESTED TABLE courses STORE AS courses_nt
20:46:32   2  /

Table created.

Elapsed: 00:00:00.04
20:46:32 SQL> BEGIN
20:46:32   2  INSERT INTO sophomores
20:46:32   3  VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT',
20:46:32   4  CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'));
20:46:32   5  END;
20:46:32   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
20:46:33 SQL> SPOOL OFF

Creating a Table with a Varray Column

2009-11-21T17:27:00.005+02:00

DROP TABLE dept_projects CASCADE CONSTRAINTS PURGE;
DROP type projectlist;

-- Each project has a 16-character code name.
-- We will store up to 50 projects at a time in a database column.
CREATE TYPE projectlist AS VARRAY(50) OF VARCHAR2(16);
/

CREATE TABLE dept_projects ( -- create database table
dept_id  NUMBER(2),
NAME     VARCHAR2(15),
budget   NUMBER(11,2),
-- Each department can have up to 50 projects.
projects PROJECTLIST);

Varray Constructor Within a SQL Statement


 BEGIN
 INSERT INTO dept_projects
 VALUES     (60,
             'Security',
             750400,
             Projectlist('New Badges','Track Computers','Check Exits'));
END;
/

Declaring a Nested Table in SQL

2009-11-20T20:42:00.002+02:00

DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;
DROP TYPE student;
DROP TYPE CourseList;
-------------------------------------------
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
/
CREATE TYPE student AS OBJECT ( -- create object
id_num INTEGER(4),
name VARCHAR2(25),
address VARCHAR2(35),
status CHAR(2),
courses CourseList); -- declare nested table as attribute
/
CREATE TABLE sophomores of student
NESTED TABLE courses STORE AS courses_nt
/

OUTPUT:-

20:40:43 SQL> DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;

Table dropped.

Elapsed: 00:00:00.04
20:40:44 SQL> DROP TYPE student;

Type dropped.

Elapsed: 00:00:00.09
20:40:44 SQL> DROP TYPE CourseList;

Type dropped.

Elapsed: 00:00:00.06

20:40:44 SQL> CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
20:40:44   2  /

Type created.

Elapsed: 00:00:00.01
20:40:44 SQL> CREATE TYPE student AS OBJECT ( -- create object
20:40:44   2  id_num INTEGER(4),
20:40:44   3  name VARCHAR2(25),
20:40:44   4  address VARCHAR2(35),
20:40:44   5  status CHAR(2),
20:40:44   6  courses CourseList); -- declare nested table as attribute
20:40:44   7  /

Type created.

Elapsed: 00:00:00.06
20:40:44 SQL> CREATE TABLE sophomores of student
20:40:44   2  NESTED TABLE courses STORE AS courses_nt
20:40:44   3  /

Table created.

Elapsed: 00:00:00.03

Using DEREF in a SELECT INTO Statement

2009-11-18T19:07:00.001+02:00

DECLARE
 emp       EMPLOYEE_TYP;
 emp_ref   REF EMPLOYEE_TYP;
 emp_name  VARCHAR2(50);
BEGIN
 SELECT Ref(e)
 INTO   emp_ref
 FROM   employee_tab e
 WHERE  e.employee_id = 370;
 -- the following assignment raises an error, not allowed in PL/SQL
 -- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
 -- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements
 SELECT Deref(emp_ref)
 INTO   emp
 FROM   dual; -- use dummy table DUAL
 emp_name := emp.first_name
             ||' '
             ||emp.last_name;
 dbms_output.Put_line(emp_name);
END;
/

Updating Rows in an Object Table With a REF Modifier

2009-11-18T19:04:00.001+02:00

DECLARE
 emp      EMPLOYEE_TYP;
 emp_ref  REF EMPLOYEE_TYP;
BEGIN
 SELECT Ref(e)
 INTO   emp_ref
 FROM   employee_tab e
 WHERE  e.employee_id = 370;
 UPDATE employee_tab e
 SET    e.address = Address_typ('8701 College','Oakland','CA','94321')
 WHERE  Ref(e) = emp_ref;
END;
/

Updating and Deleting Rows in an Object Table

2009-11-14T22:42:00.001+02:00

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 INSERT INTO employee_tab
 VALUES     (Employee_typ(370,'Robert','Myers','RMYERS','555.111.2277',
                          '07-NOV-04','SA_REP',8800,.12,101,110,Address_typ('540 Fillmore','San Francisco','CA','94011')));
 UPDATE employee_tab e
 SET    e.address.street = '1040 California'
 WHERE  e.employee_id = 370;
 DELETE FROM employee_tab e
 WHERE       e.employee_id = 310;
END;
/

Inserting Rows in an Object Table

2009-11-14T00:06:00.001+02:00

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 INSERT INTO employee_tab
 VALUES     (Employee_typ(310,'Evers','Boston','EBOSTON','555.111.2222',
                          '01-AUG-04','SA_REP',9000,.15,101,110,Address_typ('123 Main','San Francisco','CA','94111')));
 INSERT INTO employee_tab
 VALUES     (Employee_typ(320,'Martha','Dunn','MDUNN','555.111.3333',
                          '30-SEP-04','AC_MGR',12500,0,101,110,Address_typ('123 Broadway','Redwood City','CA','94065')));
END;
/

Accessing Object Attributes

2009-11-13T02:31:00.001+02:00

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',
                     '01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));
 dbms_output.Put_line(emp.first_name
                      ||' '
                      ||emp.last_name);
 dbms_output.Put_line(emp.address.street);
 dbms_output.Put_line(emp.address.city
                      ||', '
                      ||emp.address.state
                      ||' '
                      ||emp.address.postal_code);
END;
/

OUTPUT:-

Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Null Objects in a PL/SQL Block

2009-11-12T00:19:00.002+02:00

DECLARE
 emp  EMPLOYEE_TYP; -- emp is atomically null

BEGIN
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #1');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #1');
 END IF;
 emp.employee_id := 330;
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #2');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #2');
 END IF;
 emp := Employee_typ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
                     NULL,NULL,Address_typ(NULL,NULL,NULL,NULL));
 -- emp := NULL; -- this would have made the following IF statement TRUE
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #3');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #3');
 END IF;
EXCEPTION
 WHEN access_into_null THEN
   dbms_output.Put_line('Cannot assign value to NULL object');
END;
/

OUTPUT:-

emp is NULL #1
emp.employee_id is NULL #1
emp is NULL #2
emp.employee_id is NULL #3

PL/SQL procedure successfully completed.

Declaring Object Types in a PL/SQL Block

2009-11-11T01:37:00.003+02:00

DECLARE

 emp  EMPLOYEE_TYP; -- emp is atomically null



BEGIN

 -- call the constructor for employee_typ

 emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',

                     '01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));

 dbms_output.Put_line(emp.first_name

                      ||' '

                      ||emp.last_name); -- display details

 emp.Display_address(); -- call object method to display details



END;

/

OUTPUT:-

Francis Logan
Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Working With Object Types

2009-11-10T03:42:00.003+02:00

DROP TYPE address_typ;

CREATE TYPE address_typ AS OBJECT
 (street VARCHAR2(30),
  city VARCHAR2(20),
  state CHAR(2),
  postal_code VARCHAR2(6)
 );
/

DROP TYPE employee_typ;

CREATE TYPE employee_typ AS OBJECT
 (employee_id NUMBER(6),
  first_name VARCHAR2(20),
  last_name VARCHAR2(25),
  email VARCHAR2(25),
  phone_number VARCHAR2(20),
  hire_date DATE,
  job_id VARCHAR2(10),
  salary NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER(4),
  address ADDRESS_TYP,
  MAP MEMBER FUNCTION get_idno RETURN NUMBER,
  MEMBER PROCEDURE display_address(SELF  IN OUT NOCOPY EMPLOYEE_TYP)
 );
/

CREATE TYPE BODY employee_typ AS
 MAP MEMBER FUNCTION Get_idno
 RETURN NUMBER
 IS
 BEGIN
   RETURN employee_id;
 END;
 MEMBER PROCEDURE Display_address
      (SELF  IN OUT NOCOPY EMPLOYEE_TYP)
 IS
 BEGIN
   dbms_output.Put_line(first_name
                        ||' '
                        ||last_name);
   dbms_output.Put_line(address.street);
   dbms_output.Put_line(address.city
                        ||', '
                        ||address.state
                        ||' '
                        ||address.postal_code);
 END;
END;
/

CREATE TABLE employee_tab OF employee_typ;

OUTPUT: ALL Feedback return without any Errors.

Using a Pipelined Table Function as an Aggregate Function

2009-11-10T03:34:00.002+02:00

DROP TABLE gradereport CASCADE CONSTRAINTS PURGE;

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),
weight NUMBER, grade NUMBER);

INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);
INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);
INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);

DROP PACKAGE pkg_gpa;

CREATE PACKAGE pkg_gpa IS
TYPE gpa IS TABLE OF NUMBER;
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED;
END pkg_gpa;
/

CREATE PACKAGE BODY pkg_gpa IS
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
-- The function accepts a ref cursor and loops through all the input rows
LOOP
FETCH input_values INTO weight, grade;
EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average
total_weight := total_weight + weight;
total := total + grade*weight;
END LOOP;
PIPE ROW (total / total_weight);
RETURN; -- the function returns a single result
END;
END pkg_gpa;
/

-- the query result comes back as a nested table with a single row
-- COLUMN_VALUE is a keyword that returns the contents of a nested table
SELECT w.column_value "weighted result" FROM TABLE(
pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

OUTPUT:-

weighted result
---------------
            3.5

Using Multiple REF CURSOR Input Variables

2009-11-10T03:04:00.005+02:00

DROP PACKAGE refcur_pkg;

-- Define the ref cursor types
CREATE PACKAGE refcur_pkg IS
TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE PACKAGE BODY refcur_pkg IS
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec1 p1%ROWTYPE;
in_rec2 p2%ROWTYPE;
BEGIN
LOOP
FETCH p2 INTO in_rec2;
EXIT WHEN p2%NOTFOUND;
END LOOP;
CLOSE p2;
LOOP
FETCH p1 INTO in_rec1;
EXIT WHEN p1%NOTFOUND;
-- first row
out_rec.var_num := in_rec1.employee_id;
out_rec.var_char1 := in_rec1.first_name;
out_rec.var_char2 := in_rec1.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_num := in_rec2.department_id;
out_rec.var_char1 := in_rec2.department_name;
out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
PIPE ROW(out_rec);
END LOOP;
CLOSE p1;
RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the g_trans table function
SELECT * FROM TABLE(refcur_pkg.g_trans(
CURSOR(SELECT * FROM employees WHERE department_id = 60),
CURSOR(SELECT * FROM departments WHERE department_id = 60)));

OUTPUT:-

Using a Pipelined Table Function For a Transformation

2009-11-10T02:38:00.004+02:00

DROP PACKAGE refcur_pkg;

-- Define the ref cursor types and function
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.var_num := in_rec.employee_id;
out_rec.var_char1 := in_rec.first_name;
out_rec.var_char2 := in_rec.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_char1 := in_rec.email;
out_rec.var_char2 := in_rec.phone_number;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the f_transc table function
SELECT * FROM TABLE(
refcur_pkg.f_trans(CURSOR(SELECT * FROM employees WHERE department_id = 60)));

OUTPUT:-

Assigning the Result of a Table Function

2009-11-10T02:27:00.002+02:00

CREATE PACKAGE pkg1 AS
TYPE numset_t IS TABLE OF NUMBER;
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
BEGIN
FOR i IN 1..x LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
END pkg1;
/

-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));

Checking for PLSQL Compilation Type

2009-11-10T02:02:00.002+02:00

SELECT   TYPE,
       plsql_code_type,
       Count(* )
FROM     dba_plsql_object_settings
WHERE    plsql_code_type IS NOT NULL
GROUP BY TYPE,
       plsql_code_type
ORDER BY TYPE,
       plsql_code_type;

OUTPUT:-

Using NOCOPY With Parameters

2009-11-10T01:56:00.003+02:00

DECLARE
 TYPE emptabtyp IS TABLE OF employees%ROWTYPE;
 emp_tab  EMPTABTYP := Emptabtyp(NULL); -- initialize
 t1       NUMBER;
 t2       NUMBER;
 t3       NUMBER;
 PROCEDURE Get_time
      (t  OUT NUMBER)
 IS
 BEGIN
   t := dbms_utility.get_time;
 END;
 PROCEDURE Do_nothing1
      (tab  IN OUT EMPTABTYP)
 IS
 BEGIN
   NULL;
 END;
 PROCEDURE Do_nothing2
      (tab  IN OUT NOCOPY EMPTABTYP)
 IS
 BEGIN
   NULL;
 END;
BEGIN
 SELECT *
 INTO   Emp_tab(1)
 FROM   employees
 WHERE  employee_id = 100;
 emp_tab.Extend(49999,1); -- copy element 1 into 2..50000
 Get_time(t1);
 Do_nothing1(emp_tab); -- pass IN OUT parameter
 Get_time(t2);
 Do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
 Get_time(t3);
 dbms_output.Put_line('Call Duration (secs)');
 dbms_output.Put_line('--------------------');
 dbms_output.Put_line('Just IN OUT: '
                      ||To_char((t2
                                   - t1)
                                  / 100.0));
 dbms_output.Put_line('With NOCOPY: '
                      ||To_char((t3
                                   - t2))
                          / 100.0);
END;
/

OUTPUT:-

Call Duration (secs)
--------------------
Just IN OUT: .16
With NOCOPY: 0

PL/SQL procedure successfully completed.

Site Map

2009-11-09T19:27:00.003+02:00

Site Map

Homepage

Last updated: 2009, November 18

/ 12 pages

Live ORACLE

Live ORACLE: November 2009

Live ORACLE: October 2009

Live ORACLE: September 2009

Live ORACLE: July 2009

Live ORACLE: June 2009

Live ORACLE: May 2009

Live ORACLE: April 2009

Live ORACLE: March 2009

Live ORACLE: February 2009

Live ORACLE: December 2008

Live ORACLE: November 2008

11/ 27 pages

Live ORACLE: password encryption package

Live ORACLE: populate a PL/SQL Table from a cursor

Live ORACLE: Profile PL/SQL code to get run-time statistics

Live ORACLE: BULK COLLECT and BULK BIND

Live ORACLE: UPDATE/DELETE in a LOOP

Live ORACLE: Delete duplicate values from a table

Live ORACLE: Convert LONG datatypes to LOBs (Large Objects)

Live ORACLE: VARRAY

Live ORACLE: Oracle database types and object tables

Live ORACLE: Count the number of rows for ALL tables in the current schema

Live ORACLE: Encoding and Decoding of Secret Messages

Live ORACLE: Spell out Numbers to Words

Live ORACLE: Check if a year is a leap year

Live ORACLE: Example of how to pass application info through to Oracle RDBMS

Live ORACLE: Display table and column comments for the current schema

Live ORACLE: Example script to demonstrate DEFAULT column values

Live ORACLE: Select the Nth highest - lowest value from a table

Live ORACLE: Reports information about your current database context

Live ORACLE: Show database version with options installed

Live ORACLE: Oracle Magazine (May/June 2008)

Live ORACLE: Oracle Magazine (July/August 2008)

Live ORACLE: Oracle Magazine (September/October 2008)

Live ORACLE: CROSS MATRIX report

Live ORACLE: SQL Hacks Source code

Live ORACLE: Oracle to Excel via Sqlplus

Live ORACLE: Oracle Magazine (November/December 2008)

Live ORACLE: BULK COLLECT and BULK BIND

12/ 41 pages

Live ORACLE: Oracle Database Design

Live ORACLE: Oracle Forms & Reports

Live ORACLE: Oracle Database Programming

Live ORACLE: Introduction on Oracle Database

Live ORACLE: Register on Oracle.com

Live ORACLE: Make a shortcut Icon for your Oracle Project

Live ORACLE: Oracle Magazine (January/February 2009)

Live ORACLE: Download and Install Oracle DB and Developer Step by Step

Live ORACLE: Foreign Key by TOAD

Live ORACLE: Package to generate HTML-type documentation for Oracle objects

Live ORACLE: Converts a string of text into seperate soundex values

Live ORACLE: NYSIIS function (an improvement on soundex)

Live ORACLE: convert numbers to chars for printing cheques amount in Indian Style

Live ORACLE: This function will convert a number to words, handy for ptinting cheques

Live ORACLE: Replace all occurences of a substring with another substring

Live ORACLE: Count the number of rows for ALL tables in current schema

Live ORACLE: List tables from schema with more than X rows

Live ORACLE: Maintain an audit log of DDL changes (alter/ drop/ create) within a schema

Live ORACLE: Access Internet Web pages from SQL or PL/SQL

Live ORACLE: Send e-mail messages and attachments from PL/SQL (using UTL_SMTP)

Live ORACLE: Send e-mail messages and attachments from PL/SQL

Live ORACLE: Send e-mail messages from PL/SQL

Live ORACLE: Execute operating system commands from PL/SQL

Live ORACLE: Demonstrate Java stored procedures

Live ORACLE: Example PL/SQL code to demonstrate Dynamic SQL

Live ORACLE: Create a database view on top of a file

Live ORACLE: writing to a file using the UTL_FILE package

Live ORACLE: Fetch Long column values piece-wise from PL/SQL

Live ORACLE: Load a binary file (images, documents, etc) into a database table

Live ORACLE: Fetch LOB column values piece-wise from PL/SQL

Live ORACLE: Algorithms

Live ORACLE: Print ASCII table

Live ORACLE: Check if a year is a leap year (FUNCTION)

Live ORACLE: Random number/ string generator package

Live ORACLE: Package with functions to convert numbers between the Decimal, Binary, Octal and Hexidecimal numbering systems

Live ORACLE: Pass Result sets (REF CURSOR) between Procedures and Functions

Live ORACLE: Oracle Database Programming

Live ORACLE: Download and Install Oracle DB and Developer Step by Step

Live ORACLE: Execute operating system commands from PL/SQL

Live ORACLE: Fetch Long column values piece-wise from PL/SQL

02/ 143 pages

Live ORACLE: Using ROWNUM

Live ORACLE: Using CURRVAL and NEXTVAL

Live ORACLE: Oracle Magazine - March/April 2009

Live ORACLE: Calling the SQL COUNT Function in PL/SQL

Live ORACLE: Substituting PL/SQL Variables

Live ORACLE: Checking SQL%ROWCOUNT After an UPDATE

Live ORACLE: Data Manipulation With PL/SQL

Live ORACLE: Using BULK COLLECT With a SELECT INTO Statement

Live ORACLE: Using the RETURNING Clause with a Record

Live ORACLE: Updating a Row Using a Record

Live ORACLE: Inserting a PL/SQL Record Using %ROWTYPE

Live ORACLE: Using SELECT INTO to Assign Values in a Record

Live ORACLE: Assigning All the Fields of a Record in One Statement

Live ORACLE: Assigning Default Values to a Record

Live ORACLE: Declaring a Nested Record

Live ORACLE: Using a Record as Parameter to a Procedure

Live ORACLE: Returning a Record from a Function

Live ORACLE: Using %ROWTYPE to Declare a Record

Live ORACLE: Declaring and Initializing Record Types

Live ORACLE: Declaring and Initializing a Simple Record Type

Live ORACLE: Incompatibility Between Package and Local Collection Types

Live ORACLE: How Invalid Subscripts are Handled With DELETE(n)

Live ORACLE: Collection Exceptions

Live ORACLE: Using the DELETE Method on a Collection

Live ORACLE: Using TRIM on Deleted Elements

Live ORACLE: Using TRIM to Decrease the Size of a Collection

Live ORACLE: Using EXTEND to Increase the Size of a Collection

Live ORACLE: Using NEXT to Access Elements of a Nested Table

Live ORACLE: Using PRIOR and NEXT to Access Collection Elements

Live ORACLE: Using FIRST and LAST With a Collection

Live ORACLE: Checking the Maximum Size of a Collection With LIMIT

Live ORACLE: Counting Collection Elements With COUNT

Live ORACLE: Checking Whether a Collection Element EXISTS

Live ORACLE: Multilevel Associative Array

Live ORACLE: Multilevel Nested Table

Live ORACLE: Multilevel VARRAY

Live ORACLE: Comparing Nested Tables with Set Operators

Live ORACLE: Comparing Two Nested Tables

Live ORACLE: Checking if a Collection Is Null

Live ORACLE: Assigning Values to Tables with Complex Datatypes

Live ORACLE: Assigning Values to VARRAYs with Complex Datatypes

Live ORACLE: Assigning Nested Tables with Set Operators

Live ORACLE: Assigning a Null Value to a Nested Table

Live ORACLE: Datatype Compatibility for Collection Assignment

Live ORACLE: Referencing an Element of an Associative Array

Live ORACLE: Referencing a Nested Table Element

Live ORACLE: Empty Varray Constructor

Live ORACLE: Combining Collection Declaration and Constructor

Live ORACLE: Collection Constructor Including Null Elements

Live ORACLE: Constructor for a Varray

Live ORACLE: Constructor for a Nested Table

Live ORACLE: NOT NULL Constraint on Collection Elements

Live ORACLE: VARRAY of Records

Live ORACLE: Specifying Collection Element Types with %TYPE and %ROWTYPE

Live ORACLE: Calling a Procedure With a Nested Table Parameter

Live ORACLE: Declaring a Procedure Parameter as a Nested Table

Live ORACLE: Declaring Collections with %TYPE

Live ORACLE: Declaring Nested Tables, Varrays, and Associative Arrays

Live ORACLE: Declaring an Associative Array

Live ORACLE: Declaring Collection Types

Live ORACLE: Using NULL as a Placeholder When Creating a Subprogram

Live ORACLE: Using the NULL Statement to Show No Action

Live ORACLE: Using a GOTO Statement to Branch an Enclosing Block

Live ORACLE: Using a NULL Statement to Allow a GOTO to a Label

Live ORACLE: Using a Simple GOTO Statement

Live ORACLE: Using EXIT With a Label in a LOOP

Live ORACLE: Using EXIT in a LOOP

Live ORACLE: Using Labels on Loops for Referencing

Live ORACLE: Using a Label for Referencing Variables Outside a Loop

Live ORACLE: Scope of the LOOP Counter Variable

Live ORACLE: Specifying a LOOP Range at Run Time

Live ORACLE: Changing the Increment of the Counter in a FOR..LOOP Statement

Live ORACLE: Using a Reverse FOR..LOOP Statement

Live ORACLE: Using a Simple FOR..LOOP Statement

Live ORACLE: Using EXIT With Labeled Loops

Live ORACLE: Using an EXIT Statement

Live ORACLE: Using the Searched CASE Statement

Live ORACLE: Using the CASE-WHEN Statement

Live ORACLE: Extended IF-THEN Statement

Live ORACLE: Using the IF-THEN-ELSIF Statement

Live ORACLE: Nested IF Statements

Live ORACLE: Using a Simple IF-THEN-ELSE Statement

Live ORACLE: Using a Simple IF-THEN Statement

Live ORACLE: Using the Function RTRIM

Live ORACLE: Comparing Character Values

Live ORACLE: Implicit Conversion

Live ORACLE: Using SUBTYPE With %TYPE and %ROWTYPE

Live ORACLE: Default Value With Subtypes

Live ORACLE: Constraints Inherited by Subprograms

Live ORACLE: Type Compatibility With the NUMBER Datatype

Live ORACLE: Using Ranges With Subtypes

Live ORACLE: Assigning Literals to an INTERVAL DAY TO SECOND Variable

Live ORACLE: Assigning Literals to an INTERVAL YEAR TO MONTH Variable

Live ORACLE: Assigning a Literal Value to a TIMESTAMP WITH LOCAL TIME ZONE

Live ORACLE: Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable

Live ORACLE: Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions

Live ORACLE: Assigning a Literal Value to a TIMESTAMP Variable

Live ORACLE: Using the Function REPLACE

Live ORACLE: Using the Function NVL

Live ORACLE: Using the Function DECODE

Live ORACLE: Using NULLs in Comparisons

Live ORACLE: Using a Search Condition With a CASE Statement

Live ORACLE: Using the WHEN Clause With a CASE Statement

Live ORACLE: Using BOOLEAN Variables in Conditional Tests

Live ORACLE: Using Comparison Operators

Live ORACLE: Short-Circuit Evaluation

Live ORACLE: Assigning a Query Result to a Variable

Live ORACLE: Assigning BOOLEAN Values

Live ORACLE: Initialization of Variables and Constants

Live ORACLE: PL/SQL Block Using Multiple and Duplicate Labels

Live ORACLE: Replace letter from word by number

Live ORACLE: Using Subprogram Qualifier With Identifiers

Live ORACLE: Using a Label Qualifier With Identifiers

Live ORACLE: Scope Rules

Live ORACLE: Using a Subprogram Name for Name Resolution

Live ORACLE: Using a Block Label for Name Resolution

Live ORACLE: Using an Alias for Column Names

Live ORACLE: Assigning Values to a Record With a %ROWTYPE Declaration

Live ORACLE: Using the %ROWTYPE Attribute

Live ORACLE: Using %ROWTYPE With Table Rows

Live ORACLE: Using %TYPE With Table Columns

Live ORACLE: Using %TYPE With the Datatype of a Variable

Live ORACLE: Creating a Database Trigger

Live ORACLE: Techniques for Calling a Standalone Procedure From SQL*Plus

Live ORACLE: Creating a Stored Subprogram

Live ORACLE: Defining an Object Type

Live ORACLE: Declaring a Record Type

Live ORACLE: Using a PL/SQL Collection Type

Live ORACLE: Creating a Package and Package Body

Live ORACLE: Using the GOTO Statement

Live ORACLE: Using the EXIT-WHEN Statement

Live ORACLE: Using WHILE-LOOP for Control

Live ORACLE: Using the FOR-LOOP

Live ORACLE: Using the IF-THEN_ELSE and CASE Statement for Conditional Control

Live ORACLE: Using %ROWTYPE with an Explicit Cursor

Live ORACLE: Processing Query Results in a LOOP

Live ORACLE: Assigning Values to Variables as Parameters of a Subprogram

Live ORACLE: Assigning Values to Variables by SELECTing INTO

Live ORACLE: Assigning Values to Variables With the Assignment Operator

Live ORACLE: Substituting PL/SQL Variables

Live ORACLE: Using NEXT to Access Elements of a Nested Table

Live ORACLE: Using the IF-THEN-ELSIF Statement

Live ORACLE: Assigning Literals to an INTERVAL DAY TO SECOND Variable

03/ 27 pages

Live ORACLE: Using ROLLBACK

Live ORACLE: Using COMMIT With the WRITE Clause

Live ORACLE: Using a Cursor Expression

Live ORACLE: Declaration of Cursor Variables in a Package

Live ORACLE: Fetching from a Cursor Variable into Collections

Live ORACLE: Fetching from a Cursor Variable into a Record

Live ORACLE: Cursor Variable with Different Return Types

Live ORACLE: Stored Procedure to Open Ref Cursors with Different Queries

Live ORACLE: Stored Procedure to Open a Ref Cursor

Live ORACLE: Checking If a Cursor Variable is Open

Live ORACLE: Passing a REF CURSOR as a Parameter

Live ORACLE: Passing Parameters to Explicit Cursors

Live ORACLE: Passing Parameters to a Cursor FOR Loop

Live ORACLE: Using a Correlated Subquery

Live ORACLE: Using a Subquery in a FROM Clause

Live ORACLE: Using a Subquery in a Cursor

Live ORACLE: Using an Alias For Expressions in a Query

Live ORACLE: Using %ROWCOUNT

Live ORACLE: Using %NOTFOUND

Live ORACLE: Using %ISOPEN

Live ORACLE: Using %FOUND

Live ORACLE: Fetching Bulk Data With a Cursor

Live ORACLE: Fetching the Same Cursor Into Different Variables

Live ORACLE: Referencing PL/SQL Variables Within Its Scope

Live ORACLE: Fetching With a Cursor

Live ORACLE: Using SQL%ROWCOUNT

Live ORACLE: Using SQL%FOUND

04/ 2 pages

Live ORACLE: The day when most of employees were Hired

Live ORACLE: Oracle Magazine - May/June 2009

05/ 35 pages

Live ORACLE: The Employee's Period by years and months

Live ORACLE: Know the Current USER of your Session by SQL Statement

Live ORACLE: Aliasing from Passing Global Variable with NOCOPY Hint

Live ORACLE: Specifying Invoker's Rights With a Procedure

Live ORACLE: Resolving PL/SQL Functions With Inheritance

Live ORACLE: Resolving PL/SQL Procedure Names

Live ORACLE: Overloading a Subprogram Name

Live ORACLE: Procedure with Default Parameter Values

Live ORACLE: Using the OUT Mode

Live ORACLE: Subprogram Calls Using Positional, Named, and Mixed Notation

Live ORACLE: Formal Parameters and Actual Parameters

Live ORACLE: Forward Declaration for a Nested Subprogram

Live ORACLE: Simple PL/SQL Function

Live ORACLE: Simple PL/SQL Procedure

Live ORACLE: Dynamic SQL Fetching into a Record

Live ORACLE: Accessing %ROWCOUNT For an Explicit Cursor

Live ORACLE: Using Duplicate Placeholders With Dynamic SQL

Live ORACLE: Oracle Forms Tutorial (www.aboutoracleapps.com)

Live ORACLE: Dynamic SQL Inside FORALL Statement

Live ORACLE: Dynamic SQL with RETURNING BULK COLLECT INTO Clause

Live ORACLE: Dynamic SQL with BULK COLLECT INTO Clause

Live ORACLE: Dynamic SQL Procedure that Accepts Table Name and WHERE Clause

Live ORACLE: Calling an Autonomous Function

Live ORACLE: Using Autonomous Triggers

Live ORACLE: Declaring an Autonomous Trigger

Live ORACLE: Declaring an Autonomous PL/SQL Block

Live ORACLE: Declaring an Autonomous Standalone Procedure

Live ORACLE: Declaring an Autonomous Function in a Package

Live ORACLE: Using %ROWTYPE with Cursors reference ROWID

Live ORACLE: Fetching Across COMMITs Using ROWID

Live ORACLE: Fetching Across Commits

Live ORACLE: Using CURRENT OF to Update the Latest Row Fetched From a Cursor

Live ORACLE: Using SET TRANSACTION to Begin a Read-only Transaction

Live ORACLE: Reusing a SAVEPOINT With ROLLBACK

Live ORACLE: Using SAVEPOINT With ROLLBACK

06/ 3 pages

Live ORACLE: Oracle Reports Online Help (Help Contents)

Live ORACLE: Oracle Forms Online Help

Live ORACLE: Oracle Magazine - July/August 2009

07/ 2 pages

Live ORACLE: Delete Value from LOV after selecting it

09/ 1 pages

Live ORACLE: Oracle Magazine - September/October 2009

10/ 7 pages

Live ORACLE: Creating the emp_admin Package

Live ORACLE: Matching Package Specifications and Bodies

Live ORACLE: A Simple Package Specification Without a Body

Live ORACLE: Aliasing from Assigning Cursor Variables to Same Work Area

Live ORACLE: Aliasing Passing Same Parameter Multiple Times

Live ORACLE: SCROLL_VIEW Built-in

Live ORACLE: November/December 2009

11/ 47 pages

Live ORACLE: Using DEREF in a SELECT INTO Statement

Live ORACLE: Updating Rows in an Object Table With a REF Modifier

Live ORACLE: Updating and Deleting Rows in an Object Table

Live ORACLE: Inserting Rows in an Object Table

Live ORACLE: Accessing Object Attributes

Live ORACLE: Null Objects in a PL/SQL Block

Live ORACLE: Declaring Object Types in a PL/SQL Block

Live ORACLE: Site Map

Live ORACLE: Working With Object Types

Live ORACLE: Using a Pipelined Table Function as an Aggregate Function

Live ORACLE: Using Multiple REF CURSOR Input Variables

Live ORACLE: Using a Pipelined Table Function For a Transformation

Live ORACLE: Assigning the Result of a Table Function

Live ORACLE: Checking for PLSQL Compilation Type

Live ORACLE: Using NOCOPY With Parameters

Live ORACLE: SET_CANVAS_PROPERTY Built-in

Live ORACLE: Using FORALL With BULK COLLECT

Live ORACLE: Using BULK COLLECT With the RETURNING INTO Clause

Live ORACLE: Using LIMIT to Control the Number of Rows In a BULK COLLECT

Live ORACLE: Bulk-Fetching from a Cursor Into a Collection of Records

Live ORACLE: Bulk-Fetching from a Cursor Into One or More Collections

Live ORACLE: Using the Pseudocolumn ROWNUM to Limit Query Results

Live ORACLE: Retrieving Query Results With BULK COLLECT

Live ORACLE: Bulk Operation That Continues Despite Exceptions

Live ORACLE: Counting Rows Affected by FORALL With %BULK_ROWCOUNT

Live ORACLE: Using %BULK_ROWCOUNT With the FORALL Statement

Live ORACLE: Using Rollbacks With FORALL

Live ORACLE: Using FORALL with Non-Consecutive Index Values

Live ORACLE: Using FORALL with Part of a Collection

Live ORACLE: Issuing DELETE Statements in a Loop

Live ORACLE: Nesting a Query to Improve Performance

Live ORACLE: Using the DBMS_WARNING Package to Display Warnings

Live ORACLE: Controlling the Display of PL/SQL Warnings

Live ORACLE: Using a Locator Variable to Identify the Location of an Exception

Live ORACLE: Retrying a Transaction After an Exception

Live ORACLE: Continuing after an Exception Is Raised

Live ORACLE: Displaying SQLCODE and SQLERRM

Live ORACLE: How PL/SQL Exceptions Propagate

Live ORACLE: Reraising a PL/SQL Exception

Live ORACLE: Scope of an Exception

Live ORACLE: Using RAISE to Force a Pre-Defined Exception

Live ORACLE: Using RAISE to Force a User-Defined Exception

Live ORACLE: Raising an Application Error With raise_application_error

Live ORACLE: Scope of PL/SQL Exceptions

Live ORACLE: Managing Multiple Errors With a Single Exception Handler

Live ORACLE: Runtime Error Handling

Live ORACLE: Using PUT_LINE in the DBMS_OUTPUT Package

SET_CANVAS_PROPERTY Built-in

2009-11-08T06:02:00.006+02:00

Learn how to deal with SET_CANVAS_PROPERTY Built-in by little practical project ........ download it from here:

Using FORALL With BULK COLLECT

2009-11-04T21:24:00.001+02:00

DROP TABLE emp_temp CASCADE CONSTRAINTS PURGE;

CREATE TABLE emp_temp
 AS
 SELECT *
 FROM   employees;

DECLARE
 TYPE numlist IS TABLE OF NUMBER;
 depts  NUMLIST := Numlist(10,20,30);
 TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
 TYPE dept_t IS TABLE OF employees.department_id%TYPE;
 e_ids  ENUM_T;
 d_ids  DEPT_T;
BEGIN
 FORALL j IN depts.FIRST..depts.LAST
   DELETE FROM emp_temp
   WHERE       department_id = Depts(j)
   RETURNING employee_id,department_id BULK COLLECT INTO e_ids,d_ids;
 dbms_output.Put_line('Deleted '
                      ||SQL%ROWCOUNT
                      ||' rows:');
 FOR i IN e_ids.FIRST.. e_ids.LAST LOOP
   dbms_output.Put_line('Employee #'
                        ||E_ids(i)
                        ||' from dept #'
                        ||D_ids(i));
 END LOOP;
END;
/

OUTPUT:-

Deleted 9 rows:
Employee #200 from dept #10
Employee #201 from dept #20
Employee #202 from dept #20
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30

PL/SQL procedure successfully completed.

Using BULK COLLECT With the RETURNING INTO Clause

2009-11-04T21:19:00.002+02:00

DROP TABLE emp_temp CASCADE CONSTRAINTS PURGE;

CREATE TABLE emp_temp
 AS
 SELECT *
 FROM   employees;

DECLARE
 TYPE numlist IS TABLE OF employees.employee_id%TYPE;
 enums  NUMLIST;
 TYPE namelist IS TABLE OF employees.last_name%TYPE;
 names  NAMELIST;
BEGIN
 DELETE FROM emp_temp
 WHERE       department_id = 30
 RETURNING employee_id,last_name BULK COLLECT INTO enums,names;
 dbms_output.Put_line('Deleted '
                      ||SQL%ROWCOUNT
                      ||' rows:');
 FOR i IN enums.FIRST.. enums.LAST LOOP
   dbms_output.Put_line('Employee #'
                        ||Enums(i)
                        ||': '
                        ||Names(i));
 END LOOP;
END;
/

OUTPUT:-

Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares

PL/SQL procedure successfully completed.

Using LIMIT to Control the Number of Rows In a BULK COLLECT

2009-11-04T21:17:00.001+02:00

DECLARE
 TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 CURSOR c1 IS
   SELECT employee_id
   FROM   employees
   WHERE  department_id = 80;
 empids  NUMTAB;
 ROWS    PLS_INTEGER := 10;
BEGIN
 OPEN c1;
 LOOP -- the following statement fetches 10 rows or less in each iteration
   FETCH c1 BULK COLLECT INTO empids LIMIT ROWS;
   EXIT WHEN empids.COUNT = 0;
   -- EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data
   dbms_output.Put_line('------- Results from Each Bulk Fetch --------');
   FOR i IN 1.. empids.COUNT LOOP
     dbms_output.Put_line('Employee Id: '
                          ||Empids(i));
   END LOOP;
 END LOOP;
 CLOSE c1;
END;
/

OUTPUT:-

------- Results from Each Bulk Fetch --------
Employee Id: 145
Employee Id: 146
Employee Id: 147
Employee Id: 148
Employee Id: 149
Employee Id: 150
Employee Id: 151
Employee Id: 152
Employee Id: 153
Employee Id: 154
------- Results from Each Bulk Fetch --------
Employee Id: 155
Employee Id: 156
Employee Id: 157
Employee Id: 158
Employee Id: 159
Employee Id: 160
Employee Id: 161
Employee Id: 162
Employee Id: 163
Employee Id: 164
------- Results from Each Bulk Fetch --------
Employee Id: 165
Employee Id: 166
Employee Id: 167
Employee Id: 168
Employee Id: 169
Employee Id: 170
Employee Id: 171
Employee Id: 172
Employee Id: 173
Employee Id: 174
------- Results from Each Bulk Fetch --------
Employee Id: 175
Employee Id: 176
Employee Id: 177
Employee Id: 179

PL/SQL procedure successfully completed.

Bulk-Fetching from a Cursor Into a Collection of Records

2009-11-04T21:15:00.000+02:00

DECLARE
 TYPE deptrectab IS TABLE OF departments%ROWTYPE;
 dept_recs  DEPTRECTAB;
 CURSOR c1 IS
   SELECT department_id,
          department_name,
          manager_id,
          location_id
   FROM   departments
   WHERE  department_id > 70;
BEGIN
 OPEN c1;
 FETCH c1 BULK COLLECT INTO dept_recs;
END;
/

Bulk-Fetching from a Cursor Into One or More Collections

2009-11-04T21:09:00.001+02:00

DECLARE
 TYPE namelist IS TABLE OF employees.last_name%TYPE;
 TYPE sallist IS TABLE OF employees.salary%TYPE;
 CURSOR c1 IS
   SELECT last_name,
          salary
   FROM   employees
   WHERE  salary > 10000;
 names    NAMELIST;
 sals     SALLIST;
 TYPE reclist IS TABLE OF c1%ROWTYPE;
 recs     RECLIST;
 v_limit  PLS_INTEGER := 10;
 PROCEDURE Print_results
 IS
 BEGIN
   IF names IS NULL
       OR names.COUNT = 0 THEN -- check if collections are empty
  
     dbms_output.Put_line('No results!');
   ELSE
     dbms_output.Put_line('Results: ');
     FOR i IN names.FIRST.. names.LAST LOOP
       dbms_output.Put_line(' Employee '
                            ||Names(i)
                            ||': $'
                            ||Sals(i));
     END LOOP;
   END IF;
 END;
BEGIN
 dbms_output.Put_line('--- Processing all results at once ---');
 OPEN c1;
 FETCH c1 BULK COLLECT INTO names,sals;
 CLOSE c1;
 Print_results();
 dbms_output.Put_line('--- Processing '
                      ||v_limit
                      ||' rows at a time ---');
 OPEN c1;
 LOOP
   FETCH c1 BULK COLLECT INTO names,sals LIMIT v_limit;
   EXIT WHEN names.COUNT = 0;
   Print_results();
 END LOOP;
 CLOSE c1;
 dbms_output.Put_line('--- Fetching records rather than columns ---');
 OPEN c1;
 FETCH c1 BULK COLLECT INTO recs;
 FOR i IN recs.FIRST.. recs.LAST LOOP
   -- Now all the columns from the result set come from a single record
   dbms_output.Put_line(' Employee '
                        ||Recs(i).last_name
                        ||': $'
                        ||Recs(i).salary);
 END LOOP;
END;
/

OUTPUT:-

--- Processing all results at once ---
Results:
Employee Hartstein: $13000
Employee Higgins: $12000
Employee King: $24000
Employee Kochhar: $17000
Employee De Haan: $17000
Employee Greenberg: $12000
Employee Raphaely: $11000
Employee Russell: $14000
Employee Partners: $13500
Employee Errazuriz: $12000
Employee Cambrault: $11000
Employee Zlotkey: $10500
Employee Vishney: $10500
Employee Ozer: $11500
Employee Abel: $11000
--- Processing 10 rows at a time ---
Results:
Employee Hartstein: $13000
Employee Higgins: $12000
Employee King: $24000
Employee Kochhar: $17000
Employee De Haan: $17000
Employee Greenberg: $12000
Employee Raphaely: $11000
Employee Russell: $14000
Employee Partners: $13500
Employee Errazuriz: $12000
Results:
Employee Cambrault: $11000
Employee Zlotkey: $10500
Employee Vishney: $10500
Employee Ozer: $11500
Employee Abel: $11000
--- Fetching records rather than columns ---
Employee Hartstein: $13000
Employee Higgins: $12000
Employee King: $24000
Employee Kochhar: $17000
Employee De Haan: $17000
Employee Greenberg: $12000
Employee Raphaely: $11000
Employee Russell: $14000
Employee Partners: $13500
Employee Errazuriz: $12000
Employee Cambrault: $11000
Employee Zlotkey: $10500
Employee Vishney: $10500
Employee Ozer: $11500
Employee Abel: $11000

PL/SQL procedure successfully completed.

Using the Pseudocolumn ROWNUM to Limit Query Results

2009-11-04T21:02:00.000+02:00

DECLARE
 TYPE sallist IS TABLE OF employees.salary%TYPE;
 sals  SALLIST;
BEGIN
 -- Limit the number of rows to 50
 SELECT salary
 BULK COLLECT INTO sals
 FROM   employees
 WHERE  ROWNUM <= 50;
 -- Retrieve 10% (approximately) of the rows in the table
 SELECT salary
 BULK COLLECT INTO sals
 FROM   employees SAMPLE ( 10 );
END;
/

Retrieving Query Results With BULK COLLECT

2009-11-04T20:46:00.001+02:00

DECLARE
 TYPE numtab IS TABLE OF employees.employee_id%TYPE;
 TYPE nametab IS TABLE OF employees.last_name%TYPE;
 enums  NUMTAB; -- No need to initialize the collections.
 names  NAMETAB; -- Values will be filled in by the SELECT INTO.
 PROCEDURE Print_results
 IS
 BEGIN
   IF enums.COUNT = 0 THEN
     dbms_output.Put_line('No results!');
   ELSE
     dbms_output.Put_line('Results:');
     FOR i IN enums.FIRST.. enums.LAST LOOP
       dbms_output.Put_line(' Employee #'
                            ||Enums(i)
                            ||': '
                            ||Names(i));
     END LOOP;
   END IF;
 END;
BEGIN
 -- Retrieve data for employees with Ids greater than 1000
 SELECT employee_id,
        last_name
 BULK COLLECT INTO enums,names
 FROM   employees
 WHERE  employee_id > 1000;
 -- The data has all been brought into memory by BULK COLLECT
 -- No need to FETCH each row from the result set
 Print_results();
 -- Retrieve approximately 20% of all rows
 SELECT employee_id,
        last_name
 BULK COLLECT INTO enums,names
 FROM   employees SAMPLE ( 20 );
 Print_results();
END;
/

OUTPUT:-

No results!
Results:
Employee #200: Whalen
Employee #206: Gietz
Employee #100: King
Employee #104: Ernst
Employee #107: Lorentz
Employee #126: Mikkilineni
Employee #127: Landry
Employee #130: Atkinson
Employee #135: Gee
Employee #156: King
Employee #158: McEwen
Employee #163: Greene
Employee #168: Ozer
Employee #169: Bloom
Employee #170: Fox
Employee #171: Smith
Employee #173: Kumar
Employee #184: Sarchand
Employee #186: Dellinger
Employee #189: Dilly
Employee #193: Everett

PL/SQL procedure successfully completed.

Bulk Operation That Continues Despite Exceptions

2009-11-04T20:39:00.001+02:00

DROP TABLE emp_temp CASCADE CONSTRAINTS PURGE;

-- create a temporary table for this example

CREATE TABLE emp_temp
 AS
 SELECT *
 FROM   employees;



DECLARE
 TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
 emp_sr  EMPID_TAB;
 -- create an exception handler for ORA-24381
 errors  NUMBER;
 dml_errors  EXCEPTION;
 PRAGMA EXCEPTION_INIT(dml_errors,-24381);
BEGIN
 SELECT employee_id
 BULK COLLECT INTO emp_sr
 FROM   emp_temp
 WHERE  hire_date < '30-DEC-94';
 -- add '_SR' to the job_id of the most senior employees
 FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
   UPDATE emp_temp
   SET    job_id = job_id
                   ||'_SR'
   WHERE  Emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
 WHEN dml_errors THEN -- Now we figure out what failed and why.

   errors := SQL%BULK_EXCEPTIONS.COUNT;
   dbms_output.Put_line('Number of statements that failed: '
                        ||errors);
   FOR i IN 1.. errors LOOP
     dbms_output.Put_line('Error #'
                          ||i
                          ||' occurred during '
                          ||'iteration #'
                          ||SQL%Bulk_exceptions(i).ERROR_INDEX);
     dbms_output.Put_line('Error message is '
                          ||Sqlerrm(-SQL%Bulk_exceptions(i).ERROR_CODE));
   END LOOP;
END;
/

OUTPUT:-

Number of statements that failed: 2
Error #1 occurred during iteration #5
Error message is ORA-12899: value too large for column  (actual: , maximum: )
Error #2 occurred during iteration #12
Error message is ORA-12899: value too large for column  (actual: , maximum: )

PL/SQL procedure successfully completed.

Counting Rows Affected by FORALL With %BULK_ROWCOUNT

2009-11-04T20:37:00.000+02:00

DROP TABLE emp_by_dept CASCADE CONSTRAINTS PURGE;


CREATE TABLE emp_by_dept
 AS
 SELECT employee_id,
        department_id
 FROM   employees
 WHERE  1 = 0;



DECLARE
 TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
 deptnums  DEPT_TAB;
BEGIN
 SELECT department_id
 BULK COLLECT INTO deptnums
 FROM   departments;
 FORALL i IN 1..deptnums.COUNT
   INSERT INTO emp_by_dept
   SELECT employee_id,
          department_id
   FROM   employees
   WHERE  department_id = Deptnums(i);
 FOR i IN 1.. deptnums.COUNT LOOP
   -- Count how many rows were inserted for each department; that is,
   -- how many employees are in each department.
   dbms_output.Put_line('Dept '
                        ||Deptnums(i)
                        ||': inserted '
                        ||SQL%Bulk_rowcount(i)
                        ||' records');
 END LOOP;
 dbms_output.Put_line('Total records inserted: '
                      ||SQL%ROWCOUNT);
END;
/

OUTPUT:-

Dept 10: inserted 1 records
Dept 20: inserted 2 records
Dept 30: inserted 6 records
Dept 40: inserted 1 records
Dept 50: inserted 45 records
Dept 60: inserted 5 records
Dept 70: inserted 1 records
Dept 80: inserted 34 records
Dept 90: inserted 3 records
Dept 100: inserted 6 records
Dept 110: inserted 2 records
Dept 120: inserted 0 records
Dept 130: inserted 0 records
Dept 140: inserted 0 records
Dept 150: inserted 0 records
Dept 160: inserted 0 records
Dept 170: inserted 0 records
Dept 180: inserted 0 records
Dept 190: inserted 0 records
Dept 200: inserted 0 records
Dept 210: inserted 0 records
Dept 220: inserted 0 records
Dept 230: inserted 0 records
Dept 240: inserted 0 records
Dept 250: inserted 0 records
Dept 260: inserted 0 records
Dept 270: inserted 0 records
Total records inserted: 106

PL/SQL procedure successfully completed.

Using %BULK_ROWCOUNT With the FORALL Statement

2009-11-04T20:34:00.001+02:00

DROP TABLE emp_temp CASCADE CONSTRAINTS PURGE;


CREATE TABLE emp_temp
 AS
 SELECT *
 FROM   employees;



DECLARE
 TYPE numlist IS TABLE OF NUMBER;
 depts  NUMLIST := Numlist(30,50,60);
BEGIN
 FORALL j IN depts.FIRST..depts.LAST
   DELETE FROM emp_temp
   WHERE       department_id = Depts(j);
 -- How many rows were affected by each DELETE statement?
 FOR i IN depts.FIRST.. depts.LAST LOOP
   dbms_output.Put_line('Iteration #'
                        ||i
                        ||' deleted '
                        ||SQL%Bulk_rowcount(i)
                        ||' rows.');
 END LOOP;
END;
/

OUTPUT:-

Iteration #1 deleted 6 rows.
Iteration #2 deleted 45 rows.
Iteration #3 deleted 5 rows.

PL/SQL procedure successfully completed.

Using Rollbacks With FORALL

2009-11-04T20:30:00.001+02:00

DROP TABLE emp_temp CASCADE CONSTRAINTS PURGE;


CREATE TABLE emp_temp (
 deptno NUMBER(2),
 job    VARCHAR2(18));



DECLARE
 TYPE numlist IS TABLE OF NUMBER;
 depts  NUMLIST := Numlist(10,20,30);
BEGIN
 INSERT INTO emp_temp
 VALUES     (10,
             'Clerk');
 -- Lengthening this job title causes an exception
 INSERT INTO emp_temp
 VALUES     (20,
             'Bookkeeper');
 INSERT INTO emp_temp
 VALUES     (30,
             'Analyst');
 COMMIT;
 FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements.
   UPDATE emp_temp
   SET    job = job
                ||' (Senior)'
   WHERE  deptno = Depts(j);
-- raises a "value too large" exception
EXCEPTION
 WHEN OTHERS THEN
   dbms_output.Put_line('Problem in the FORALL statement.');
   COMMIT; -- Commit results of successful updates.

END;
/

OUTPUT:-

Problem in the FORALL statement.

PL/SQL procedure successfully completed.

Using FORALL with Non-Consecutive Index Values

2009-11-04T20:14:00.002+02:00

-- Create empty tables to hold order details
DROP TABLE valid_orders CASCADE CONSTRAINTS PURGE;

CREATE TABLE valid_orders (
cust_name VARCHAR2(32),
amount    NUMBER(10,2));

DROP TABLE big_orders CASCADE CONSTRAINTS PURGE;

CREATE TABLE big_orders
AS
SELECT *
FROM   valid_orders
WHERE  1 = 0;

DROP TABLE rejected_orders CASCADE CONSTRAINTS PURGE;

CREATE TABLE rejected_orders
AS
SELECT *
FROM   valid_orders
WHERE  1 = 0;

DECLARE
-- Make collections to hold a set of customer names and order amounts.
SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
TYPE cust_typ IS TABLE OF CUST_NAME;
cust_tab            CUST_TYP;
SUBTYPE order_amount IS valid_orders.amount%TYPE;
TYPE amount_typ IS TABLE OF NUMBER;
amount_tab          AMOUNT_TYP;
-- Make other collections to point into the CUST_TAB collection.
TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
big_order_tab       INDEX_POINTER_T := Index_pointer_t();
rejected_order_tab  INDEX_POINTER_T := Index_pointer_t();
PROCEDURE Setup_data
IS
BEGIN
  -- Set up sample order data, including some invalid orders and some 'big' orders.
  cust_tab := Cust_typ('Company1','Company2','Company3','Company4',
                       'Company5');
  amount_tab := Amount_typ(5000.01,0,150.25,4000.00,NULL);
END;
BEGIN
Setup_data();
dbms_output.Put_line('--- Original order data ---');
FOR i IN 1.. cust_tab.LAST LOOP
  dbms_output.Put_line('Customer #'
                       ||i
                       ||', '
                       ||Cust_tab(i)
                       ||': $'
                       ||Amount_tab(i));
END LOOP;
-- Delete invalid orders (where amount is null or 0).
FOR i IN 1.. cust_tab.LAST LOOP
  IF Amount_tab(i) IS NULL
      OR Amount_tab(i) = 0 THEN
    cust_tab.Delete(i);
    amount_tab.Delete(i);
  END IF;
END LOOP;
dbms_output.Put_line('--- Data with invalid orders deleted ---');
FOR i IN 1.. cust_tab.LAST LOOP
  IF cust_tab.Exists(i) THEN
    dbms_output.Put_line('Customer #'
                         ||i
                         ||', '
                         ||Cust_tab(i)
                         ||': $'
                         ||Amount_tab(i));
  END IF;
END LOOP;
-- Because the subscripts of the collections are not consecutive, use
-- FORALL...INDICES OF to iterate through the actual subscripts,
-- rather than 1..COUNT
FORALL i IN INDICES OF cust_tab
  INSERT INTO valid_orders
             (cust_name,
              amount)
  VALUES     (Cust_tab(i),
              Amount_tab(i));
-- Now process the order data differently
-- Extract 2 subsets and store each subset in a different table
Setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
FOR i IN cust_tab.FIRST.. cust_tab.LAST LOOP
  IF Amount_tab(i) IS NULL
      OR Amount_tab(i) = 0 THEN
    rejected_order_tab.extend; -- Add a new element to this collection
    -- Record the subscript from the original collection
    Rejected_order_tab(rejected_order_tab.LAST) := i;
  END IF;
  IF Amount_tab(i) > 2000 THEN
    big_order_tab.extend; -- Add a new element to this collection
    -- Record the subscript from the original collection
    Big_order_tab(big_order_tab.LAST) := i;
  END IF;
END LOOP;
-- Now it's easy to run one DML statement on one subset of elements,
-- and another DML statement on a different subset.
FORALL i IN VALUES OF rejected_order_tab
  INSERT INTO rejected_orders
  VALUES     (Cust_tab(i),
              Amount_tab(i));
FORALL i IN VALUES OF big_order_tab
  INSERT INTO big_orders
  VALUES     (Cust_tab(i),
              Amount_tab(i));
COMMIT;
END;
/
 

OUTPUT:-

--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000

PL/SQL procedure successfully completed.

 -- Verify that the correct order details were stored
SELECT cust_name "Customer",
       amount "Valid order amount"
FROM   valid_orders;
Customer                         Valid order amount
-------------------------------- ------------------
Company1                                    5000.01
Company3                                     150.25
Company4                                       4000

SELECT cust_name "Customer",
       amount "Big order amount"
FROM   big_orders;

Customer                         Big order amount
-------------------------------- ----------------
Company1                                  5000.01
Company4                                     4000

SELECT cust_name "Customer",
       amount "Rejected order amount"
FROM   rejected_orders;

Customer                         Rejected order amount
-------------------------------- ---------------------
Company2                                             0
Company5

Using FORALL with Part of a Collection

2009-11-04T20:11:00.000+02:00

DROP TABLE employees_temp CASCADE CONSTRAINTS PURGE;


CREATE TABLE employees_temp
 AS
 SELECT *
 FROM   employees;


DECLARE
 TYPE numlist IS VARRAY(10) OF NUMBER;
 depts  NUMLIST := Numlist(5,10,20,30,50,55,57,60,70,75);
BEGIN
 FORALL j IN 4..7 -- use only part of varray
   DELETE FROM employees_temp
   WHERE       department_id = Depts(j);
 COMMIT;
END;
/

Issuing DELETE Statements in a Loop

2009-11-04T19:44:00.001+02:00

DROP TABLE parts1 CASCADE CONSTRAINTS PURGE;

CREATE TABLE parts1 (
 pnum  INTEGER,
 pname VARCHAR2(15));


DROP TABLE parts2 CASCADE CONSTRAINTS PURGE;

CREATE TABLE parts2 (
 pnum  INTEGER,
 pname VARCHAR2(15));


DECLARE
 TYPE numtab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
 TYPE nametab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
 pnums   NUMTAB;
 pnames  NAMETAB;
 iterations  CONSTANT PLS_INTEGER := 500;
 t1      INTEGER;
 t2      INTEGER;
 t3      INTEGER;
BEGIN
 FOR j IN 1.. iterations LOOP -- load index-by tables
   Pnums(j) := j;
   Pnames(j) := 'Part No. '
                ||To_char(j);
 END LOOP;
 t1 := dbms_utility.get_time;
 FOR i IN 1.. iterations LOOP -- use FOR loop
   INSERT INTO parts1
   VALUES     (Pnums(i),
               Pnames(i));
 END LOOP;
 t2 := dbms_utility.get_time;
 FORALL i IN 1..iterations -- use FORALL statement
   INSERT INTO parts2
   VALUES     (Pnums(i),
               Pnames(i));
 t3 := dbms_utility.get_time;
 dbms_output.Put_line('Execution Time (secs)');
 dbms_output.Put_line('---------------------');
 dbms_output.Put_line('FOR loop: '
                      ||To_char((t2
                                   - t1)
                                  / 100));
 dbms_output.Put_line('FORALL: '
                      ||To_char((t3
                                   - t2)
                                  / 100));
 COMMIT;
END;
/

OUTPUT:-

Execution Time (secs)
---------------------
FOR loop: .03
FORALL: 0

PL/SQL procedure successfully completed.

Nesting a Query to Improve Performance

2009-11-04T19:36:00.002+02:00

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.

Using the DBMS_WARNING Package to Display Warnings

2009-11-04T19:16:00.003+02:00

-- 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.

Controlling the Display of PL/SQL Warnings

2009-11-04T19:09:00.003+02:00


-- 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';

Using a Locator Variable to Identify the Location of an Exception

2009-11-04T18:58:00.004+02:00

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.

Retrying a Transaction After an Exception

2009-11-04T18:50:00.001+02:00

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;
/

Continuing after an Exception Is Raised

2009-11-04T18:42:00.002+02:00

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;
/

Displaying SQLCODE and SQLERRM

2009-11-04T00:07:00.003+02:00

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.

How PL/SQL Exceptions Propagate

2009-11-01T23:15:00.002+02:00

Reraising a PL/SQL Exception

2009-11-01T22:51:00.001+02:00

DECLARE
 salary_too_high  EXCEPTION;
 current_salary    NUMBER := 20000;
 max_salary        NUMBER := 10000;
 erroneous_salary  NUMBER;
BEGIN
 BEGIN ---------- sub-block begins
   IF current_salary > max_salary THEN
     RAISE salary_too_high; -- raise the exception
   END IF;
 EXCEPTION
   WHEN salary_too_high THEN
     -- first step in handling the error
     dbms_output.Put_line('Salary '
                          ||erroneous_salary
                          ||' is out of range.');
     dbms_output.Put_line('Maximum salary is '
                          ||max_salary
                          ||'.');
     RAISE; -- reraise the current exception

 END; ------------ sub-block ends

EXCEPTION
 WHEN salary_too_high THEN
   -- handle the error more thoroughly
   erroneous_salary := current_salary;
   current_salary := max_salary;
   dbms_output.Put_line('Revising salary from '
                        ||erroneous_salary
                        ||' to '
                        ||current_salary
                        ||'.');
END;
/

OUTPUT:-

Salary  is out of range.
Maximum salary is 10000.
Revising salary from 20000 to 10000.

PL/SQL procedure successfully completed.