Sunday, April 29, 2012

Some Useful SQL Scripts


Some Useful SQL Scripts are provided below:





rem -----------------------------------------------------------------------
rem UPDATED VERSION
rem Filename:   matrix.sql
rem Purpose:    Example of a CROSS MATRIX report implemented using
rem             standard SQL.
rem Date:       12-Feb-2000
rem Author:     Frank Naude, Oracle FAQ
rem
rem Description Removed the Main query because the sub query itself
rem             will full fill the requirement.
rem -----------------------------------------------------------------------

       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 scott.emp
       GROUP BY job
/

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






rem -----------------------------------------------------------------------
rem Filename:   oerr.sql
rem Purpose:    Lookup Oracle error messages. Similar to unix "oerr" command.
rem             This script is handy on platforms like NT with no OERR support
rem -----------------------------------------------------------------------

set serveroutput on
set veri off feed off

prompt Lookup Oracle error messages:
prompt
prompt Please enter error numbers as negatives. E.g. -1
prompt

exec dbms_output.put_line('==> '||sqlerrm( &errno ) );

set veri on feed on
undef errno





rem -----------------------------------------------------------------------
rem Filename:   maxvalue.sql
rem Purpose:    Select the Nth highest value from a table
rem Date:       18-Apr-2001
rem -----------------------------------------------------------------------

select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;

-- Example :
--
-- Given a table called emp with the following columns:
--   id   number
--   name varchar2(20)
--   sal  number
--
-- For the second highest salary:
--
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
--




rem -----------------------------------------------------------------------
rem Filename:   minvalue.sql
rem Purpose:    Select the Nth lowest value from a table
rem Date:       18-Apr-2001
rem -----------------------------------------------------------------------

select level, min('col_name') from my_table
where level = '&n'
connect by prior ('col_name') < 'col_name')
group by level;


-- Example:
--
-- Given a table called emp with the following columns:
--   id   number
--   name varchar2(20)
--   sal  number
--
-- For the second lowest salary:
--
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
--





rem -----------------------------------------------------------------------
rem Filename:   default.sql
rem Purpose:    Example script to demonstrate DEFAULT column values
rem Date:       25-Apr-2001
rem -----------------------------------------------------------------------

-- drop table x
-- /

create table x (a char, b number default 99999, c date, d varchar2(6))
/

alter table x modify (c date default sysdate)
/

insert into x(a, d) values ('a', 'qwerty')
/

select * from x
/

--
-- Expected output:
--
--  A          B C           D
--  - ---------- ----------- ------
--  a      99999 25-APR-2001 qwerty
-





rem -----------------------------------------------------------------------
rem Filename:   comments.sql
rem Purpose:    Display table and column comments for the current schema
rem             Handy for getting to know the database schema
rem -----------------------------------------------------------------------

set pages 50000
set null 'No Comments'

tti 'Table Comments'
col comments format a29 wrap word

select * from user_tab_comments;

tti 'Column Comments'
col comments format a18 wrap word
break on table_name skip 1
select * from user_col_comments;
clear break

set null ''
set pages 23




rem -----------------------------------------------------------------------
rem Filename:   appinfo.sql
rem Purpose:    Example of how to pass application info through to Oracle RDBMS
rem -----------------------------------------------------------------------

-- The following code tells the database what the application is up to:

begin
   dbms_application_info.set_client_info('BANCS application info');
   dbms_application_info.set_module('BANCS XYZ module', 'BANCS action name');
end;
/

-- Retrieve application info from the database:

select module, action, client_info
from   sys.v_$session where audsid = USERENV('SESSIONID')
/

select sql_text
from   sys.v_$sqlarea
where  module = 'BANCS XYZ module'
and  action = 'BANCS action name'
/




rem -----------------------------------------------------------------------
rem Filename:   help.sql
rem Purpose:    Access the SQL*Plus Help table
rem Notes:      If the HELP table doesn't exist, see the SQL*Plus FAQ for
rem             installation instructions.
rem Date:       05-July-98
rem -----------------------------------------------------------------------

select info
from   system.help
where  upper(topic)=upper('&1')
/






