Oracle Nested Tables Example

http://www.joegarrepy.com/oracle_nestedtables.htm

/*
-- ****************************************************************** --
9/30/2003 NESTED TABLES Create Script (Joe Garrepy) VERSION 1.0
-- ****************************************************************** --
The following script creates several TYPE objects and one TABLE object
to illustrate the creation of NESTED tables. The script also shows
how to insert rows into the Regular table and also work with percision
on the nested tables (all DML statements). It also contains SQL code
to bring back values from the table.
-- ****************************************************************** --
*/
--> Create Name Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_NAME"
AS OBJECT
(FIRST VARCHAR2(60),
LAST VARCHAR2(60),
MIDDLE VARCHAR2(30),
PREFIX VARCHAR2(10),
SUFFIX VARCHAR2(10),
CREATE_DATE DATE,
CHANGE_DATE DATE,
CREATE_USER VARCHAR2(30),
CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_NAME"
AS TABLE OF ROW_NAME
/
--> Create Person Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_PERSON"
AS OBJECT
(SSN VARCHAR2(9),
GENDER VARCHAR2(1),
BDATE DATE,
ETHNICITY VARCHAR2(2),
MARITAL_STATUS VARCHAR2(2),
HAIR VARCHAR2(10),
EYES VARCHAR2(10),
HEIGHT VARCHAR2(10),
CREATE_DATE DATE,
CHANGE_DATE DATE,
CREATE_USER VARCHAR2(30),
CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_PERSON"
AS TABLE OF ROW_PERSON
/
--> Create Employment Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_EMPLOYMENT"
AS OBJECT
(JOB_TITLE VARCHAR2(60),
START_DATE DATE,
TERMINATED VARCHAR2(1),
END_DATE DATE,
SALARY NUMBER(8,4),
CREATE_DATE DATE,
CHANGE_DATE DATE,
CREATE_USER VARCHAR2(30),
CHANGE_USER VARCHAR2(30)
)
/
CREATE OR REPLACE TYPE "TAB_EMPLOYMENT"
AS TABLE OF ROW_EMPLOYMENT
/
--> Create table with UID and TAB_NAME & TAB_PERSON tables as columns
CREATE TABLE "ENTITY"
( "UIDN" NUMBER(10) NOT NULL,
"NAME" "TAB_NAME" DEFAULT "TAB_NAME"(),
"PERSON" "TAB_PERSON" DEFAULT "TAB_PERSON"(),
"EMPLOYMENT" "TAB_EMPLOYMENT" DEFAULT "TAB_EMPLOYMENT"(),
"DEAD" VARCHAR2(1) DEFAULT 'N',
"DEAD_DATE" DATE,
"CREATE_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"CHANGE_DATE" DATE,
"CREATE_USER" VARCHAR2(30) DEFAULT USER,
"CHANGE_USER" VARCHAR2(30),
PRIMARY KEY ("UIDN"))
NESTED TABLE "NAME" STORE AS "ENTITY_NAME"
NESTED TABLE "PERSON" STORE AS "ENTITY_PERSON"
NESTED TABLE "EMPLOYMENT" STORE AS "ENTITY_EMPLOYMENT"
/
COMMENT ON TABLE "ENTITY" IS 'Entity Base Table with repeating data as Nested Tables';
COMMENT ON COLUMN "ENTITY"."UIDN" IS 'Unique Identifaction Number/KEY';
COMMENT ON COLUMN "ENTITY"."NAME" IS 'Nested Table Storing Name Data for an entity';
COMMENT ON COLUMN "ENTITY"."PERSON" IS 'Nested Table Storing Person Data for an entity';
COMMENT ON COLUMN "ENTITY"."EMPLOYMENT" IS 'Nested Table Storing Employment related data for an entity';
COMMENT ON COLUMN "ENTITY"."DEAD" IS 'Y if entity is dead else N';
COMMENT ON COLUMN "ENTITY"."DEAD_DATE" IS 'Date of Death';
--> Now lets insert some records
INSERT INTO entity VALUES
(1,TAB_NAME(ROW_NAME('Orion','Pax',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL),
ROW_NAME('Optimus','Prime',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
TAB_PERSON(ROW_PERSON('000000000','M','01-JAN-1985','AB',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
TAB_EMPLOYMENT(ROW_EMPLOYMENT('Autobot Leader','01-JAN-1985',NULL,NULL,1000,SYSDATE,NULL,USER,NULL)),
'N',NULL,SYSDATE,NULL,USER,NULL);
INSERT INTO entity VALUES
(2,TAB_NAME(ROW_NAME('Megatron',NULL,NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
TAB_PERSON(ROW_PERSON('000000001','M','01-JAN-1985','DC',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
TAB_EMPLOYMENT(ROW_EMPLOYMENT('Decepticon Leader','01-JAN-1985',NULL,NULL,995,SYSDATE,NULL,USER,NULL)),
'N',NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
--> View what whe have inserted by flatening out the column collections
SELECT src.uidn,
n.*,
p.*,
e.*,
dead,
dead_date,
src.create_date,
src.change_date,
src.create_user,
src.change_user
FROM ENTITY src,
TABLE(src.name) n,
TABLE(src.person) p,
TABLE(src.employment) e;
--> Insert specific records into column of collections
/* Megatron's name changed during Transformers the Movie so lets insert
a new record into the collection for Names.
The first thing that needs to be done is to get the base record from
the entity and the column with the collection that we want to update
table we do so with this statement
INSERT INTO
TABLE(select entity.name
from entity
where entity.uidn = 2)
The next part of the insert involves adding a record to the collection
column
('Galvatron',NULL,NULL,NULL,
NULL,SYSDATE,NULL,USER,NULL)
*/
INSERT INTO
TABLE(select entity.name
from entity
where entity.uidn = 2)
VALUES
('Galvatron',NULL,NULL,NULL,
NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
--> Update specific records inside of column collections
/* We'll use a similiar method to update Megatrons Galvatron's
NAME record to have a last name
*/
UPDATE TABLE(select entity.name
from entity
where entity.uidn = 2)
SET last = 'Decepticon',
change_date = SYSDATE,
change_user = USER
WHERE first = 'Galvatron';
COMMIT;
--> Delete specific records inside of column collections
/* Eventually Galvatron's name returns to being Megatron,
so lets delete out the Galvatron record from the NAME
collection
*/
DELETE TABLE(select entity.name
from entity
where entity.uidn = 2)
WHERE first = 'Galvatron';
COMMIT;

Oracle Pipelined table functions

http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

http://www.joegarrepy.com/pipelined_function.htm
http://www.akadia.com/services/ora_pipe_functions.html

Overview Basically, when you would like a PLSQL (or java or c) routine to be the «source» of data -- instead of a table -- you would use a pipelined function. PIPELINED functions will operate like a table. A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 9, large transformations required either significant memory overhead, or storing the data in intermediate tables between each stage of the transformation. The loading process caused immense performance degradations in both cases.

Using PL/SQL table functions can significantly lower the over-head of doing such transformations. PL/SQL table functions accept and return multiple rows, delivering them as they are ready rather than all at once, and can be made to execute as parallel operations. Simple Example - Generating Some Random Data How could you create six unique random numbers between 1 and 49 with one SQL statement? We would generate the set of numbers to pick from (see the innermost query that follows); any table with 49 or more records would do it. First the quick-and-dirty solution without a pipelined function. select r   from (select r            from (select rownum r                    from all_objects                   where rownum < 50)           order by dbms_random.value)   where rownum <= 6;          R ----------         10          2         19         34         12         21 That query works by generating the numbers 1 .. 49, using the inline view. We wrap that innermost query as an inline view and sort it by a random value, using DBMS_RANDOM.VALUE. We wrap that result set in yet another inline view and just take the first six rows. If we run that query over and over, we'll get a different set of six rows each time. This sort of question comes up frequently—maybe not about how to generate a set of six random numbers but rather, "how can we get N rows?" For example, we'd like the inclusive set of all dates between 25-FEB-2004 and 10-MAR-2004. The question becomes how to do this without a "real" table, and the answer lies in Oracle9i/10g with its PIPELINED function capability. We can write a PL/SQL function that will operate like a table. We need to start with a SQL collection type; this describes what the PIPELINED function will return. In this case, we are choosing a table of numbers; the virtual table we are creating will simply return the numbers 1, 2, 3, ... N: create type array     as table of number / Type created. Next, we create the actual PIPELINED function. This function will accept an input to limit the number of rows returned. If no input is provided, this function will just keep generating rows for a very long time (so be careful and make sure to use ROWNUM or some other limit in the query itself!). The PIPELINED keyword on line 4 allows this function to work as if it were a table: create function   gen_numbers(n in number default null)   return array   PIPELINED   as   begin      for i in 1 .. nvl(n,999999999)      loop          pipe row(i);      end loop;      return;   end; / Function created. Suppose we needed three rows for something. We can now do that in one of two ways: select * from TABLE(gen_numbers(3));  COLUMN_VALUE  ------------            1            2            3 or select * from TABLE(gen_numbers)  where rownum <= 3;  COLUMN_VALUE  ------------            1            2            3 Now we are ready to re-answer the original question, using the following functionality: select *   from (   select *     from (select * from table(gen_numbers(49)))   order by dbms_random.random   ) where rownum <= 6 /  COLUMN_VALUE  ------------           47           42           40           15           48           23 We can use this virtual table functionality for many things, such as generating that range of dates: select to_date('25-feb-2004')+         column_value-1   from TABLE(gen_numbers(15)) / TO_DATE(' --------- 25-FEB-04 26-FEB-04 27-FEB-04 28-FEB-04 29-FEB-04 01-MAR-04 02-MAR-04 03-MAR-04 04-MAR-04 05-MAR-04 06-MAR-04 07-MAR-04 08-MAR-04 09-MAR-04 10-MAR-04 Note the name of the column we used: COLUMN_VALUE. That is the default name for the column coming back from the PIPELINED function. Typical Pipelined Example This are the typical steps to perform when using PL/SQL Table Functions: The producer function must use the PIPELINED keyword in its declaration.   The producer function must use an OUT parameter that is a record, corresponding to a row in the result set.   Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword.   The producer function must end with a RETURN statement that does not specify any return value.   The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table. The first step is to define the format of the rows that are going to be returned. In this case here, we're going to return a INT, DATE followed by a VARCHAR2(25). CREATE OR REPLACE TYPE myObjectFormat AS OBJECT ( A INT, B DATE, C VARCHAR2(25) ) / Next a collection type for the type previously defined must be created. CREATE OR REPLACE TYPE myTableType    AS TABLE OF myObjectFormat / Finally, the producer function is packaged in a package. It is a pipelined function as indicated by the keyword pipelined. CREATE OR REPLACE PACKAGE myDemoPack AS       FUNCTION prodFunc RETURN myTableType PIPELINED; END; / CREATE OR REPLACE PACKAGE BODY myDemoPack AS FUNCTION prodFunc RETURN myTableType PIPELINED IS BEGIN   FOR i in 1 .. 5     LOOP       PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));     END LOOP;     RETURN;   END; END; / Test It: ALTER SESSION SET NLS_DATE_FORMAT='dd.mm.yyyy'; SELECT * FROM TABLE(myDemoPack.prodFunc());          A B          C ---------- ---------- ---------          1 31.05.2004 Row 1          2 01.06.2004 Row 2          3 02.06.2004 Row 3          4 03.06.2004 Row 4          5 04.06.2004 Row 5 Conclusion Pipelined functions are useful if there is a need for a data source other than a table in a select statement.

SEMI-Joins & Anti-Joins in oracle

http://www.dbspecialists.com/files/presentations/semijoins.html

Search condition Case Statement

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_salary EMPLOYEES.SALARY%TYPE;
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
v_msg VARCHAR(20);
BEGIN
SELECT salary INTO n_salary FROM employees WHERE employee_id = n_emp_id; CASE
WHEN n_salary < 2000 THEN
v_msg := 'Low';
WHEN n_salary >= 2000 AND n_salary <=3000
THEN
v_msg := 'Fair';
WHEN n_salary >= 3000 THEN
v_msg := 'High';
END CASE;

DBMS_OUTPUT.PUT_LINE(v_msg);

END;
/

Update PK - Deferrable constraints

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_12.shtml

Oracle Constraints - All in one page details
http://psoug.org/reference/constraints.html

Deferred Integrity Constraints in Oracle8

by Jeff Hunter, Sr. Database Administrator

Introduction

Up until the release of Oracle8, all integrity constraints in the database where considered immediate constraints. With an immediate constraint, if a user enters data that violates a constraint, the statement is rolled back and Oracle signals an error. With the introduction of Oracle8, users now have the option of selecting between immediate and a new option called deferred itegrity constraints.
Deferred Integrity Constraints
Deferred integrity constraints are those that are not enforced until the user attempts to commit the transaction. At the time of the commit, if any data entered during the transaction violates an integrity constraint, Oracle will then rollback the entire transaction and signal an error to the user.
Why use deferrable integrity constraints?
One use is pure laziness. In Oracle7, users had no way of entering data into a child table before entering the matching data into the parent table. With Oracle8, users now have the option of inserting data into a child table before insert its matching data into the parent table by simply deferring the foreign-key constraint.

Another feature is speed. Anyone that has ever dealt with inserting large amounts of data into a table, like found in a data warehousing environment, know of the cost loading (inserting) data into tables. Deferring the integrity checking until after the data has loaded can save considerable time when dealing with large volumes of data being inserted into tables.

Solving design issues. Take for example the two tables ORDER and ORDER LINE. In this model, we cannot have an ORDER LINE unless we have an ORDER, but we also assert that all ORDERS must have ORDER LINES. This catch-22 issue can easily be resolved by deferring all integrity constraints until the time of the commit.
Warnings
Deferred integrity constraints should only be used when absolutely necessary. If at all possible, avoid hard-coding constraint names with your application code. Deferring integrity constraints should only be used when solving a design issue. Be aware that very few applications support commit time errors.
Statements
To defer ALL constraints that are deferrable during the entire session, you can use the alter session set constraints=deferred statement.

A user can defer a named or all constraints that are deferrable for a specific transaction using the set constraint name deferred or set constraint all deferred.
The user process may also set constraints for immediate enforcement for a specific transaction using the set constraint name immediate or set constraint all immediate.
Are all constraints deferrable?
Not all constraints in the database can be set to deferrable. Only when the constraint is created with the deferrable option can user processes set the constraint as deferrable. If the constraint is created with the not deferrable option (the default), then user processes will always be bound by immediate integrity constraint.
Default behavior of constraints
Along with defining whether a constraint is deferrable or not, you can also set its default behavior with the initially deferred and initially immediate statement. The initially deferred option will enable the constraint as deferrable by default whereas the initially immediate option requires the user process to explicitly set the transaction or session to deferrable.
NOTE: The initially deferred and not deferrable keyword are mutually exclusive.


New columns in the CONSTRAINTS view
There are new columns in the "USER_CONSTRAINTS/DBA_CONSTRAINTS/ALL_CONSTRAINTS" views that will give you information on whether the constraint is deferrable. For example:

SELECT
constraint_name
, deferrable
, deferred
FROM user_constraints
WHERE constraint_name like 'EMP%';

CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------ -------------- ---------
EMP_PRIMARY_KEY NOT DEFERRABLE IMMEDIATE
EMP_SELF_KEY NOT DEFERRABLE IMMEDIATE
EMP_FOREIGN_KEY DEFERRABLE DEFERRED


Example

REM -- ---------------------
REM -- Create example tables
REM -- ---------------------

DROP TABLE emp;
DROP TABLE dept;

CREATE TABLE dept (
deptno NUMBER(2) NOT NULL
, dname CHAR(14)
, loc CHAR(13)
, CONSTRAINT dept_pk PRIMARY KEY (deptno)
);

INSERT INTO dept VALUES (10,'FINANCE','PITTSBURGH');
INSERT INTO dept VALUES (20,'SALES','NEW YORK');
INSERT INTO dept VALUES (30,'OPERATIONS','BOSTON');

COMMIT;

CREATE TABLE emp (
empno NUMBER(4) NOT NULL
, ename CHAR(10)
, job CHAR(9)
, deptno NUMBER(2) NOT NULL
, CONSTRAINT emp_fk1 FOREIGN KEY (deptno)
REFERENCES dept (deptno)
DEFERRABLE
INITIALLY IMMEDIATE
, CONSTRAINT emp_pk PRIMARY KEY (empno)
);

INSERT INTO emp VALUES (1001, 'JEFF', 'PRESIDENT', 10);
INSERT INTO emp VALUES (1002, 'MELODY', 'MANAGER', 30);
INSERT INTO emp VALUES (1003, 'MARK', 'MANAGER', 10);
INSERT INTO emp VALUES (1004, 'MARTIN', 'MANAGER', 20);

COMMIT;

REM -- -------------------------------
REM -- Try to delete from parent table
REM -- -------------------------------

DELETE FROM dept WHERE deptno = 10;

ERROR at line 1:
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found

REM -- --------------------------------------------------------
REM -- Set this transaction to defer all deferrable constraints
REM -- --------------------------------------------------------

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM dept WHERE deptno = 10;

1 row deleted.

COMMIT;

ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found

Toms Discussion,
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:914629004506

Create XML Schema on the FLY

OTN Thread to create XML on the FLY

https://forums.oracle.com/forums/thread.jspa?threadID=426163


CREATE OR REPLACE
PROCEDURE GEN_XML_SCHEMA_OUTPUT
(p_schema_name VARCHAR2, p_table_name VARCHAR2) AS
CURSOR c_table_columns(c_table_name varchar2, c_schema_name varchar2) IS
SELECT column_name, data_type, data_default
FROM all_tab_columns
WHERE table_name = c_table_name
AND owner = c_schema_name;
l_col_str VARCHAR2(2000);
BEGIN

DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('<'>');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
FOR l_cur_table_columns IN c_table_columns(p_table_name, p_schema_name) LOOP
l_col_str := 'ELSIF l_cur_table_columns.data_type = 'NUMBER' THEN
l_col_str := l_col_str || 'xs:integer' || '"';
ELSIF l_cur_table_columns.data_type = 'DATE' THEN
l_col_str := l_col_str || 'xs:date' || '"';
END IF;

IF l_cur_table_columns.data_default IS NOT NULL THEN
l_col_str := l_col_str || ' default="' || TRIM(l_cur_table_columns.data_default) || '"';
END IF;
l_col_str := l_col_str || '/>';
DBMS_OUTPUT.PUT_LINE( l_col_str);
END LOOP;
DBMS_OUTPUT.PUT_LINE( '');
DBMS_OUTPUT.PUT_LINE( '');
DBMS_OUTPUT.PUT_LINE( '');
END;