Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

Writing SQL queries

</p> <p>Writing SQL queries<br />



Also see
How to debug PL/SQL code


Transform a subquery involving the IN clause to a Join?

Original Statement:
  SELECT  e.empno, e.ename
  FROM    emp e
  WHERE   e.deptno IN (
      SELECT  d.deptno
      FROM    dept d
      WHERE   d.loc = 'CHICAGO'
  );

New Statement:
  SELECT e.empno, e.ename
  FROM   emp e, dept d
  WHERE  d.loc = 'CHICAGO'
    AND  d.deptno = e.deptno;

Transform a statement involving an OR condition to a UNION ALL?


Original Statement:
  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'CHICAGO'
     OR   loc = 'NEW YORK';

New Statement:
  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'CHICAGO'
  UNION ALL
  SELECT  dname, loc
  FROM    dept
  WHERE   loc = 'NEW YORK';

Eliminating duplicate values in a table?

Provided below are four methods for identifying or removing duplicate rows from a table: 

Method 1:
---------
  DELETE FROM emp a
    WHERE rowid > (
      SELECT min(rowid)
      FROM   emp b
      WHERE  a.emp_id = b.emp_id
    );

Method 2:
---------
  CREATE TABLE emp2 AS
    SELECT distinct *
    FROM   emp;

  DROP TABLE emp;

  RENAME emp2 TO emp;

Method 3:
---------
  DELETE FROM emp
    WHERE rowid NOT IN (
      SELECT    MIN(rowid)
      FROM      emp
      GROUP BY  emp_id
    );

Method 4:
---------
  DELETE FROM emp a
    WHERE EXISTS (
      SELECT 'true'
      FROM   emp b
      WHERE  b.emp_id = a.emp_id
        AND  b.rowid < a.rowid
    );NOTE: If you where to create an index on the joined fields in the inner loop, it may be possible to eliminate N^2 operations as there would be no need to loop through the entire table on each pass be a record. 

Getting a count of the different data values in a column?

  SELECT dname
    , sum(decode(job, 'CLERK',     1, 0)) Clerk
    , sum(decode(job, 'SALESMAN',  1, 0)) Salesman
    , sum(decode(job, 'MANAGER',   1, 0)) Manager
    , sum(decode(job, 'ANALYST',   1, 0)) Analyst
    , sum(decode(job, 'PRESIDENT', 1, 0)) President
  FROM
      emp e
    , dept d
  WHERE
      e.deptno (+) = d.deptno
  GROUP BY
      dname;

  DNAME               CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
  -------------- ---------- ---------- ---------- ---------- ----------
  ACCOUNTING              1          0          1          0          1
  OPERATIONS              0          0          0          0          0
  RESEARCH                2          0          1          2          0
  SALES                   1          4          1          0          0

Getting count/sum RANGES of data values in a column?

A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).
  SELECT
      job
    , sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000"
    , sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000"
    , sum(decode(greatest(sal,0),    least(sal,999), 1, 0))  "Range 0-1000"
  FROM      emp
  GROUP BY  job;

  JOB       Range 3000-6000 Range 1000-3000 Range 0-1000
  --------- --------------- --------------- ------------
  ANALYST                 2               0            0
  CLERK                   0               2            2
  MANAGER                 0               3            0
  PRESIDENT               1               0            0
  SALESMAN                0               4            0

Retrieve only the Nth row from a table?


Method 1:
---------

SELECT ename, job, hiredate FROM emp WHERE rowid=
        (SELECT rowid FROM emp WHERE rownum <= 3
         MINUS
        SELECT rowid FROM emp WHERE rownum < 3
        );

Method 2:
---------
SELECT ename, job, hiredate FROM emp WHERE rownum = 1
    AND rowid NOT IN (SELECT rowid FROM emp WHERE  rownum < 3);

NOTE: Always remember that there is no explicit order in a relational database. 