rem -----------------------------------------------------------------------
rem Filename:   leapyear.sql
rem Purpose:    Check if a year is a leap year
rem -----------------------------------------------------------------------

select year,
       decode( mod(year, 4), 0,
          decode( mod(year, 400), 0, 'Leap Year',
             decode( mod(year, 100), 0, 'Not a Leap Year', 'Leap Year')
          ), 'Not a Leap Year'
       ) as leap_year_indicator
from   my_table
/



rem -----------------------------------------------------------------------
rem Filename:   objopt.sql
rem Purpose:    Demonstrate Oracle database types and object tables
rem Date:       12-Feb-2000
rem -----------------------------------------------------------------------

drop type employee_typ;

create type employee_typ as object (
        empno NUMBER,
        emp_name varchar2(30),
        hiredate date,
        member function days_at_company return NUMBER,
        pragma restrict_references(days_at_company, WNDS)
)
/

create type body employee_tye is
begin
        member function days_at_company return number is
        begin
                return (SYSDATE-hiredate);
        end;
end;
/
show errors

drop type department_typ;

create type department_typ as object (
        deptno NUMBER(5),
        manager ref employee_typ
)
/

select * from user_types
where predefined = 'NO';

-- Create a object table
create table emp1 as employee_typ;

create table employee (emp_no NUMBER, emp employee_typ);

insert into employee values (1, employee_typ(1, 'Frank Naude', SYSDATE));

commit;

select * from employee;

select x.emp.emp_name from employee x;






rem -----------------------------------------------------------------------
rem Filename:   varray.sql
rem Purpose:    Demontrate VARRAY (variable array in one database column)
rem             collection types
rem Date:       12-Aug-2001
rem -----------------------------------------------------------------------

CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128);
/
CREATE TABLE varray_table (id number, col1 vcarray);

INSERT INTO varray_table VALUES (1, vcarray('A'));
INSERT INTO varray_table VALUES (2, vcarray('B', 'C'));
INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F'));

SELECT * FROM varray_table;
SELECT * FROM USER_VARRAYS;
-- SELECT * FROM USER_SEGMENTS;

-- Unnesting the collection:
select t1.id, t2.COLUMN_VALUE
from   varray_table t1, TABLE(t1.col1) t2
/

-- Use PL/SQL to access the varray...
set serveroutput on
declare
  v_vcarray vcarray;
begin
  for c1 in (select * from varray_table) loop
      dbms_output.put_line('Row fetched...');
      FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
          dbms_output.put_line('...property fetched: '|| c1.col1(i));
      END LOOP;
  end loop;
end;
/

-- Clean-up...
DROP TABLE varray_table;
DROP TYPE vcarray;





rem -----------------------------------------------------------------------
rem Filename:   temptab.sql
rem Purpose:    Demonstrate Oracle 8i temporary tables
rem Date:       23-Apr-2000
rem -----------------------------------------------------------------------

drop table x
/

create global temporary table x (a date)
        on commit delete rows     -- Delete rows after commit
        -- on commit preserve rows   -- Delete rows after exit session
/

select table_name, temporary, duration
from   user_tables
where  table_name = 'X'
/

insert into x values (sysdate);

select * from x;

commit;

-- Inserted rows are missing after commit
select * from x;




rem -----------------------------------------------------------------------
rem Purpose:    Delete duplicate values from a table
rem Date:       04-Mar-2005
rem Notes:      Verify that the correct rows are deleted before you COMMIT!
rem -----------------------------------------------------------------------

DELETE FROM my_table
 WHERE ROWID NOT IN (SELECT MIN(ROWID)
                       FROM my_table
                      GROUP BY delete_col_name);

-- Example :
--
-- Given a table called emp with the following columns:
--   id   number
--   name varchar2(20)
--   sal  number
--
-- To delete the duplicate values:
--
-- DELETE FROM emp
--  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);
--
-- COMMIT;
--






rem -----------------------------------------------------------------------
rem Purpose:    Delete duplicate values from a table
rem Date:       04-Mar-2005
rem Notes:      Verify that the correct rows are deleted before you COMMIT!
rem -----------------------------------------------------------------------

