be happy and make others to be happy: Some Useful SQL Scripts

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;

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

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

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

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;

be happy and make others to be happy

Infolinks

Wednesday 11 July 2012

Some Useful SQL Scripts

Some Useful SQL Scripts

No comments:

Post a Comment