Retrieve rows between N and M from a table?

  SELECT ename, job, hiredate FROM emp WHERE rowid in
      (
        SELECT rowid FROM emp WHERE rownum <= 7
        MINUS
        SELECT rowid FROM emp WHERE rownum < 3
      );

Retrieve EVERY Nth row from a table?


Method 1:
---------
SELECT ename, job, hiredate FROM emp WHERE (rowid,0) in
      (
        SELECT rowid, mod(rownum,4) FROM emp
      );

Method 2:
---------
Using Dynamic Views: (available in Oracle7.2 and higher)
SELECT ename, job, hiredate FROM (
             SELECT rownum rn, empno, ename, job, hiredate FROM   emp) d_table
             WHERE mod(d_table.rn,4) = 0;

Retrieve the TOP N Rows from a table?


Method 1:
---------
Starting with Oracle8i, you can have an inner-query with an ORDER BY clause
  SELECT
      ename
    , job
    , hiredate
    , sal
  FROM (  SELECT empno, ename, job, hiredate, sal
          FROM   emp
          ORDER BY sal DESC
       )
  WHERE rownum = ( SELECT count(distinct b.sal)
               FROM   emp b
               WHERE  b.sal >= a.sal
             )
  ORDER BY a.sal DESC

MATRIX Report using SQL?

  SELECT *
  FROM ( SELECT    job
                 , sum(decode(deptno, 10, sal)) DEPT10
                 , sum(decode(deptno, 20, sal)) DEPT20
                 , sum(decode(deptno, 30, sal)) DEPT30
                 , sum(decode(deptno, 40, sal)) DEPT40
         FROM      emp e
         GROUP BY  job
  )
  ORDER BY 1;

  JOB           DEPT10     DEPT20     DEPT30     DEPT40
  --------- ---------- ---------- ---------- ----------
  ANALYST                    6000
  CLERK           1300       1900        950
  MANAGER         2450       2975       2850
  PRESIDENT       5000
  SALESMAN                              5600

Posted in Uncategorized | Leave a comment

How to handle data in BULK

How to handle data in BULK


What Is a PL/SQL Collection ?

A collection is an ordered group of elements, all of the same type.

PL/SQL offers these collection types:

  • Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings
    for subscript values. (They are similar to hash tables in other programming languages.)
  • Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.
    You can define equivalent SQL types, allowing nested tables to be stored in database tables
    and manipulated through SQL.
  • Varrays (short for variable-size arrays) hold a fixed number of elements
    (although you can change the number of elements at runtime).
    They use sequential numbers as subscripts.

You can define collection types in a procedure, function, or package and pass collection variables as parameters,
to move data between client-side applications and stored subprograms.
To look up data that is more complex than single values,
you can store PL/SQL records or SQL object types in collections.
Nested tables and varrays can also be attributes of object types.

Nested Tables, Varrays and Associative arrays

Within the database, nested tables can be considered one-column database tables.
The size of a nested table can be increased dynamically.
You can delete elements from a nested table.

A varray stores elements in an order and allows to reference individual elements easily
using standard subscripting syntax.
A varray has a maximum size, which you must specify in its type definition.
Its index has a fixed lower bound of 1 and an extensible upper bound.

Associative arrays are sets of key-value pairs, where each key is unique
and is used to locate a corresponding value in the array.
The key can be an integer or a string.
Assigning a value using a key for the first time adds that key to the associative array.

 

Manipulating Collections

1) Defining Collection Types
To create collections, you define a collection type, then declare variables of that type.
You can define TABLE and VARRAY types in the declarative part of any PL/SQL block,
subprogram, or package. Collection type can be created as object in the database also.

declare
TYPE number_table is TABLE of number(10);
TYPE emptab is TABLE of emp%rowtype;
TYPE empsaltype is table of number index by varchar2(50);
TYPE int_array_type IS VARRAY(50) OF INTEGER;

As database object

SQL> create or replace type emptab as object(empno number, ename varchar2(50));

2) Initializing and Referencing Collections