DELETE FROM my_table
 WHERE ROWID NOT IN (SELECT MIN(ROWID)
                       FROM my_table
                      GROUP BY delete_col_name);

-- Example :
--
-- Given a table called emp with the following columns:
--   id   number
--   name varchar2(20)
--   sal  number
--
-- To delete the duplicate values:
--
-- DELETE FROM emp
--  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);
--
-- COMMIT;
--





rem -----------------------------------------------------------------------
rem Filename:  plstable.sql
rem Purpose:   Example: how to populate a PL/SQL Table from a cursor
rem Date:      09-Apr-1999
rem -----------------------------------------------------------------------

set serveroutput on

declare
  -- Declare the PL/SQL table
  type deptarr is table of dept%rowtype
       index by binary_integer;
  d_arr deptarr;

  -- Declare cursor
  type d_cur is ref cursor return dept%rowtype;
  c1 d_cur;

  i number := 1;
begin
  -- Populate the PL/SQL table from the cursor
  open c1 for select * from dept;
  loop
    exit when c1%NOTFOUND;
    fetch c1 into d_arr(i);
    i := i+1;
  end loop;
  close c1;

  -- Display the entire PL/SQL table on screen
  for i in 1..d_arr.last loop
    dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
    dbms_output.put_line('DNAME  : '||d_arr(i).dname  );
    dbms_output.put_line('LOC    : '||d_arr(i).loc    );
    dbms_output.put_line('---------------------------');
  end loop;
end;
/





rem -----------------------------------------------------------------------
rem Filename:  password.sql
rem Purpose:   Simple password encryption package to demonstrate how
rem            values can be encrypted and decrypted using Oracle's
rem            DBMS Obfuscation Toolkit
rem Note:      Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Date:      18-Mar-2003
rem -----------------------------------------------------------------------


CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors


CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- key must be exactly 8 bytes long
  c_encrypt_key varchar2(8) := 'key45678';

  function encrypt (i_password varchar2) return varchar2 is
    v_encrypted_val varchar2(38);
    v_data          varchar2(38);
  begin
     -- Input data must have a length divisible by eight
     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
        input_string     => v_data,
        key_string       => c_encrypt_key,
        encrypted_string => v_encrypted_val);
     return v_encrypted_val;
  end encrypt;

  function decrypt (i_password varchar2) return varchar2 is
    v_decrypted_val varchar2(38);
  begin
     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
        input_string     => i_password,
        key_string       => c_encrypt_key,
        decrypted_string => v_decrypted_val);
     return v_decrypted_val;
  end decrypt;


end PASSWORD;
/
show errors

-- Test if it is working...
select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;





rem -----------------------------------------------------------------------
rem Filename:   dynasql.sql
rem Purpose:    Example PL/SQL code to demonstrate Dynamic SQL
rem Date:       25-Feb-2003
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE CREATE_TABLE1 AS
  sql_stmt varchar2(4000);
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
END;
/
show errors

CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS
  cur integer;
  rc  integer;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
  rc := DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
show errors

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
  v_cursor integer;
  v_dname  char(20);
  v_rows   integer;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
  DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  loop
    if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
       exit;
    end if;
    DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
    DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
  end loop;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
  when others then
       DBMS_SQL.CLOSE_CURSOR(v_cursor);
       raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/
show errors





rem -----------------------------------------------------------------------
rem Filename:   httpget.sql
rem Purpose:    Access Internet Web pages from SQL or PL/SQL
rem Notes:      From Oracle 8.0 one can retrieve web pages directly
rem         from SQL or PL/SQL. Note you need to run utlhttp.sql as
rem         SYS before this procedure will work.
rem Date:       27-Mar-2000
rem -----------------------------------------------------------------------

set pages 50000

select utl_http.request('http://oracleapps4u.blogspot.in/') from dual;





rem -----------------------------------------------------------------------
rem Filename:   strreplace.sql
rem Purpose:    Replace all occurences of a substring with another substring
rem Date:       28-Jul-2003
rem -----------------------------------------------------------------------

