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