To initialize a nested table or varray, you use a constructor, a system-defined function
with the same name as the collection type.
This function "constructs" collections from the elements passed to it.

declare
TYPE number_table is TABLE of number(10);
TYPE int_array_type IS VARRAY(50) OF INTEGER;
nt numbet_table := number_table();
int_arr int_array_type := int_array_type(null, null, null, null);

3) Using Collection Methods

The following collection methods help generalize code,
make collections easier to use, and make your applications easier to maintain:

        EXISTS
        COUNT
        LIMIT
        FIRST and LAST
        PRIOR and NEXT
        EXTEND
        TRIM
        DELETE
A collection method is a built-in function or procedure that operates
on collections and is called using dot notation. The syntax follows:
collection_name.method_name[(parameters)]

i)   EXISTS(n) returns TRUE if the nth element in a collection exists.
ii)  COUNT returns the number of elements that a collection currently contains
iii) For varrays, LIMIT returns the maximum number of elements that a varray can contain
iv)   FIRST and LAST return the first and last (smallest and largest) index numbers in a collection.
v)   To increase the size of a nested table or varray, use EXTEND. You cannot use EXTEND with index-by tables.
          This procedure has three forms:
                     EXTEND appends one null element to a collection.
                     EXTEND(n) appends n null elements to a collection.
                     EXTEND(n,i) appends n copies of the ith element to a collection. 

vi) TRIM decreasing the Size of a Collection (TRIM Method)
          This procedure has two forms:
                     TRIM removes one element from the end of a collection.
                     TRIM(n) removes n elements from the end of a collection. 

vii) Deleting Collection Elements (DELETE Method)
          This procedure has various forms:
                     DELETE removes all elements from a collection.
                     DELETE(n) removes the nth element from an associative array with a numeric key or a nested table.
                                        If the associative array has a string key, the element corresponding to the key value is deleted.
                                        If n is null, DELETE(n) does nothing.
                     DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
                                        If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Avoiding Collection Exceptions

In most cases, if you reference a nonexistent collection element, PL/SQL raises a predefined exception. Consider the following example:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   nums NumList;  -- atomically null
BEGIN
   /* Assume execution continues despite the raised exceptions. */
   nums(1) := 1;            -- raises COLLECTION_IS_NULL       (1)
   nums := NumList(1,2);  -- initialize table
   nums(NULL) := 3          -- raises VALUE_ERROR              (2)
   nums(0) := 3;            -- raises SUBSCRIPT_OUTSIDE_LIMIT  (3)
   nums(3) := 3;            -- raises SUBSCRIPT_BEYOND_COUNT   (4)
   nums.DELETE(1);  -- delete element 1
   IF nums(1) = 1 THEN ...  -- raises NO_DATA_FOUND            (5)

In the first case, the nested table is atomically null. In the second case,
the subscript is null. In the third case, the subscript is outside the legal range.
In the fourth case, the subscript exceeds the number of elements in the table.
In the fifth case, the subscript designates a deleted element.

The following list shows when a given exception is raised:

Collection Exception Raised when…
COLLECTION_IS_NULL you try to operate on an atomically null collection.
NO_DATA_FOUND a subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT a subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT a subscript is outside the allowed range.
VALUE_ERROR a subscript is null or not convertible to the key type.
This exception might occur if the key is defined as a PLS_INTEGER range,
and the subscript is outside this range.

In some cases, you can pass invalid subscripts to a method without raising an exception.
For instance, when you pass a null subscript to procedure DELETE, it does nothing.
Also, you can replace deleted elements without raising NO_DATA_FOUND, as the following example shows:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   nums NumList := NumList(10,20,30);  -- initialize table
BEGIN
   nums.DELETE(-1);  -- does not raise SUBSCRIPT_OUTSIDE_LIMIT
   nums.DELETE(3);   -- delete 3rd element
   dbms_output.put_line(nums.COUNT);  -- prints 2
   nums(3) := 30;    -- allowed; does not raise NO_DATA_FOUND
   dbms_output.put_line(nums.COUNT);  -- prints 3