create or replace function strreplace(str varchar2, from_str varchar2, to_str varchar2)
  return varchar2
AS
  str_temp varchar2(4000);
  str_pos  number := instr(str, from_str);
BEGIN
  str_temp := str;
  while ( str_pos > 0 ) loop
    str_temp := substr(str_temp, 0, str_pos-1) || to_str ||
                substr(str_temp, str_pos + length(from_str));
    str_pos  := instr(str_temp, from_str);
  end loop;
  return str_temp;
END;
/
show errors


-- Examples

select strreplace('This is a beautiful day!', 'beautiful', 'horrible')
from dual
/

select 'mv '||name||' '||strreplace(name, 'OLDSID', 'NEWSID')
from   v$datafile
/






rem ----------------------------------------------------------------------
rem Filename:   smtp-att.sql
rem Purpose:    Send e-mail messages and attachments from PL/SQL
rem Notes:      From Oracle8i release 8.1.6 one can send e-mail messages
rem             directly from PL/SQL using either the UTL_TCP or UTL_SMTP
rem             packages. Jserver needs to be installed and configured.
rem             No pipes or external procedures required.
rem Date:       15-MAR-2001
rem ----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from    varchar2 := 'EMAILADDRESS@DOMAIN.COM',    ----- MAIL BOX SENDING THE EMAIL
  msg_to      varchar2 := 'EMAILADDRESS@DOMAIN.COM',    ----- MAIL BOX RECIEVING THE EMAIL
  msg_subject varchar2 := 'Output file TEST1',          ----- EMAIL SUBJECT
  msg_text    varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
  v_output1   varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
  c  utl_tcp.connection;
  rc integer;
  crlf VARCHAR2(2):= CHR(13)||CHR(10);
  mesg VARCHAR2( 32767 );
BEGIN
  c := utl_tcp.open_connection('196.35.140.18', 25);       ----- OPEN SMTP PORT CONNECTION
  rc := utl_tcp.write_line(c, 'HELO 196.35.140.18');       ----- PERFORMS HANDSHAKING WITH SMTP SERVER
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18');       ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);    ----- MAIL BOX SENDING THE EMAIL
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);        ----- MAIL BOX RECIEVING THE EMAIL
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'DATA');                     ----- EMAIL MESSAGE BODY START
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
  rc := utl_tcp.write_line(c, 'From: '||msg_from||' <'||msg_from||'>');
  rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
  rc := utl_tcp.write_line(c, 'To: '||msg_to||' <'||msg_to||'>');
  rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
  rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;');     ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
  rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"');          ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
  rc := utl_tcp.write_line(c, '');                                   ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
  rc := utl_tcp.write_line(c, '-------SECBOUND');
  rc := utl_tcp.write_line(c, 'Content-Type: text/plain');           ----- 1ST BODY PART. EMAIL TEXT MESSAGE
  rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit');
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, msg_text);                             ----- TEXT OF EMAIL MESSAGE
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, '-------SECBOUND');
  rc := utl_tcp.write_line(c, 'Content-Type: text/plain;');          ----- 2ND BODY PART.
  rc := utl_tcp.write_line(c, ' name="Test.txt"');
  rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
  rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;');   ----- INDICATES THAT THIS IS AN ATTACHMENT
  rc := utl_tcp.write_line(c, ' filename="Test.txt"');               ----- SUGGESTED FILE NAME FOR ATTACHMENT
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, v_output1);
  rc := utl_tcp.write_line(c, '-------SECBOUND--');
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, '.');                    ----- EMAIL MESSAGE BODY END
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'QUIT');                 ----- ENDS EMAIL TRANSACTION
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  utl_tcp.close_connection(c);                         ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
  when others then
       raise_application_error(-20000, SQLERRM);
END;
/








-----------------------------------------------------------------------
-- Filename:   DBA_add_PK.sql
-- Purpose:    Create a Primary key column on a table were this is not
--             yet available
-- Notes:      This script will create a new script that extends your
--             tables with a extra column to store the primary and generates
--             the primary key values.
-- Date:       02-august-2007
-------------------------------------------------------------------------