END;

Reducing Loop Overhead for Collections with Bulk Binds

Collections can be used to bulk bind data values with SQL statement

BULK COLLECT

declare
TYPE emptab is TABLE of emp%rowtype;
et emptab;
n1 number;
BEGIN
select * BULK COLLECT INTO et from emp;
for n1 in et.FIRST..et.LAST LOOP
dbms_output.put_line(et(n1).ename);
END LOOP;
END;
/

A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding

BULK DML

declare
TYPE numtab is table of number;
type chartab is table of varchar2(200);
v_empno numtab;
v_ename chartab;
v_sal numtab;
n1 number;
BEGIN
select empno, ename, sal BULK COLLECT INTO v_empno,v_ename, v_sal from emp;
for n1 in v_sal.FIRST..v_sal.LAST LOOP
v_sal(n1)  := v_sal(n1)*1.1;
END LOOP;
forall n1 in v_empno.first..v_empno.last
update emp set sal=v_sal(n1) where empno=v_empno(n1);
END;
/

Using rowid for BULK update or delete
Accessing using rowid is the fastest method to fetch a record.
using rowid for bulk update is faster than using a primary key.

declare
TYPE numtab is table of number;
type chartab is table of varchar2(200);
type rowidtab is table of rowid;
v_empno numtab;
v_ename chartab;
v_sal numtab;
v_rid rowidtab;
n1 number;
BEGIN
select rowid, empno, ename, sal BULK COLLECT INTO v_rid, v_empno,v_ename, v_sal from emp;
v_sal(n1)  := v_sal(n1)*1.1;
END LOOP;
forall n1 in v_empno.first..v_empno.last
update emp set sal=v_sal(n1) where rowid=v_rid(n1);
END;
/

Upsert using values from external table

Task
       1) Read external table
       2) Check whether record exists in the database table CUSTOMER for the key value custno
       3) If records is not present then insert else update

declare
TYPE numtab is table of number;
type chartab is table of varchar2(200);
type rowidtab is table of rowid;
v_custno numtab;
v_custname chartab;
v_custtype numtab;
v_c1 numtab;
v_c2 chartab;
v_rid rowidtab;
n1 number;
nr number;
BEGIN
select rowid, custno, custname, custtype,b.c1, b.c2 BULK COLLECT INTO v_rid, v_custno,v_custname, v_custtype, v_c1, v_c2
from customer a, ext_table b where a.custno(+)=b.c1 order by 1 nulls first,2;
-- The data is ordered by riowid. Nulls values first
-- The first set of rows where rowid is null is uded for insert
nr:=0;
forall n1 in v_custno.first..v_custno.last LOOP
if v_rid(n1) is null then nr:=n1; end if;
END IF;
if nr>0 then
forall n1 in 1..nr
insert into customer(custno,custname) values(v_c1(n1), v_c2(n1));
end if;
if nr < v_rid.count then
forall n1 in nr+1..v_rid.count
update customer set custname=v_c2(n1) where rowid=v_rid(n1);
end if;
END;
/

GLOBAL TEMPORARY TABLES

In Oracle Specify GLOBAL TEMPORARY in the create table statemnt to indicate that the table is temporary.
Temporary tables are useful in applications where a result set is to be buffered,
perhaps because it is constructed by running multiple DML operations.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

 

For a global temporary table

  • The data in a temporary table is visible only to the session that inserts the data into the table.
    The LOCK statement has no effect on a temporary table, because each session has its own private data.
  • Space is allocated for the table segment at the time of the first DML operation on the table.
  • A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session.
    It does not truncate the data of other sessions that are using the same table.
  • The data you entered is lost when you rollback a transaction
  • DML statements on temporary tables do not generate redo logs for the data changes.
    However, undo logs for the data and redo logs for the undo logs are generated.
  • It is possible to create indexes, triggers and views based on temporary table
  • Oracle utilities can export and import the definition of a temporary table.
    However, no data rows are exported even if you use the ROWS clause.

For small amount of data rows global temporary table is an alternative for collections.
Indexes can be created on global temporary tables.

Continue reading »

Posted in Uncategorized | Leave a comment

Dealing with Constraints

Dealing with Constraints


Introduction

Three type of constraints are discussed here. ie Primary key, Unique and Referential
A Foreign key constraint has to refer to a Primary key constraint of another table
The constraint_type in user_constraints table has value P for Primary key, U for Unique and R for Referential

The following code works for constraints having 4 or less number of columns

Display All Constrainst and Relations

Master table and child tables
-----------------------------
select a.TABLE_NAME||'('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name))||','||
max(decode(c.position,5,c.column_name))||','||
max(decode(c.position,6,c.column_name))||','||
max(decode(c.position,7,c.column_name))||','||
max(decode(c.position,8,c.column_name))||','||
max(decode(c.position,9,c.column_name))||','||
max(decode(c.position,10,c.column_name))||','||
max(decode(c.position,11,c.column_name))||','||
max(decode(c.position,12,c.column_name))||','||
max(decode(c.position,13,c.column_name))||','||
max(decode(c.position,14,c.column_name))||','||
max(decode(c.position,15,c.column_name))||','||
max(decode(c.position,16,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name))||','||
max(decode(d.position,5,d.column_name))||','||
max(decode(d.position,6,d.column_name))||','||
max(decode(d.position,7,d.column_name))||','||
max(decode(d.position,8,d.column_name))||','||
max(decode(d.position,9,d.column_name))||','||
max(decode(d.position,10,d.column_name))||','||
max(decode(d.position,11,d.column_name))||','||
max(decode(d.position,12,d.column_name))||','||
max(decode(d.position,13,d.column_name))||','||
max(decode(d.position,14,d.column_name))||','||
max(decode(d.position,15,d.column_name))||','||
max(decode(d.position,16,d.column_name)),',')||')'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE IN ('P', 'U')
group by a.table_name, b.table_name
order by  1
/

Child tables referencing a master table
---------------------------------------
select b.TABLE_NAME||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name))||','||
max(decode(d.position,5,d.column_name))||','||
max(decode(d.position,6,d.column_name))||','||
max(decode(d.position,7,d.column_name))||','||
max(decode(d.position,8,d.column_name))||','||
max(decode(d.position,9,d.column_name))||','||
max(decode(d.position,10,d.column_name))||','||
max(decode(d.position,11,d.column_name))||','||
max(decode(d.position,12,d.column_name))||','||
max(decode(d.position,13,d.column_name))||','||
max(decode(d.position,14,d.column_name))||','||
max(decode(d.position,15,d.column_name))||','||
max(decode(d.position,16,d.column_name)),',')||') References '||
a.table_name||'('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name))||','||
max(decode(c.position,5,c.column_name))||','||
max(decode(c.position,6,c.column_name))||','||
max(decode(c.position,7,c.column_name))||','||
max(decode(c.position,8,c.column_name))||','||
max(decode(c.position,9,c.column_name))||','||
max(decode(c.position,10,c.column_name))||','||
max(decode(c.position,11,c.column_name))||','||
max(decode(c.position,12,c.column_name))||','||
max(decode(c.position,13,c.column_name))||','||
max(decode(c.position,14,c.column_name))||','||
max(decode(c.position,15,c.column_name))||','||
max(decode(c.position,16,c.column_name)),',')||')'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE IN ('P', 'U')
group by a.table_name, b.table_name
order by  1
/

Disable all Referential constraints

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

DROP Primary Key Constraint

Remember , In order to drop the primary key, all foreign keys referring the table has to be dropped
A PL/SQL code looks better

set serveroutput ON SIZE 1000000