DECLARE
  TYPE oracle_tables IS VARRAY(20) OF VARCHAR2(30);
  my_tables oracle_tables;

  v_table_name VARCHAR2(60); 
  v_pk_name    VARCHAR2(30);
  v_schema     VARCHAR2(30);
 
BEGIN
    -- Put in your schema name here
    v_schema  := 'your_schema';

    -- Put in your tables you want extend here
    my_tables := oracle_tables('table01',
                               'table02',
                               'table03',
                               'table04',
                               'table05',
                               'table06',
                               'table07',
                               'table08',
                               'table09',
                               'table10',
                               'table11',
                               'table12',
                               'table13',
                               'table14',
                               'table15',
                               'table16',
                               'table17',
                               'table18',
                               'table19',
                               'table20');
 
 
 
  FOR i in 1..my_tables.COUNT() LOOP

    v_pk_name    := SUBSTR(my_tables(i),1,1)||substr(my_tables(i),instr(my_tables(i),'_')+1,3)||'_SEQ';
    v_table_name := v_schema||'.'||my_tables(i);

   
    DBMS_OUTPUT.put_line(CHR(10)||CHR(10));
   

    DBMS_OUTPUT.put_line('------------------------------------------------------');
    DBMS_OUTPUT.put_line('-- Begin script '||v_table_name);
    DBMS_OUTPUT.put_line('------------------------------------------------------');

   
    -----------------------------------------------------------
    --  Copy table structure
    -----------------------------------------------------------
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('-- Copy table structure');
    DBMS_OUTPUT.put_line('CREATE TABLE '||v_table_name||'_t AS SELECT * FROM '||v_table_name||' WHERE 1=0;');
   
    -----------------------------------------------------------
    --  Alter statements - add primary key column on temp table
    -----------------------------------------------------------
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Alter statements - add primary key column on temp table');
    DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||'_t ADD '||v_pk_name||' '||' number(10) NOT NULL;');
    DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||'_t ADD CONSTRAINT pk_'||SUBSTR(my_tables(i),1,20)||'_t PRIMARY KEY ('||v_pk_name||');');
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Alter statements - add primary key field to production table');
    DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||' ADD '||v_pk_name||' '||' number(10);');

       
    -----------------------------------------------------------
    --  Create Sequence
    -----------------------------------------------------------
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Create Sequence');
    DBMS_OUTPUT.put_line('CREATE SEQUENCE '||v_schema||'.'||'SQ_'||my_tables(i) ||' INCREMENT BY 1 START WITH 1;');
   

    -----------------------------------------------------------
    --  Create Trigger
    -----------------------------------------------------------
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Create Trigger');   
    DBMS_OUTPUT.put_line('CREATE OR REPLACE TRIGGER '||substr(v_table_name,1,20)||'_briu_t');
    DBMS_OUTPUT.put_line('        BEFORE INSERT OR UPDATE');
    DBMS_OUTPUT.put_line('        ON '||v_table_name||'_t');
    DBMS_OUTPUT.put_line('        REFERENCING NEW AS NEW OLD AS OLD');
    DBMS_OUTPUT.put_line('        FOR EACH ROW');
    DBMS_OUTPUT.put_line('');
    DBMS_OUTPUT.put_line('        BEGIN');
    DBMS_OUTPUT.put_line('           -- If the PK column is empty we gonna fill this in with the next value of the sequence');   
    DBMS_OUTPUT.put_line('           IF :NEW.'||v_pk_name ||' IS NULL THEN');
    DBMS_OUTPUT.put_line('              SELECT SQ_'||my_tables(i) ||'.NEXTVAL');
    DBMS_OUTPUT.put_line('              INTO :NEW.'||v_pk_name);
    DBMS_OUTPUT.put_line('              FROM DUAL;');
    DBMS_OUTPUT.put_line('           END IF;');
    DBMS_OUTPUT.put_line('        END;'); 
    DBMS_OUTPUT.put_line('/');
                       
   
    -----------------------------------------------------------
    --  Copy data from production table to the temp table
    -----------------------------------------------------------   
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Copy data from production table to the temp table');
    DBMS_OUTPUT.put_line('INSERT INTO '||v_table_name||'_t SELECT * FROM '||v_table_name||';');
   
   
    -----------------------------------------------------------
    --  Truncate production table
    -----------------------------------------------------------
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Truncate production table');  
    DBMS_OUTPUT.put_line('TRUNCATE TABLE '||v_table_name||';');
  
   
    -----------------------------------------------------------
    --  Modify field to production table and copy data from the temp table
    -----------------------------------------------------------   
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Alter statements - modify primary key field to production table');
    DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||' ADD '||v_pk_name||' '||' number(10) NOT NULL;');
    DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||' ADD CONSTRAINT pk_'||SUBSTR(my_tables(i),1,20)||' PRIMARY KEY ('||v_pk_name||');');
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Copy data from temp table to the production table');
    DBMS_OUTPUT.put_line('INSERT INTO '||v_table_name||' SELECT * FROM '||v_table_name||'_t ;');
   
   
   
   
    -----------------------------------------------------------
    --  Create trigger
    -----------------------------------------------------------
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Create trigger');   
    DBMS_OUTPUT.put_line('CREATE OR REPLACE TRIGGER '||substr(v_table_name,1,20)||'_briu');
    DBMS_OUTPUT.put_line('        BEFORE INSERT OR UPDATE');
    DBMS_OUTPUT.put_line('        ON '||v_table_name);
    DBMS_OUTPUT.put_line('        REFERENCING NEW AS NEW OLD AS OLD');
    DBMS_OUTPUT.put_line('        FOR EACH ROW');
    DBMS_OUTPUT.put_line('');
    DBMS_OUTPUT.put_line('        BEGIN');
    DBMS_OUTPUT.put_line('           -- If the PK column is empty we gonna fill this in with the next value of the sequence');
    DBMS_OUTPUT.put_line('           IF :NEW.'||v_pk_name ||' IS NULL THEN');
    DBMS_OUTPUT.put_line('              SELECT SQ_'||my_tables(i) ||'.NEXTVAL');
    DBMS_OUTPUT.put_line('              INTO :NEW.'||v_pk_name);
    DBMS_OUTPUT.put_line('              FROM DUAL;');
    DBMS_OUTPUT.put_line('           END IF;');
    DBMS_OUTPUT.put_line('        END;');   
    DBMS_OUTPUT.put_line('/');   
  
    DBMS_OUTPUT.put_line('commit;'); 
   
    -----------------------------------------------------------
    --  Drop temp table
    -----------------------------------------------------------
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line('--Drop temp table');  
    DBMS_OUTPUT.put_line('DROP TABLE '||v_table_name||'_t;');
   
    DBMS_OUTPUT.put_line('------------------------------------------------------');
    DBMS_OUTPUT.put_line('-- End script '||v_table_name);
    DBMS_OUTPUT.put_line('------------------------------------------------------');
    DBMS_OUTPUT.put_line(CHR(10));
    DBMS_OUTPUT.put_line(CHR(10));
         
   
  END LOOP;
END;






rem -----------------------------------------------------------------------
rem Filename:   db-error.sql
rem Purpose:    Log all database errors to a table
rem             Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and
rem         GRANT SELECT ON SYS.V_$SESSION required
rem Date:       21-Mar-2000
rem -----------------------------------------------------------------------

drop trigger log_errors_trig;
drop table   log_errors_tab;

create table log_errors_tab (
      error     varchar2(30),
      timestamp date,
      username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
      process   varchar2(8),
      program   varchar2(48));

create or replace trigger log_errors_trig
      after servererror on database
declare
      var_user     varchar2(30);
      var_osuser   varchar2(30);
      var_machine  varchar2(64);
      var_process  varchar2(8);
      var_program  varchar2(48);
begin
      select username, osuser, machine, process, program
      into   var_user, var_osuser, var_machine, var_process, var_program
      from   sys.v_$session
      where  audsid = userenv('sessionid');

      insert into log_errors_tab
        values(dbms_standard.server_error(1),sysdate,var_user,
               var_osuser,var_machine,var_process,var_program);
end;
/