BEGIN
for c1 in(select table_name, constraint_name from user_constraints where constraint_type='P') LOOP
for c2 in(select table_name, constraint_name from user_constraints where constraint_type='R' and r_constraint_name=c1.constraint_name) LOOP
dbms_output.put_line('alter table '||c2.table_name||' drop constraint '||c2.constraint_name||';');
END LOOP;
dbms_output.put_line('alter table '||c1.table_name||' drop constraint '||c1.constraint_name||';');
dbms_output.put_line('---------------------');
END LOOP;
END;
/

Create Foreign Key constraint

select 'alter table '||a.TABLE_NAME||' add constraint '||a.constraint_name||' foreign key('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name)),',')||');'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE='P'
group by a.table_name, a.constraint_name, b.table_name
order by  1
/

Create Primary Key constraints

select 'alter table '||b.table_name||' add constraint '||b.constraint_name||' primary key('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name)),',')||')'
from user_constraints b, user_cons_columns d
where b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and b.CONSTRAINT_TYPE IN('P')
group by b.table_name, b.constraint_name
order by  1
/

Drop and Add Referential constraints for truncating a table

All referential integrity constraints has to be dropped before truncating a table
The following code can be used to generate scripts to
     1) Drop foreign key constraints
     2) Truncate table
     3) Recreate foreign key constraints

set serveroutput ON

set serveroutput ON

declare
p_tn varchar2(30) := 'EMPLOYEES';
BEGIN
for cur1 in(select 'alter table '||TABLE_NAME||' drop constraint '||constraint_name||';' str from user_constraints where constraint_type='R' and r_constraint_name IN (select constraint_name from user_constraints where constraint_type in('P', 'U') and table_name=p_tn)) LOOP
dbms_output.put_line(cur1.str);
END LOOP;
dbms_output.put_line('truncate table '||p_tn||';');
for cur1 IN( select 'alter table '||a.TABLE_NAME||' add constraint '||a.constraint_name||' foreign key('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name)),',')||');' str
from user_constraints  a, user_constraints  b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
b.table_name=p_tn and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE='P'
group by a.table_name, a.constraint_name, b.table_name
order by  1) LOOP
dbms_output.put_line(cur1.str);
END LOOP;
END;
/

Create hierarchy for Referential Tables

It is not possible drop a table that is referenced by some other tables.
Before dropping the table, the FK constraint has to be dropped or the FK table has to be dropped.
Also when creating tables the correct table creation order has to be determined
The parent table has to be created before child table.

The following script can be used to arrange the table hierarchially.

with data
as
(select table_name, constraint_name, r_constraint_name, constraint_type,
nvl( (select constraint_name from user_constraints uc2 where uc2.table_name=uc1.table_name and uc2.constraint_type = 'P'),
(select constraint_name from user_constraints uc2 where uc2.table_name=uc1.table_name and uc2.constraint_type = 'U') ) pkey
from user_constraints uc1
where constraint_type in ('P','U','R'))
select rpad('*',2*level,'*')||table_name tname from data
start with table_name not in (select table_name from data where constraint_type = 'R')
connect by prior pkey = r_constraint_name and constraint_type = 'R'
/

Missing rows from child tables

 

For finding out the rows that are missing in the parent for a Referential constraint column

Create the following table to store the rowid values of the problem rows

create table rows_cons_missing(table_name varchar2(30), constraint_name varchar2(30), missing_row rowid);

---   DELETE rows_cons_missing;

set serveroutput ON

DECLARE
str1 varchar2(1000);
str2 varchar2(1000);
qry1 varchar2(1000);
v_tabname varchar2(30) := 'EMPLOYEES';
rtabname varchar2(30);
BEGIN
for cur1 in(select constraint_name, r_constraint_name from user_constraints where table_name=v_tabname and constraint_type='R')
LOOP
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str1 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.constraint_name and c.constraint_name=cur1.constraint_name;
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str2 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.r_constraint_name and c.constraint_name=cur1.r_constraint_name;
select table_name INTO rtabname from user_constraints where constraint_name=cur1.r_constraint_name;
qry1 := 'INSERT INTO rows_cons_missing select '''||v_tabname||''','''||cur1.constraint_name||''',rowid from '||v_tabname||' where ('||str1||') NOT IN (select ('||str2||') from '||rtabname||')';
--dbms_output.put_line(qry1);
execute immediate qry1;
dbms_output.put_line(sql%rowcount||' Rows inserted for constraint '||cur1.constraint_name);
END LOOP;
END;
/

Missing rows from parent table

 

While deleting records from the master table there can be an excpetion, because
there are records in the child table referencing the parent table. 

The following script can be used to identify the rows thar are currently referenced by other tables

Here again we use the follwing table for storing the rowid values

SQL> desc rows_cons_missing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT_NAME                                    VARCHAR2(30)
 MISSING_ROW                                        ROWID

---   DELETE rows_cons_missing;

set serveroutput ON

DECLARE
str1 varchar2(1000);
str2 varchar2(1000);
qry1 varchar2(1000);
v_tabname varchar2(30) := 'WEB_CATEGORY';
rtabname varchar2(30);
BEGIN
for cur1 in(select table_name, constraint_name, r_constraint_name from user_constraints where constraint_type='R' and r_constraint_name IN(select constraint_name from user_constraints where constraint_type='P' and table_name=v_tabname))
LOOP
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str1 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.constraint_name and c.constraint_name=cur1.constraint_name;
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str2 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.r_constraint_name and c.constraint_name=cur1.r_constraint_name;
qry1 := 'INSERT INTO rows_cons_missing select '''||v_tabname||''','''||cur1.constraint_name||''',rowid from '||v_tabname||' where ('||str1||') NOT IN (select ('||str2||') from '||cur1.table_name||')';
--dbms_output.put_line(qry1);
execute immediate qry1;
dbms_output.put_line(sql%rowcount||' Rows inserted for constraint '||cur1.constraint_name);
END LOOP;
END;
/

Posted in Uncategorized | Leave a comment

Source code for Oracle SYS Views

Oracle System Views

Source code for Oracle SYS Views

Continue reading »

Posted in Uncategorized | Leave a comment

Hierarchial Queries

Hierarchial Queries

The queries shown below demonstrates how to query an hierarchial database.
The queries works on employees tables.
The script for creating the table and inserting data is available here…

Continue reading »

Posted in Uncategorized | Leave a comment

More about Oracle Indexes

More about Oracle Indexes
Also see
How to debug PL/SQL code



Are my indexes correct? Does all the queries use indexes? Do I need to create more indexes?
These are some of the questions developers frequently ask. Creating too many indexes can slow down the OLTP applications very much.
So it is required to decide on creating the indexes as accurately as possible.

Continue reading »

Posted in Uncategorized | Leave a comment

Oracle System Views

Oracle System Views


Also see
How to debug PL/SQL code

Continue reading »

Posted in Uncategorized | Comments Off

Oracle Installation Links

Oracle Installation Links

A collection of useful links related to installing Oracle on various platforms.

Installation of Oracle on Linix Platforms – 10g

Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases 32-bit/64-bit)

Installing Oracle Database 10g on Novell SUSE Linux Enterprise Server 9 (SLES-9) and on SUSE Linux Professional 9.1, 9.2, and 9.3

Installing Oracle Database 10g Release 1 and 2 (32-bit/64-bit) on Red Hat Enterprise Linux AS 4, 3, 2.1, Fedora Core 4, 3, 1, RH 9 on x86 and x86_64 Architecture

Installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3

Follow the “Continue reading ยป” link for many more links….

Continue reading »

Posted in Uncategorized | Leave a comment

Designing Indexes – Concepts

Designing Indexes – Concepts

Index Concepts

Creating the right indexes can improve the search performance in database applications. Also for tables with heavy DML’s the indexes will cause more write IO. So for an application that is both transaction and query intensive a compromise has to be reached regarding the creation of indexes. Too many indexes can increase the write IO and less indexes will lead to full table scan and increase in total reads and physical disk reads.

Continue reading »

Posted in Uncategorized | Leave a comment