queries

Oracle:

1. Top 10 records
sing the ROWNUM Pseudocolumn
One way to solve this problem is by using the Oracle pseudocolumn ROWNUM. ROWNUM is available in Oracle versions 7, 8.0 and 8i (8.1); I am not sure about Oracle 6. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use the ROWNUM pseudocolumn to limit the number of rows returned by a query to 5:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6;

advertisement

Get comprehensive solutions for building software.

# Download: IBM Rational Rose XDE Developer for Visual Studio

# Download: IBM Rational Application Developer for WebSphere Software

# Download: IBM Rational Software Architect

# On-Demand Webcast: Making Java Development Easier

Listing 2 shows the output of the above code. As you see, the first five rows have been returned.

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause.

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6
ORDER BY Sal;

Listing 3 shows the output of the above code.

Watch Out! Because the ROWNUM is assigned upon retrieval, it is assigned prior to any sorting! This is opposite to the result you would get in SQL Server using the TOP clause. In order to select employees with the highest five salaries, you have to force sorting and then apply ROWNUM condition. Here is the syntax for a top-N query where N = 5 (this syntax with the subquery works only in Oracle 8i):

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
ORDER BY NVL(Sal, 0) DESC)
WHERE ROWNUM < 6;

Listing 4 shows the output of the above code. I used the NVL() function to sort the expression because sorting just by Emp_Salary would have put all records with NULL salary before those with the highest salaries, and that's not what we wanted to achieve.

Utilizing Oracle 8i's Ranking Functions
Another way to perform a top-N query uses the new Oracle 8i feature called "analytic functions." The SQL language, while extremely capable in many areas, has never provided strong support for analytic tasks, such as computing rankings, cumulative and moving averages, lead/lag comparisons, and reporting. These tasks require extensive PL/SQL programming, often with performance issues. Oracle 8i now provides a new wide set of analytic functions that address this need.
advertisement

For a top-N query you can use two ranking functions: RANK and DENSE_RANK. Both allow you to rank items in a group—for example, finding the top-five employees by salary, which is exactly what we need to achieve.

The difference between RANK() and DENSE_RANK() is that RANK() leaves gaps in the ranking sequence when there are ties. In our case, Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK() but only fourth place using RANK():

SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Rank,
DENSE_RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Drank
FROM Emp
ORDER BY SAL Desc NULLS LAST;

Listing 5 shows the output of the above code.

The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.

If the sequence is in descending order, then NULLS LAST implies that NULL values are smaller than non-NULL ones and rows with NULLs will appear at the bottom of the list. If the NULLS FIRST | NULLS LAST clause is omitted, then NULL values are considered larger than any other values and their ordering position depends on the ASC | DESC arguments.

If the ordering sequence is ascending (ASC), then rows with NULLs will appear last; if the sequence is descending (DESC), then rows with NULLs will appear first. NULLs are considered equal to other NULLs and, therefore, the order in which rows with NULLs are presented is nondeterministic.

sing RANK() to Obtain a Top-N Query
To obtain a top-N query, use RANK() in a subquery and then apply a filter condition outside the subquery:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank < 6;

advertisement
Listing 6 shows the output of the above code.

Using the same technique, you can retrieve the bottom-five employees by salary:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL ASC NULLS FIRST) AS Emp_Rank
FROM Emp
ORDER BY SAL ASC NULLS FIRST)
WHERE Emp_Rank < 6;

Listing 7 shows the output of the above code.

Ranking functions can be used to operate within groups, too—that is, the rank value gets reset whenever the group changes. This is achieved with a PARTION BY subclause. Here is the syntax to retrieve the top employee by salary per manager group:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(PARTITION BY MGR ORDER BY MGR, SAL DESC NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY MGR, SAL DESC NULLS LAST)
WHERE Emp_Rank = 1;

Listing 8 shows the output of the above code.

As you can see, analytic functions are extremely useful in all types of analysis and computations, and they provide substantial SQL extensions to Oracle 8i.



Sample SQL matrix report

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 Updated By Mahesh Pednekar. (bunty609@hotmail.com)
rem Description Removed the Main query because the sub query itself
rem will full fill the requirement.
rem -----------------------------------------------------------------------



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 Author: Frank Naude, Oracle FAQ
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
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




Some other common sql queries from orafaqs.com


SQL

General SQL Scripts

Sample SQL matrix report

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 Updated By Mahesh Pednekar. (bunty609@hotmail.com)
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
--


Lookup Oracle error messages

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 Author: Frank Naude, Oracle FAQ
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




Display Database version, installed options and port string

rem -----------------------------------------------------------------------
rem Filename: ver.sql
rem Purpose: Show database version with options intalled
rem (handy for your HELP/ABOUT menu)
rem Date: 12-Nov-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set head off feed off pages 0 serveroutput on

col banner format a72 wrap

select banner
from sys.v_$version;

select ' With the '||parameter||' option'
from sys.v_$option
where value = 'TRUE';

select ' The '||parameter||' option is not installed'
from sys.v_$option
where value <> 'TRUE';

begin
dbms_output.put_line('Port String: '||dbms_utility.port_string);
end;
/

set head on feed on


• "Who am I" script

rem -----------------------------------------------------------------------
rem Filename: whoami.sql
rem Purpose: Reports information about your current database context
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off
store set store rep
set head off
set pause off
set termout on

select 'User: '|| user || ' on database ' || global_name,
' (term='||USERENV('TERMINAL')||
', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT
from global_name;

@store
set termout on


Select the Nth highest value from a table

rem -----------------------------------------------------------------------
rem Filename: maxvalue.sql
rem Purpose: Select the Nth highest value from a table
rem Date: 18-Apr-2001
rem Author: Deepak Rai, SSE, Satyam Computer Services Ltd. India
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
--

Select the Nth lowest value from a table

rem -----------------------------------------------------------------------
rem Filename: minvalue.sql
rem Purpose: Select the Nth lowest value from a table
rem Date: 18-Apr-2001
rem Author: Deepak Rai, SSE, Satyam Computer Services Ltd. India
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
--

Demonstrate default column values
rem -----------------------------------------------------------------------
rem Filename: default.sql
rem Purpose: Example script to demonstrate DEFAULT column values
rem Date: 25-Apr-2001
rem Author: Frank Naude, Oracle FAQ
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 Author: Frank Naude, Oracle FAQ
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

Pass application info through to the Oracle RDBMS


rem -----------------------------------------------------------------------
rem Filename: appinfo.sql
rem Purpose: Example of how to pass application info through to Oracle RDBMS
rem Author: Frank Naude, Oracle FAQ
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'
/

SQL*Plus Help script
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 Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

select info
from system.help
where upper(topic)=upper('&1')
/
• Test for Leap Years
rem -----------------------------------------------------------------------
rem Filename: leapyear.sql
rem Purpose: Check if a year is a leap year
rem Author: Frank Naude, Oracle FAQ
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
/
Spell out numbers to words
rem -----------------------------------------------------------------------
rem Filename: spellout.sql
rem Purpose: This script will spell out numbers to words (handy for
rem cheque printing). Example '10' --> Ten
rem Date: 12-Sep-2000
rem Author: Anonymous
rem -----------------------------------------------------------------------

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/
Demonstrate simple encoding and decoding of messages
rem -----------------------------------------------------------------------
rem Filename: encode.sql
rem Purpose: Demonstrate simple encoding and decoding of secret messages
rem This method can be extended to create simple password
rem encryption
rem Date: 15-Feb-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

SELECT TRANSLATE(
'HELLO WORLD', -- Message to encode
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/

SELECT TRANSLATE(
'85@@%._%*@4', -- Message to decode
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/
• Count the number of rows for ALL tables in current schema
rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in the current schema
rem Date: 30-Apr-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off echo off feed off trimspool on head off pages 0

spool countall.tmp
select 'SELECT count(*), '''||table_name||''' from '||table_name||';'
from user_tables
/
spool off

set termout on
@@countall.tmp

set head on feed on


Demonstrate Oracle database types and object tables
rem -----------------------------------------------------------------------
rem Filename: objopt.sql
rem Purpose: Demonstrate Oracle database types and object tables
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
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;
Demonstrate VARRAY database types
rem -----------------------------------------------------------------------
rem Filename: varray.sql
rem Purpose: Demontrate VARRAY (variable array in one database column)
rem collection types
rem Date: 12-Aug-2001
rem Author: Frank Naude, Oracle FAQ
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;
Demonstrate Oracle temporary tables
rem -----------------------------------------------------------------------
rem Filename: temptab.sql
rem Purpose: Demonstrate Oracle 8i temporary tables
rem Date: 23-Apr-2000
rem Author: Frank Naude, Oracle FAQ
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;

Convert LONG data types to LOBs
rem -----------------------------------------------------------------------
rem Filename: conv2lob.sql
rem Purpose: Convert LONG datatypes to LOBs (Large Objects)
rem Dependancies: Oracle 8.1.x or higher
rem Date: 17-Sep-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

create table old_long_table(c1 number, c2 long);
insert into old_long_table values (1, 'LONG data to convert to CLOB');

create table new_lob_table(c1 number, c2 clob);

-- Use TO_LOB function to convert LONG to LOB...
insert into new_lob_table
select c1, to_lob(c2) from old_long_table;

-- Note: the same procdure can be used to convert LONG RAW datatypes to BLOBs.

Delete duplicate values from a table

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 Author: Dharmendra Srivastava,Associate,
rem MindTree Consulting Pvt Ltd. India
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;
--

General PL/SQL: Scripts
1. Update/ delete from a huge table with intermittent commits
rem -----------------------------------------------------------------------
rem Filename: plsloop.sql
rem Purpose: Example: UPDATE/DELETE in a loop and commit very X records
rem Handy for huge tables that cause rollback segment problems
rem DON'T ISSUE COMMIT TOO FREQUENTLY!
rem Date: 09-Apr-1999; Updated: 25-Nov-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

declare
i number := 0;
cursor s1 is SELECT rowid, t.* FROM tab1 t WHERE col1 = 'value1';
begin
for c1 in s1 loop
update tab1 set col1 = 'value2'
where rowid = c1.rowid;

i := i + 1; -- Commit after every X records
if i > 10000 then
commit;
i := 0;
end if;

end loop;
commit;
end;
/

-- Note: More advanced users can use the mod() function to commit every N rows.
-- No counter variable required:
--
-- if mod(i, 10000)
-- commit;
-- dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount);
-- end if;
--
Simple program to demonstrate BULK COLLECT and BULK BIND operations

rem -----------------------------------------------------------------------
rem Filename: bulkbind.sql
rem Purpose: Simple program to demonstrate BULK COLLECT and BULK BIND.
rem Notes: Bulk operations on ROWTYPE only work from and above.
rem Date: 12-Feb-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000

DECLARE
CURSOR emp_cur IS SELECT * FROM EMP;

TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tab emp_tab_t; -- In-memory table

rows NATURAL := 10000; -- Number of rows to process at a time
i BINARY_INTEGER := 0;
BEGIN
OPEN emp_cur;
LOOP
-- Bulk collect data into memory table - X rows at a time
FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
EXIT WHEN emp_tab.COUNT = 0;

DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');

FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
-- Manipumate data in the memory table...
dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
END LOOP;

-- Bulk bind of data in memory table...
FORALL i in emp_tab.FIRST..emp_tab.LAST
INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);

END LOOP;
CLOSE emp_cur;
END;
/
Profile PL/SQL code for execution statistics

rem -----------------------------------------------------------------------
rem Filename: profiler.sql
rem Purpose: Profile PL/SQL code to get run-time statistics.
rem Shows execution time for each PL/SQL line. This data can
rem be used to improve performance.
rem Date: 02-Mar-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

conn / as sysdba

-- Install the profiler...
@?/rdbms/admin/proftab
@?/rdbms/admin/profload
@?/plsql/demo/profrep.sql

-- Create a test procedure to time...
CREATE OR REPLACE PROCEDURE proc1 IS
v_dummy CHAR;
BEGIN
FOR i IN 1..100 LOOP
SELECT dummy INTO v_dummy FROM dual;
END LOOP;
END;
/
SHOW ERRORS

-- Do the profilling and print the report...
set line 5000 serveroutput on size 1000000
DECLARE
v_run NUMBER;
BEGIN
DBMS_PROFILER.START_PROFILER('test','test1',v_run);
proc1;
DBMS_PROFILER.STOP_PROFILER;
DBMS_PROFILER.ROLLUP_RUN(v_run);
PROF_REPORT_UTILITIES.PRINT_RUN(v_run);
END;
/
Select records from a cursor into PL/SQL table
rem -----------------------------------------------------------------------
rem Filename: plstable.sql
rem Purpose: Example: how to populate a PL/SQL Table from a cursor
rem Date: 09-Apr-1999
rem Author: Frank Naude, Oracle FAQ
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;
/

Password encrypt/decrypt using DBMS Obfuscation Toolkit
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 Author: Frank Naude, Oracle FAQ
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;


Pass result sets (REF CURSOR) between procedures and functions
rem -----------------------------------------------------------------------
rem Filename: refcurs.sql
rem Purpose: Pass result sets (REF CURSOR) between procedures and
rem functions
rem Date: 15-Jun-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

-- Define TYPES package separately to be available to all programming
-- environments...
CREATE OR REPLACE PACKAGE types AS
TYPE cursortyp is REF CURSOR; -- use weak form
END;
/

-- Create test package to demonstrate passing result sets...
CREATE OR REPLACE PACKAGE test_ref_cursor AS
PROCEDURE main;
FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp;
PROCEDURE process_cursor(cur types.cursortyp);
END;
/
show errors


CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS

-- Main program entry point
PROCEDURE main IS
BEGIN
process_cursor( get_cursor_ref(1) );
process_cursor( get_cursor_ref(2) );
END;

-- Get and return a CURSOR REF/ Result Set
FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS
cur types.cursortyp;
BEGIN
if typ = 1 THEN
OPEN cur FOR SELECT * FROM emp WHERE ROWNUM < 5;
ELSE
OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5;
END IF;
RETURN cur;
END;

-- Process rows for an EMP or DEPT cursor
PROCEDURE process_cursor(cur types.cursortyp) IS
empRec emp%ROWTYPE;
deptRec dept%ROWTYPE;
BEGIN
LOOP
FETCH cur INTO empRec; -- Maybe it was an EMP cursor, try to fetch...
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('EMP ROW: '||empRec.ename);
END LOOP;
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN -- OK, so it was't EMP, let's try DEPT.
LOOP
FETCH cur INTO deptRec;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('DEPT ROW: '||deptRec.dname);
END LOOP;
END;

END;
/
show errors


EXEC test_ref_cursor.main;

Convert between different numbering systems (binary, octal, decimal and hex)
rem -----------------------------------------------------------------------
rem Filename: dbms_numsystem.sql
rem Purpose: Package with functions to convert numbers between the
rem Decimal, Binary, Octal and Hexidecimal numbering systems.
rem Usage: See sampels at the bottom of this file
rem Author: Frank Naude, 17 February 2003
rem -----------------------------------------------------------------------

set serveroutput on

CREATE OR REPLACE PACKAGE dbms_numsystem AS
function bin2dec (binval in char ) RETURN number;
function dec2bin (N in number) RETURN varchar2;
function oct2dec (octval in char ) RETURN number;
function dec2oct (N in number) RETURN varchar2;
function hex2dec (hexval in char ) RETURN number;
function dec2hex (N in number) RETURN varchar2;
END dbms_numsystem;
/
show errors

CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS

FUNCTION bin2dec (binval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(binval);
for i in 1..digits loop
current_digit := SUBSTR(binval, i, 1);
current_digit_dec := to_number(current_digit);
result := (result * 2) + current_digit_dec;
end loop;
return result;
END bin2dec;

FUNCTION dec2bin (N in number) RETURN varchar2 IS
binval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
binval := mod(N2, 2) || binval;
N2 := trunc( N2 / 2 );
end loop;
return binval;
END dec2bin;

FUNCTION oct2dec (octval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(octval);
for i in 1..digits loop
current_digit := SUBSTR(octval, i, 1);
current_digit_dec := to_number(current_digit);
result := (result * 8) + current_digit_dec;
end loop;
return result;
END oct2dec;

FUNCTION dec2oct (N in number) RETURN varchar2 IS
octval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
octval := mod(N2, 8) || octval;
N2 := trunc( N2 / 8 );
end loop;
return octval;
END dec2oct;

FUNCTION hex2dec (hexval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(hexval);
for i in 1..digits loop
current_digit := SUBSTR(hexval, i, 1);
if current_digit in ('A','B','C','D','E','F') then
current_digit_dec := ascii(current_digit) - ascii('A') + 10;
else
current_digit_dec := to_number(current_digit);
end if;
result := (result * 16) + current_digit_dec;
end loop;
return result;
END hex2dec;

FUNCTION dec2hex (N in number) RETURN varchar2 IS
hexval varchar2(64);
N2 number := N;
digit number;
hexdigit char;
BEGIN
while ( N2 > 0 ) loop
digit := mod(N2, 16);
if digit > 9 then
hexdigit := chr(ascii('A') + digit - 10);
else
hexdigit := to_char(digit);
end if;
hexval := hexdigit || hexval;
N2 := trunc( N2 / 16 );
end loop;
return hexval;
END dec2hex;

END dbms_numsystem;
/
show errors

-- Examples:
select dbms_numsystem.dec2bin(22) from dual;
select dbms_numsystem.bin2dec('10110') from dual;
select dbms_numsystem.dec2oct(44978) from dual;
select dbms_numsystem.oct2dec(127662) from dual;
select dbms_numsystem.dec2hex(44978) from dual;
select dbms_numsystem.hex2dec('AFB2') from dual;

Random number/ string generator package
/*
------------------------------------------------------------------------------
Filename: random.txt
Purpose: Random number/ string generator package
Author: Unknown
Original: http://orafaq.net/scripts/sql/random.txt
Edits:
19990908 Phil Rand Added functions rand_string(), smaller().
------------------------------------------------------------------------------
*/

create or replace package random
is
procedure srand(new_seed in number);
procedure get_rand(r OUT number);
procedure get_rand_max(r OUT number, n IN number);
function rand return number;
function rand_max(n IN number) return number;
function rand_string(ssiz IN number) return varchar2;
function smaller(x IN number, y IN number) return number;
pragma restrict_references(rand, WNDS);
pragma restrict_references(rand_max, WNDS);
pragma restrict_references(random, WNDS, RNPS);
pragma restrict_references(rand_string, WNDS);
pragma restrict_references(smaller, WNDS);
end random;
/

create or replace package body random
is
multiplier constant number := 22695477;
increment constant number := 1;
"2^32" constant number := 2 ** 32;
"2^16" constant number := 2 ** 16;
"0x7fff" constant number := 32767;
Seed number := 1;

function smaller(x IN number, y IN number) return number is
begin
if x <= y then
return x;
else
return y;
end if;
end smaller;

function rand_string(ssiz IN number) return varchar2 is
i number;
m number;
c char;
result varchar2(2000) := '';
begin
m := smaller(ssiz,2000);
for i in 1..m loop
c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
result := result || c;
end loop;
return result;
end rand_string;

procedure srand(new_seed in number) is
begin
Seed := new_seed;
end srand;

function rand return number is
begin
Seed := mod(multiplier * Seed + increment, "2^32");
return bitand(Seed/"2^16", "0x7fff");
end rand;

procedure get_rand(r OUT number) is
begin
r := rand;
end get_rand;

function rand_max(n IN number) return number is
begin
return mod(rand, n) + 1;
end rand_max;

procedure get_rand_max(r OUT number, n IN number) is
begin
r := rand_max(n);
end get_rand_max;

begin
select userenv('SESSIONID')
into Seed
from dual;
end random;
/

-- Some examples:
select random.rand_max(10) from dual;
select random.rand_max(10) from dual;
select random.rand_string(20) from dual;
select random.rand_string(20) from dual;

Function to test for Leap Years
rem -----------------------------------------------------------------------
rem Filename: leapyear.sql
rem Purpose: Check if a year is a leap year
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION isLeapYear(i_year NUMBER) RETURN boolean AS
BEGIN
-- A year is a leap year if it is evenly divisible by 4
-- but not if it's evenly divisible by 100
-- unless it's also evenly divisible by 400

IF mod(i_year, 400) = 0 OR ( mod(i_year, 4) = 0 AND mod(i_year, 100) != 0) THEN
return TRUE;
ELSE
return FALSE;
END IF;
END;
/
show errors

-- Let's test it
SET SERVEROUTPUT ON
BEGIN
IF isLeapYear(2004) THEN
dbms_output.put_line('Yes, it is a leap year');
ELSE
dbms_output.put_line('No, it is not a leap year');
END IF;
END;
/

• Print the ASCII table
rem -----------------------------------------------------------------------
rem Filename: asciitab.sql
rem Purpose: Print ASCII table
rem Date: 13-Jun-2000
rem Author: Anonymous
rem -----------------------------------------------------------------------

set serveroutput on size 10240

declare
i number;
j number;
k number;
begin
for i in 2..15 loop
for j in 1..16 loop
k:=i*16+j;
dbms_output.put((to_char(k,'000'))||':'||chr(k)||' ');
if k mod 8 = 0 then
dbms_output.put_line('');
end if;
end loop;
end loop;
end;
/
show errors


Recursive algorithms to calculate Fibonacci and Factorials


rem -----------------------------------------------------------------------
rem Filename: recurse.sql
rem Purpose: Script to demonstrate how recursive algorithms like
rem Fibonacci and Factorials can be implemented in PL/SQL
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- Computing the Factorial of a number (n!)
CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
/

-- Test n!
SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;

-- Sample output:
-- FAC(1) FAC(2) FAC(3) FAC(4) FAC(5)
-- ---------- ---------- ---------- ---------- ----------
-- 1 2 6 24 120


-- Computing the Nth Fibonacci number
CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN -- terminating condition
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2); -- recursive call
END IF;
END fib;
/

-- Test Fibonacci Series:
SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;

-- Sample output:
-- FIB(1) FIB(2) FIB(3) FIB(4) FIB(5)
-- ---------- ---------- ---------- ---------- ----------
-- 1 1 2 3 5
--


Fetch LOB column values piece-wise from PL/SQL
rem -----------------------------------------------------------------------
rem Filename: readlob.sql
rem Purpose: Fetch LOB column values piece-wise from PL/SQL
rem Date: 12-Jun-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

DROP TABLE lob_table; -- Create table to hols LOBs
CREATE TABLE lob_table (
id INTEGER,
b_lob BLOB,
c_lob CLOB,
b_file BFILE );

INSERT INTO lob_table -- Create sample record
VALUES (1, EMPTY_BLOB(), 'abcde', NULL);

DECLARE
clob_locator CLOB;
charbuf VARCHAR2(20);
read_offset INTEGER;
read_amount INTEGER;
BEGIN
-- First we need to get the lob locator
SELECT c_lob INTO clob_locator FROM lob_table WHERE id = 1;

DBMS_OUTPUT.PUT_LINE('CLOB Size: ' ||
DBMS_LOB.GETLENGTH(clob_locator));

-- Read LOB field contents
read_offset := 1;
read_amount := 20;
dbms_lob.read(clob_locator, read_amount, read_offset, charbuf);
dbms_output.put_line('CLOB Value: ' || charbuf);
END;
/

Upload and save binary files (like pictures, documents, etc) to/from the DB
rem -----------------------------------------------------------------------
rem Filename: loadlob.sql
rem Purpose: Load a binary file (images, documents, etc) into a
rem database table.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on
DROP TABLE lob_table;
DROP SEQUENCE lob_seq;

CREATE OR REPLACE DIRECTORY my_dir AS '/app/oracle/';

CREATE TABLE lob_table (id NUMBER, fil BLOB);
CREATE SEQUENCE lob_seq;

CREATE OR REPLACE PROCEDURE load_file(p_file VARCHAR2)
IS
src_lob BFILE := BFILENAME('MY_DIR', p_file);
dest_lob BLOB;
BEGIN
INSERT INTO lob_table VALUES(lob_seq.nextval, EMPTY_BLOB())
RETURNING fil INTO dest_lob;

DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
SRC_LOB => src_lob,
AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
DBMS_LOB.CLOSE(src_lob);

COMMIT;
END;
/
show errors

-- Let's test it
exec load_file('pic1.gif');
SELECT id, DBMS_LOB.GETLENGTH(fil) AS bytes_loaded
FROM lob_table;


rem -----------------------------------------------------------------------
rem Filename: savelob.sql
rem Purpose: Save a binary file (images, documents, etc) from database
rem to a flat file.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE save_file(p_id NUMBER, p_file VARCHAR2)
IS
v_lob_loc BLOB;
v_lob_len NUMBER;
v_buffer RAW(32767);
v_buffer_size BINARY_INTEGER := 32767;
v_offset NUMBER := 1;
v_out_file UTL_FILE.FILE_TYPE;
BEGIN
SELECT fil INTO v_lob_loc FROM lob_table WHERE id = p_id;
v_lob_len := DBMS_LOB.GETLENGTH(v_lob_loc);

DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);
v_out_file := UTL_FILE.FOPEN(location => 'MY_DIR',
filename => p_file,
open_mode => 'w',
max_linesize => 32767);

WHILE (v_offset <= v_lob_len) LOOP
dbms_output.put_line('v_start : ' || to_char(v_offset));
DBMS_LOB.READ(lob_loc => v_lob_loc,
amount => v_buffer_size,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_buffer_size;
UTL_FILE.PUT_RAW(file => v_out_file,
buffer => v_buffer);
END LOOP;

UTL_FILE.FCLOSE(v_out_file);
DBMS_LOB.CLOSE(v_lob_loc);
END;
/
show errors

-- Let's test it
exec save_file(1, 'pic2.gif');
! ls -l /app/oracle/pic*.gif


Fetch LONG column values piece-wise from PL/SQL
rem -----------------------------------------------------------------------
rem Filename: readlong.sql
rem Purpose: Fetch Long column values piece-wise from PL/SQL
rem Date: 12-Jan-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

-- Create test table
drop table longtable;
create table longtable (longcol long) tablespace TOOLS;
insert into longtable values ( rpad('x', 257, 'QWERTY') );

DECLARE
cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;;
rc NUMBER;
long_piece VARCHAR2(256);
piece_len INTEGER := 0;
long_tab DBMS_SQL.VARCHAR2S;
long_len INTEGER := 0;
BEGIN
DBMS_SQL.PARSE(cur1, 'select longcol from longtable', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1);
rc := DBMS_SQL.EXECUTE(cur1);
rc := DBMS_SQL.FETCH_ROWS(cur1); -- Get one row

-- Loop until all pieces of the long column are processed
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 256, long_len, long_piece, piece_len);
EXIT WHEN piece_len = 0;
DBMS_OUTPUT.PUT_LINE('Long piece len='|| piece_len);

long_tab( NVL(long_tab.LAST, 0)+1 ) := long_piece; -- Add piece to table
long_len := long_len + piece_len;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cur1);
DBMS_OUTPUT.PUT_LINE('Total long col fetched, len='|| long_len);
END;
/
Demonstrate writing to a file using the UTL_FILE package
rem -----------------------------------------------------------------------
rem Filename: utlfile.sql
rem Purpose: Demonstrate writing to a file using the UTL_FILE package
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE DIRECTORY test_dir AS 'c:\';
-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;
/
Map an external file to a database view
rem -----------------------------------------------------------------------
rem Filename: fileview.sql
rem Purpose: Create a database view on top of a file
rem Date: 27-Nov-2002
rem -----------------------------------------------------------------------

-- Utl_file_dir must be set to the directory you want to read from
show parameter utl_file_dir

-- Define the table's columns
CREATE OR REPLACE TYPE Alert_Row_Type AS OBJECT (
line NUMBER(8),
text VARCHAR2(2000)
);
/

-- Create a table of many row objects
CREATE OR REPLACE TYPE Alert_Type IS TABLE OF Alert_Row_Type;
/

-- Create a function to read the data into the table
CREATE OR REPLACE FUNCTION Get_Alert
RETURN Alert_Type
IS
Alert_Tab Alert_Type := Alert_Type(Alert_Row_Type(NULL, NULL));
v_file Utl_File.File_Type;
v_line NUMBER(10) := 1;
v_text VARCHAR2(2000);
b_read BOOLEAN := TRUE;
b_first BOOLEAN := TRUE;
BEGIN
dbms_output.put_line('About to open file...');
v_file := Utl_File.FOpen('/app/oracle/admin/orcl/bdump', 'alert_orcl.log', 'r');
WHILE b_read LOOP
BEGIN
Utl_File.Get_Line(v_file, v_text);
IF b_first THEN
b_first := FALSE;
ELSE
Alert_Tab.Extend;
END IF;

Alert_Tab(Alert_Tab.Last) := Alert_Row_Type(v_line, v_text);
v_line := v_line + 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
b_read := FALSE;
END;
END LOOP;
Utl_File.FClose(v_file);
RETURN Alert_Tab;
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.internal_error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.other_error');
END;
/
show errors

-- Create a view to get the info from the function
CREATE OR REPLACE FORCE VIEW alert_log_file AS
SELECT LINE, TEXT
FROM Table(Cast(Get_Alert() As Alert_Type))
/

-- Test it!!!
set pages 50000
select * from alert_log_file
where text like '%ORA-%'
/
• Demonstrate Dynamic SQL
rem -----------------------------------------------------------------------
rem Filename: dynasql.sql
rem Purpose: Example PL/SQL code to demonstrate Dynamic SQL
rem Date: 25-Feb-2003
rem Author: Frank Naude, Oracle FAQ
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
• Demonstrate Java stored procedures
rem -----------------------------------------------------------------------
rem Filename: java.sql
rem Purpose: Demonstrate Java stored procedures (available from Oracle 8i)
rem Date: 13-Jun-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

conn / as sysdba

-- @?/javavm/install/initjvm.sql
grant javauserpriv to scott;

conn scott/tiger

prompt Loading java source into database...

create or replace java source named "Hello" as
public class Hello { /* Pure Java Code */
static public String Msg(String tail) {
return "Hello " + tail;
}
}
/
-- SHOW ERRORS not needed
-- Note, you can also use "loadjava" to load source files into Oracle.

prompt Publish Java to PL/SQL...

create or replace function hello (str varchar2) return varchar as
language java name 'Hello.Msg(java.lang.String) return java.lang.String';
/
show errors

prompt Call Java function...

select hello('Frank') from dual
/

• Execute Operating System commands from PL/SQL (Java call)
rem -----------------------------------------------------------------------
rem Filename: oscmd.sql
rem Purpose: Execute operating system commands from PL/SQL
rem Notes: Specify full paths to commands, for example,
rem specify /usr/bin/ps instead of ps.
rem Date: 09-Apr-2005
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

rem -----------------------------------------------------------------------
rem Grant Java Access to user SCOTT
rem -----------------------------------------------------------------------

conn / as sysdba

EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.io.FilePermission', '/bin/sh', 'execute');
-- Other read ,write or execute permission may be requried

rem -----------------------------------------------------------------------
rem Create Java class to execute OS commands...
rem -----------------------------------------------------------------------

conn scott/tiger

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
} else { // Linux or Unix System
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}

// Execute the command...
final Process pr = Runtime.getRuntime().exec(finalCommand);

// Capture output from STDOUT...
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("stdout: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
} catch (IOException ioe) {
System.out.println("Error printing process output.");
ioe.printStackTrace();
} finally {
try {
br_in.close();
} catch (Exception ex) {}
}

// Capture output from STDERR...
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("stderr: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Error printing execution errors.");
ioe.printStackTrace();
} finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}

};
/
show errors

rem -----------------------------------------------------------------------
rem Publish the Java call to PL/SQL...
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE host (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
show errors

rem -----------------------------------------------------------------------
rem Let's test it...
rem -----------------------------------------------------------------------

CALL DBMS_JAVA.SET_OUTPUT(1000000);
SET SERVEROUTPUT ON SIZE 1000000
exec host('/usr/bin/ls');

FTP Client (GET and PUT files from PL/SQL)
rem -----------------------------------------------------------------------
rem Filename: ftpclient.sql
rem Purpose: PL/SQL FTP Client
rem Date: 19-Nov-2003
rem Author: Russ Johnson, Braun Consulting
rem -----------------------------------------------------------------------


CREATE OR REPLACE PACKAGE BRNC_FTP_PKG
AS
/**
*
* PL/SQL FTP Client
*
* Created by: Russ Johnson, Braun Consulting
*
* www.braunconsult.com
*
* OVERVIEW
* --------------------
* This package uses the standard packages UTL_FILE and UTL_TCP to perform
* client-side FTP functionality (PUT and GET) for text files as defined in
* the World Wide Web Consortium's RFC 959 document - http://www.w3.org/Protocols/rfc959/
* The procedures and functions in this package allow single or multiple file transfer using
* standard TCP/IP connections.
*
* LIMITATIONS
* --------------------
* Currently the API is limited to transfer of ASCII text files only. This is
* primarily because UTL_FILE only supports text I/O, but also because the original
* design was for creating text files from data in the Oracle database, then transferring the file to a remote host.
* Furthermore, the API does not support SSH/Secure FTP or connection through a proxy server.
* Keep in mind that FTP passes the username/password combo in plain text over TCP/IP.
*
* DB versions - 8i (8.1.x) and above. 8.0.x may work if it has the SYS.UTL_TCP package.
*
*
* Note: Since UTL_FILE is used for the client-side I/O, this package is also limited to
* transfer of files that exist in directories available to UTL_FILE for read/write.
* These directories are defined by the UTL_FILE_DIR parameter in the init.ora file.
*
* USAGE
* --------------------

* Three functions are available for FTP - PUT, GET, and FTP_MULTIPLE. FTP_MULTIPLE takes
* a table of records that define the files to be transferred (filename, directory, etc.).
* That table can have 1 record or multiple records. The PUT and GET functions are included
* for convenience to FTP one file at a time. PUT and GET return true if the file is transferred
* successfully and false if it fails. FTP_MULTIPLE returns true if no batch-level errors occur
* (such as an invalid host, refused connection, or invalid login information). It also takes the
* table of file records IN and passes it back OUT. Each record contains individual error information.
*
* EXAMPLE
* --------------------
* Transfer multiple files - 1 GET and 2 PUT from a Windows machine to a host (assuming UNIX here).
* Display any errors that occur.
* DECLARE
*
* v_username VARCHAR2(40) := 'rjohnson';
* v_password VARCHAR2(40) := 'password';
* v_hostname VARCHAR2(255) := 'ftp.oracle.com';
* v_error_message VARCHAR2(1000);
* b_put BOOLEAN;
* t_files BRNC_FTP_PKG.t_ftp_rec; -- Declare our table of file records
*
* BEGIN
*
* t_files(1).localpath := 'd:\oracle\utl_file\outbound';
* t_files(1).filename := 'myfile1.txt';
* t_files(1).remotepath := '/home/oracle/text_files';
* t_files(1).transfer_mode := 'PUT';
*
* t_files(2).localpath := 'd:\oracle\utl_file\inbound';
* t_files(2).filename := 'incoming_file.xml';
* t_files(2).remotepath := '/home/oracle/xml_files';
* t_files(2).transfer_mode := 'GET';
*
* t_files(3).localpath := 'd:\oracle\utl_file\outbound';
* t_files(3).filename := 'myfile2.txt';
* t_files(3).remotepath := '/home';
* t_files(3).transfer_mode := 'PUT';
*
* b_put := BRNC_FTP_PKG.FTP_MULTIPLE(v_error_message,
* t_files,
* v_username,
* v_password,
* v_hostname);
* IF b_put = TRUE
* THEN
* FOR i IN t_files.FIRST..t_files.LAST
* LOOP
* IF t_files.EXISTS(i)
* THEN
* DBMS_OUTPUT.PUT_LINE(t_files(i).status||' | '||
* t_files(i).error_message||' | '||
* to_char(t_files(i).bytes_transmitted)||' | '||
* to_char(t_files(i).trans_start,'YYYY-MM-DD HH:MI:SS')||' | '||
* to_char(t_files(i).trans_end,'YYYY-MM-DD HH:MI:SS'));
* END IF;
* END LOOP;
* ELSE
* DBMS_OUTPUT.PUT_LINE(v_error_message);
* END IF;
*
* EXCEPTION
* WHEN OTHERS
* THEN
* DBMS_OUTPUT.PUT_LINE(SQLERRM);
* END;
*
* CREDITS
* --------------------
* The W3C's RFC 959 that describes the FTP process.
*
* http://www.w3c.org
*
* Much of the PL/SQL code in this package was based on Java code written by
* Bruce Blackshaw of Enterprise Distributed Technologies Ltd. None of that code
* was copied, but the objects and methods greatly helped my understanding of the
* FTP Client process.
*
* http://www.enterprisedt.com
*
* VERSION HISTORY
* --------------------
* 1.0 11/19/2002 Unit-tested single and multiple transfers between disparate hosts.
*
*
*/


/**
* Exceptions
*
*/

ctrl_exception EXCEPTION;
data_exception EXCEPTION;

/**
* Constants - FTP valid response codes
*
*/

CONNECT_CODE CONSTANT PLS_INTEGER := 220;
USER_CODE CONSTANT PLS_INTEGER := 331;
LOGIN_CODE CONSTANT PLS_INTEGER := 230;
PWD_CODE CONSTANT PLS_INTEGER := 257;
PASV_CODE CONSTANT PLS_INTEGER := 227;
CWD_CODE CONSTANT PLS_INTEGER := 250;
TSFR_START_CODE1 CONSTANT PLS_INTEGER := 125;
TSFR_START_CODE2 CONSTANT PLS_INTEGER := 150;
TSFR_END_CODE CONSTANT PLS_INTEGER := 226;
QUIT_CODE CONSTANT PLS_INTEGER := 221;
SYST_CODE CONSTANT PLS_INTEGER := 215;
TYPE_CODE CONSTANT PLS_INTEGER := 200;

/**
* FTP File record datatype
*
* Elements:
* localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* filetype - reserved for future use, ignored in code
* transfer_mode - 'PUT' or 'GET'
* status - status of the transfer. 'ERROR' or 'SUCCESS'
* error_message - meaningful (hopefully) error message explaining the reason for failure
* bytes_transmitted - how many bytes were sent/received
* trans_start - date/time the transmission started
* trans_end - date/time the transmission ended
*
*/

TYPE r_ftp_rec IS RECORD(localpath VARCHAR2(255),
filename VARCHAR2(255),
remotepath VARCHAR2(255),
filetype VARCHAR2(20),
transfer_mode VARCHAR2(5),
status VARCHAR2(40),
error_message VARCHAR2(255),
bytes_transmitted NUMBER,
trans_start DATE,
trans_end DATE);

/**
* FTP File Table - used to store many files for transfer
*
*/

TYPE t_ftp_rec IS TABLE of r_ftp_rec INDEX BY BINARY_INTEGER;

/**
* Internal convenience procedure for creating passive host IP address
* and port number.
*
*/

PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2,
p_pasv_host OUT VARCHAR2,
p_pasv_port OUT NUMBER);

/**
* Function used to validate FTP server responses based on the
* code passed in p_code. Reads single or multi-line responses.
*
*/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN;

/**
* Function used to validate FTP server responses based on the
* code passed in p_code. Reads single or multi-line responses.
* Overloaded because some responses can have 2 valid codes.
*
*/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code1 IN PLS_INTEGER,
p_code2 IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN;

/**
* Procedure that handles the actual data transfer. Meant
* for internal package use. Returns information about the
* actual transfer.
*
*/

PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_pasv_host IN VARCHAR2,
p_pasv_port IN PLS_INTEGER,
p_transfer_mode IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE);

/**
* Function to handle FTP of many files.
* Returns TRUE if no batch-level errors occur.
* Returns FALSE if a batch-level error occurs.
*
* Parameters:
*
* p_error_msg - error message for batch level errors
* p_files - BRNC_FTP_PKG.t_ftp_rec table type. Accepts
* list of files to be transferred (may be any combination of PUT or GET)
* returns the table updated with transfer status, error message,
* bytes_transmitted, transmission start date/time and transmission end
* date/time
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - hostname or IP address of server Ex: 'ftp.oracle.com' or '127.0.0.1'
* p_port - port number to connect on. FTP is usually on 21, but this may be overridden
* if the server is configured differently.
*
*/

FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2,
p_files IN OUT t_ftp_rec,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
p_port IN PLS_INTEGER DEFAULT 21)
RETURN BOOLEAN;

/**
* Convenience function for single-file PUT
*
* Parameters:
* p_localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* p_filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* p_remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1'
* v_status - status of the transfer. 'ERROR' or 'SUCCESS'
* v_error_message - meaningful (hopefully) error message explaining the reason for failure
* n_bytes_transmitted - how many bytes were sent/received
* d_trans_start - date/time the transmission started
* d_trans_end - date/time the transmission ended
* p_port - port number to connect to, default is 21
* p_filetype - always set to 'ASCII', reserved for future use, ignored in code
*
*/

FUNCTION PUT(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN;

/**
* Convenience function for single-file GET
*
* Parameters:
* p_localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* p_filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* p_remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1'
* v_status - status of the transfer. 'ERROR' or 'SUCCESS'
* v_error_message - meaningful (hopefully) error message explaining the reason for failure
* n_bytes_transmitted - how many bytes were sent/received
* d_trans_start - date/time the transmission started
* d_trans_end - date/time the transmission ended
* p_port - port number to connect to, default is 21
* p_filetype - always set to 'ASCII', reserved for future use, ignored in code
*
*/

FUNCTION GET(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN;

END BRNC_FTP_PKG;
/
CREATE OR REPLACE PACKAGE BODY BRNC_FTP_PKG
AS

/*****************************************************************************
** Create the passive host IP and port number to connect to
**
*****************************************************************************/

PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2,
p_pasv_host OUT VARCHAR2,
p_pasv_port OUT NUMBER)
IS

v_pasv_cmd VARCHAR2(30) := p_pasv_cmd; --Host and port to connect to for data transfer
n_port_dec NUMBER;
n_port_add NUMBER;


BEGIN

p_pasv_host := REPLACE(SUBSTR(v_pasv_cmd,1,INSTR(v_pasv_cmd,',',1,4)-1),',','.');

n_port_dec := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,4)+1,(INSTR(v_pasv_cmd,',',1,5)-(INSTR(v_pasv_cmd,',',1,4)+1))));
n_port_add := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,5)+1,LENGTH(v_pasv_cmd)-INSTR(v_pasv_cmd,',',1,5)));

p_pasv_port := (n_port_dec*256) + n_port_add;


EXCEPTION
WHEN OTHERS
THEN
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;

END CREATE_PASV;

/*****************************************************************************
** Read a single or multi-line reply from the FTP server and validate
** it against the code passed in p_code.
**
** Return TRUE if reply code matches p_code, FALSE if it doesn't or error
** occurs
**
** Send full server response back to calling procedure
*****************************************************************************/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN
IS
n_code VARCHAR2(3) := p_code;
n_byte_count PLS_INTEGER;
v_msg VARCHAR2(255);
n_line_count PLS_INTEGER := 0;
BEGIN
LOOP
v_msg := UTL_TCP.GET_LINE(p_ctrl_con);
n_line_count := n_line_count + 1;
IF n_line_count = 1
THEN
p_reply := v_msg;
ELSE
p_reply := p_reply || SUBSTR(v_msg,4);
END IF;
EXIT WHEN INSTR(v_msg,'-',1,1) <> 4;
END LOOP;
IF to_number(SUBSTR(p_reply,1,3)) = n_code
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_reply := SQLERRM;
RETURN FALSE;
END VALIDATE_REPLY;

/*****************************************************************************
** Reads a single or multi-line reply from the FTP server
**
** Return TRUE if reply code matches p_code1 or p_code2,
** FALSE if it doesn't or error occurs
**
** Send full server response back to calling procedure
*****************************************************************************/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code1 IN PLS_INTEGER,
p_code2 IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN
IS
v_code1 VARCHAR2(3) := to_char(p_code1);
v_code2 VARCHAR2(3) := to_char(p_code2);
v_msg VARCHAR2(255);
n_line_count PLS_INTEGER := 0;
BEGIN
LOOP
v_msg := UTL_TCP.GET_LINE(p_ctrl_con);
n_line_count := n_line_count + 1;
IF n_line_count = 1
THEN
p_reply := v_msg;
ELSE
p_reply := p_reply || SUBSTR(v_msg,4);
END IF;
EXIT WHEN INSTR(v_msg,'-',1,1) <> 4;
END LOOP;
IF to_number(SUBSTR(p_reply,1,3)) IN(v_code1,v_code2)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_reply := SQLERRM;
RETURN FALSE;
END VALIDATE_REPLY;

/*****************************************************************************
** Handles actual data transfer. Responds with status, error message, and
** transfer statistics.
**
** Potential errors could be with connection or file i/o
**
*****************************************************************************/

PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_pasv_host IN VARCHAR2,
p_pasv_port IN PLS_INTEGER,
p_transfer_mode IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE)
IS
u_data_con UTL_TCP.CONNECTION;
u_filehandle UTL_FILE.FILE_TYPE;
v_tsfr_mode VARCHAR2(3) := p_transfer_mode;
v_mode VARCHAR2(1);
v_tsfr_cmd VARCHAR2(10);
v_buffer VARCHAR2(32767);
v_localpath VARCHAR2(255) := p_localpath;
v_filename VARCHAR2(255) := p_filename;
v_host VARCHAR2(20) := p_pasv_host;
n_port PLS_INTEGER := p_pasv_port;
n_bytes NUMBER;
v_msg VARCHAR2(255);
v_reply VARCHAR2(1000);
v_err_status VARCHAR2(20) := 'ERROR';

BEGIN

/** Initialize some of our OUT variables **/

v_status := 'SUCCESS';
v_error_message := ' ';
n_bytes_transmitted := 0;

IF UPPER(v_tsfr_mode) = 'PUT'
THEN
v_mode := 'r';
v_tsfr_cmd := 'STOR ';

ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
v_mode := 'w';
v_tsfr_cmd := 'RETR ';
END IF;

/** Open data connection on Passive host and port **/

u_data_con := UTL_TCP.OPEN_CONNECTION(v_host,n_port);

/** Open the local file to read and transfer data **/

u_filehandle := UTL_FILE.FOPEN(v_localpath,v_filename,v_mode);

/** Send the STOR command to tell the server we're going to upload a file **/

n_bytes := UTL_TCP.WRITE_LINE(u_ctrl_con,v_tsfr_cmd||v_filename);
IF VALIDATE_REPLY(u_ctrl_con,TSFR_START_CODE1,TSFR_START_CODE2,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

d_trans_start := SYSDATE;

IF UPPER(v_tsfr_mode) = 'PUT'
THEN
LOOP
BEGIN
UTL_FILE.GET_LINE(u_filehandle,v_buffer);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;

n_bytes := UTL_TCP.WRITE_LINE(u_data_con,v_buffer);
n_bytes_transmitted := n_bytes_transmitted + n_bytes;

END LOOP;

ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
LOOP
BEGIN
v_buffer := UTL_TCP.GET_LINE(u_data_con,TRUE);

/** Sometimes the TCP/IP buffer sends null data **/
/** we only want to receive the actual data **/

IF v_buffer IS NOT NULL
THEN
UTL_FILE.PUT_LINE(u_filehandle,v_buffer);
n_bytes := LENGTH(v_buffer);
n_bytes_transmitted := n_bytes_transmitted + n_bytes;
END IF;


EXCEPTION
WHEN UTL_TCP.END_OF_INPUT
THEN
EXIT;
END;

END LOOP;

END IF;

/** Flush the buffer on the data connection **/

--UTL_TCP.FLUSH(u_data_con);

d_trans_end := SYSDATE;

/** Close the file **/

UTL_FILE.FCLOSE(u_filehandle);

/** Close the Data Connection **/

UTL_TCP.CLOSE_CONNECTION(u_data_con);

/** Verify the transfer succeeded **/

IF VALIDATE_REPLY(u_ctrl_con,TSFR_END_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

EXCEPTION
WHEN ctrl_exception
THEN
v_status := v_err_status;
v_error_message := v_reply;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.invalid_path
THEN
v_status := v_err_status;
v_error_message := 'Directory '||v_localpath||' is not available to UTL_FILE. Check the init.ora file for valid UTL_FILE directories.';
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.invalid_operation
THEN
v_status := v_err_status;

IF UPPER(v_tsfr_mode) = 'PUT'
THEN
v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for reading.';

ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for writing.';

END IF;

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.read_error
THEN
v_status := v_err_status;
v_error_message := 'The system encountered an error while trying to read '||v_filename||' in the directory '||v_localpath;

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.write_error
THEN
v_status := v_err_status;
v_error_message := 'The system encountered an error while trying to write to '||v_filename||' in the directory '||v_localpath;

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.internal_error
THEN
v_status := v_err_status;
v_error_message := 'The UTL_FILE package encountered an unexpected internal system error.';

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN OTHERS
THEN
v_status := v_err_status;
v_error_message := SQLERRM;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
END TRANSFER_ASCII;

/*****************************************************************************
** Handles connection to host and FTP of multiple files
** Files can be any combination of PUT and GET
**
*****************************************************************************/

FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2,
p_files IN OUT t_ftp_rec,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
p_port IN PLS_INTEGER DEFAULT 21)
RETURN BOOLEAN
IS
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(30) := p_password;
v_hostname VARCHAR2(30) := p_hostname;
n_port PLS_INTEGER := p_port;
u_ctrl_con UTL_TCP.CONNECTION;
n_byte_count PLS_INTEGER;
n_first_index NUMBER;
v_msg VARCHAR2(250);
v_reply VARCHAR2(1000);
v_pasv_host VARCHAR2(20);
n_pasv_port NUMBER;

invalid_transfer EXCEPTION;
BEGIN

p_error_msg := 'FTP Successful'; --Assume the overall transfer will succeed

/** Attempt to connect to the host machine **/

u_ctrl_con := UTL_TCP.OPEN_CONNECTION(v_hostname,n_port);
IF VALIDATE_REPLY(u_ctrl_con,CONNECT_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Send username **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'USER '||v_username);
IF VALIDATE_REPLY(u_ctrl_con,USER_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Send password **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASS '||v_password);
IF VALIDATE_REPLY(u_ctrl_con,LOGIN_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** We should be logged in, time to transfer all files **/

FOR i IN p_files.FIRST..p_files.LAST
LOOP
IF p_files.EXISTS(i)
THEN
BEGIN

/** Change to the remotepath directory **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'CWD '||p_files(i).remotepath);
IF VALIDATE_REPLY(u_ctrl_con,CWD_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Switch to IMAGE mode **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'TYPE I');
IF VALIDATE_REPLY(u_ctrl_con,TYPE_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Get a Passive connection to use for data transfer **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASV');
IF VALIDATE_REPLY(u_ctrl_con,PASV_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

CREATE_PASV(SUBSTR(v_reply,INSTR(v_reply,'(',1,1)+1,INSTR(v_reply,')',1,1)-INSTR(v_reply,'(',1,1)-1),v_pasv_host,n_pasv_port);

/** Transfer Data **/

IF UPPER(p_files(i).transfer_mode) = 'PUT'
THEN
TRANSFER_ASCII(u_ctrl_con,
p_files(i).localpath,
p_files(i).filename,
v_pasv_host,
n_pasv_port,
p_files(i).transfer_mode,
p_files(i).status,
p_files(i).error_message,
p_files(i).bytes_transmitted,
p_files(i).trans_start,
p_files(i).trans_end);

ELSIF UPPER(p_files(i).transfer_mode) = 'GET'
THEN
TRANSFER_ASCII(u_ctrl_con,
p_files(i).localpath,
p_files(i).filename,
v_pasv_host,
n_pasv_port,
p_files(i).transfer_mode,
p_files(i).status,
p_files(i).error_message,
p_files(i).bytes_transmitted,
p_files(i).trans_start,
p_files(i).trans_end);
ELSE
RAISE invalid_transfer; -- Raise an exception here
END IF;

EXCEPTION
WHEN ctrl_exception
THEN
p_files(i).status := 'ERROR';
p_files(i).error_message := v_reply;

WHEN invalid_transfer
THEN
p_files(i).status := 'ERROR';
p_files(i).error_message := 'Invalid transfer method. Use PUT or GET.';

END;
END IF;
END LOOP;

/** Send QUIT command **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'QUIT');

/** Don't need to validate QUIT, just close the connection **/

UTL_TCP.CLOSE_CONNECTION(u_ctrl_con);
RETURN TRUE;

EXCEPTION
WHEN ctrl_exception
THEN
p_error_msg := v_reply;
UTL_TCP.CLOSE_ALL_CONNECTIONS;
RETURN FALSE;
WHEN OTHERS
THEN
p_error_msg := SQLERRM;
UTL_TCP.CLOSE_ALL_CONNECTIONS;
RETURN FALSE;
END FTP_MULTIPLE;

/*****************************************************************************
** Convenience function for single-file PUT
** Formats file information for FTP_MULTIPLE function and calls it.
**
*****************************************************************************/

FUNCTION PUT(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN
IS
t_files t_ftp_rec;
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(50) := p_password;
v_hostname VARCHAR2(100) := p_hostname;
n_port PLS_INTEGER := p_port;
v_err_msg VARCHAR2(255);
b_ftp BOOLEAN;
BEGIN
t_files(1).localpath := p_localpath;
t_files(1).filename := p_filename;
t_files(1).remotepath := p_remotepath;
t_files(1).filetype := p_filetype;
t_files(1).transfer_mode := 'PUT';

b_ftp := FTP_MULTIPLE(v_err_msg,
t_files,
v_username,
v_password,
v_hostname,
n_port);
IF b_ftp = FALSE
THEN
v_status := 'ERROR';
v_error_message := v_err_msg;
RETURN FALSE;
ELSIF b_ftp = TRUE
THEN
v_status := t_files(1).status;
v_error_message := t_files(1).error_message;
n_bytes_transmitted := t_files(1).bytes_transmitted;
d_trans_start := t_files(1).trans_start;
d_trans_end := t_files(1).trans_end;
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_status := 'ERROR';
v_error_message := SQLERRM;
RETURN FALSE;
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END PUT;

/*****************************************************************************
** Convenience function for single-file GET
** Formats file information for FTP_MULTIPLE function and calls it.
**
*****************************************************************************/

FUNCTION GET(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN
IS
t_files t_ftp_rec;
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(50) := p_password;
v_hostname VARCHAR2(100) := p_hostname;
n_port PLS_INTEGER := p_port;
v_err_msg VARCHAR2(255);
b_ftp BOOLEAN;
BEGIN
t_files(1).localpath := p_localpath;
t_files(1).filename := p_filename;
t_files(1).remotepath := p_remotepath;
t_files(1).filetype := p_filetype;
t_files(1).transfer_mode := 'GET';

b_ftp := FTP_MULTIPLE(v_err_msg,
t_files,
v_username,
v_password,
v_hostname,
n_port);
IF b_ftp = FALSE
THEN
v_status := 'ERROR';
v_error_message := v_err_msg;
RETURN FALSE;
ELSIF b_ftp = TRUE
THEN
v_status := t_files(1).status;
v_error_message := t_files(1).error_message;
n_bytes_transmitted := t_files(1).bytes_transmitted;
d_trans_start := t_files(1).trans_start;
d_trans_end := t_files(1).trans_end;
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_status := 'ERROR';
v_error_message := SQLERRM;
RETURN FALSE;
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END GET;

END BRNC_FTP_PKG;
/

Send e-mail messages from PL/SQL
rem -----------------------------------------------------------------------
rem Filename: smtp.sql
rem Purpose: Send e-mail messages 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 Pont the IP Address to your local SMTP (Simple Mail
rem Transport) Server. No pipes or external procedures are
rem required.
rem Date: 27-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, 'Unable to send e-mail message from pl/sql because of: '||
sqlerrm);
END;
/
show errors

-- Examples:
set serveroutput on

exec send_mail(msg_to =>'you@yourdomain.com');

exec send_mail(msg_to =>'you@yourdomain.com', -
msg_text=>'Look Ma, I can send mail from plsql' -
);
• Send e-mail messages from PL/SQL with MIME attachments

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 Author: Virgilio Nunes (Virgilio@logtek.co.za)
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;
/

Mailmerge: Merge data from the table/view data sources into a custom template

rem -----------------------------------------------------------------------
rem Filename: mailmerge.sql
rem Purpose: Merge data from the table/view data sources into a custom
rem template.
rem Date: 30 Dec 2004
rem Author: Claudiu Ariton (aritonc@yahoo.com)
rem
rem Package detail:
rem TEMP_MAIL_MERGE - temporary table to store the result
rem DATA_SOURCES - data sources table
rem mail_merge procedure - Merge data from list of values into template
rem generic_mail_merge - Merge data from a table/view into template
rem
rem To define a template use $n where n is the position of a field in
rem data_sources_v view (escape character is $).
rem
rem E.G.: $2 $3 born on $4 has to pay $$$5. Call us at $6.
rem -----------------------------------------------------------------------


/*************************** Create result temp table, test table and context *************************/

create GLOBAL TEMPORARY table TEMP_MAIL_MERGE
(
PKID NUMBER ,
RESULT VARCHAR2(4000) )
On Commit Delete Rows
/

create table data_sources
(ID Number(10) primary key,
P2 Varchar2(10),
P3 Varchar2(50),
P4 DATE,
P5 NUMBER
)
/

Insert into DATA_SOURCES
(ID, P2, P3, P4, P5)
Values
(1, 'Claudiu', 'Ariton', TO_DATE('09/21/1976 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100000)
/
Commit
/

create or replace context mm_ctx using mail_merge
/

create or replace view data_sources_v as
select ID PKID, P2, P3, P4, P5,
SYS_CONTEXT('mm_ctx','param1',2000) param1 from
data_sources
/


/*************************** Create package head *************************/

Create or Replace Package mail_merge as

/**************************************************************************************
*
* TITLE......: Mail Merge
* DESCRIPTION: Merge data from the data source into a custom template
*
* AUTHOR.....: Claudiu Ariton
* DATE.......: april 2004
*
* Modifications
*
**************************************************************************************/

type Argv is table of varchar2(4000) index by binary_integer;
emptyargv argv;

procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx' );

function mail_merge(
p_message in varchar2,
p_argv in argv ,
p_esc_char in varchar2 default '$') return varchar2;

Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv);

end mail_merge;
/

/*************************** Create package body *************************/

Create or Replace Package Body mail_merge as

-- -----------------------------------------------------------------------------------
-- Set_ctx - set generic context
-- -----------------------------------------------------------------------------------
procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx' )
as

begin
dbms_session.set_context( p_ctx, p_name, p_value, USER);
end;

-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from list of vaalues into a custom template
-- -----------------------------------------------------------------------------------
function mail_merge(
p_message in varchar2,
p_argv in argv ,
p_esc_char in varchar2 default '$') return varchar2 is
--
l_message long := null;
l_str long := p_message;
l_idx number := 1;
l_ptr number := 1;
l_poz varchar2(10);
l_on number;
begin
if nvl( instr( p_message, p_esc_char ), 0 ) = 0 and
nvl( instr( p_message, '\' ), 0 ) = 0 then
return p_message;
end if;
loop

l_on:=0;
l_ptr := instr( l_str, p_esc_char );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );

l_poz:=null;
while substr( l_str, 1, 1 ) in ('0','1','2','3','4','5','6','7','8','9') loop
l_poz:=l_poz||substr( l_str, 1, 1 );
l_str := substr( l_str, 2 );
l_on:=1;
end loop;

begin
l_message := l_message || p_argv(to_number(l_poz));
exception
when no_data_found then l_message := l_message || '';
when others then null;
end;

if (substr( l_str,1,1 ) = p_esc_char) and (l_on=0) then
l_message := l_message || p_esc_char;
l_str := substr( l_str, 2 );
end if;

end loop;

l_str := l_message || l_str;
l_message := null;
loop
l_ptr := instr( l_str, '\' );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );
if substr( l_str, 1, 1 ) = 'n' then
l_message := l_message || chr(10);
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = 't' then
l_message := l_message || chr(9);
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = '\' then
l_message := l_message || '\';
l_str := substr( l_str, 2 );
else
l_message := l_message || '\';
end if;
end loop;

return l_message || l_str;
end mail_merge;

-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from a table/viiew into a custom template
-- -----------------------------------------------------------------------------------
Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv)
is
l_theCursor integer default dbms_sql.open_cursor;
l_defcolumn varchar2(4000);
l_columnValue argv;
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
v_result varchar2(4000);
v_pkid number;
begin
execute immediate
'alter session set
nls_date_format='''|| p_date_format ||'''';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

if p_bindid is not null then
dbms_sql.bind_variable( l_theCursor, ':1', p_bindid );
end if;

dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

-- seteaza context
begin
for i in 1..p_list_val.count loop
set_ctx('param'||to_char(i),p_list_val(i));
end loop;
exception
when others then null;
end;

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_defcolumn, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue(i) );
if upper(l_descTbl(i).col_name)='PKID' then
v_pkid:=to_number(l_columnValue(i));
end if;
end loop;

v_result:=mail_merge( p_ttext,l_columnValue);
insert into TEMP_MAIL_MERGE(pkid,result) values (v_pkid,v_result);
end loop;
dbms_sql.close_cursor( l_theCursor);
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
if dbms_sql.is_open(l_theCursor) then
dbms_sql.close_cursor( l_theCursor);
end if;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;

end mail_merge;
/

/*************************** Test it *************************/
declare
v_list_val mail_merge.argv;
v_query varchar2(4000);
begin
v_list_val(1):='(040)-2313543';
v_query:= 'select * from data_sources_v vt where 1=1'||' AND vt.pkid=:1';
mail_merge.generic_mail_merge('$2 $3 born on $4 has to pay $$$5. Call us at $6. Thank you very much.',
v_query,'dd-MON-yyyy',1,v_list_val);
end;
/

select * from temp_mail_merge
/

• Read an Internet Web pages from PL/SQL
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 Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000

select utl_http.request('http://www.orafaq.net/') from dual;

Track DLL changes (create, drop, alter) within a schema

rem -----------------------------------------------------------------------
rem Filename: auditdll.sql
rem Purpose: Maintain an audit log of DDL changes (alter/ drop/ create)
rem within a schema
rem Date: 15-Feb-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

DROP TRIGGER audit_ddl_changes
/
DROP TABLE dll_audit_log
/

CREATE TABLE dll_audit_log (
stamp DATE,
username VARCHAR2(30),
osuser VARCHAR2(30),
machine VARCHAR2(30),
terminal VARCHAR2(30),
operation VARCHAR2(30),
objtype VARCHAR2(30),
objname VARCHAR2(30))
/

CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER create OR drop OR alter
ON scott.SCHEMA -- Change SCOTT to your schema name!!!
-- ON DATABASE
BEGIN
INSERT INTO dll_audit_log VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/
show errors


-- Now, let's test it
CREATE TABLE my_test_table (col1 DATE)
/
DROP TABLE my_test_table
/
set pages 50000
SELECT * FROM dll_audit_log
/

Count the number of rows in ALL tables for the current schema

rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in current schema
rem using PL/SQL
rem Date: 15-Apr-2000
rem Author: Eberhardt, Roberto (Bolton) (reberhar@husky.ca)
rem -----------------------------------------------------------------------

set serveroutput on size 1000000

DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with more rows
cursor c1 is select table_name from user_tables order by table_name;
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||
to_char(t_total_records,'99999999')||' record(s)');

end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/
List tables from schema with more than X rows
rem -----------------------------------------------------------------------
rem Filename: countall2.sql
rem Purpose: List tables from schema with more than X rows
rem Date: 15-Sep-2005
rem Author: Praveen Kumar Chugh
rem -----------------------------------------------------------------------

REM First of all create the following function - rowcount...

CREATE OR REPLACE FUNCTION rowcount(tname VARCHAR2) RETURN NUMBER IS
x NUMBER;
stmt VARCHAR2(200);
BEGIN
stmt := 'select count(*) from '||tname;
execute immediate stmt into x;
return x;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
/
SHOW ERRORS

REM Then write this query...

SELECT table_name, roucount(table_name) Records
FROM cat
WHERE roucount(table_name) >= 100;
/

Replace all occurences of a substring with another substring
rem -----------------------------------------------------------------------
rem Filename: strreplace.sql
rem Purpose: Replace all occurences of a substring with another substring
rem Date: 28-Jul-2003
rem Author: Frank Naude, Oracle FAQ
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
/

Spell out numbers to words (handy for cheque printing)
rem -----------------------------------------------------------------------
rem Filename: cheqprint.sql
rem Purpose: This function will convert a number to words, handy for
rem ptinting cheques.
rem Notes: The number before the decimal should be between 1..5373484
rem Date: 24-Feb-2003
rem Author: Sebastian Thomas, sebastianthomas@rediffmail.com
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION NUMBER_CONVERSION(NUM NUMBER) RETURN VARCHAR2
IS
A VARCHAR2(1000);
B VARCHAR2(20);
X NUMBER;
Y NUMBER := 1;
Z NUMBER;
LSIGN NUMBER;
NO NUMBER;
BEGIN
X:= INSTR(NUM, '.');
LSIGN := SIGN(NUM);
NO := ABS(NUM);
IF X = 0 THEN
SELECT TO_CHAR(TO_DATE(NO, 'J'), 'JSP') INTO A FROM DUAL;
ELSE
SELECT to_char(to_date(SUBSTR(NO, 1,
NVL(INSTR(NO, '.')-1, LENGTH(NO))),
'J'), 'JSP') INTO A FROM DUAL;
SELECT LENGTH(SUBSTR(NO, INSTR(NO, '.')+1)) INTO Z FROM DUAL;
A := A ||' POINT ';
WHILE Y< Z+1 LOOP
SELECT TO_CHAR(TO_DATE(SUBSTR(NO, (INSTR(NO, '.')+Y), 1), 'J'), 'JSP')
INTO B FROM DUAL;
A := A || B ||' ';
y :=y+1;
END LOOP;
END IF;
IF LSIGN = -1 THEN
RETURN 'NEGATIVE '||A;
ELSE
RETURN A;
END IF;
END;
/
show errors

-- Examples:
Select number_conversion(-3786.9899876) from dual;
Select number_conversion(7685.78788) from dual;
Select number_conversion(7678) from dual;

Print cheque amounts in Indian Style
rem -----------------------------------------------------------------------
rem Filename: indicheq.sql
rem Purpose: This procedure will convert numbers to chars for printing
rem cheques amount in Indian Style. It will print amount from
rem Rs1 to Rs.989999999.
rem Note: In order to see your output in SQL*Plus, set serverout on
rem Date: 22-Feb-2003
rem Author: Birender Kumar, bir_canada@yahoo.com
rem Updateded: 04-Aug-2003
rem Updated By: marine, marine00072003@yahoo.com
rem -----------------------------------------------------------------------

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE cheq(n NUMBER)
as
L NUMBER := 0;
OUTPUT VARCHAR2(2000) := '';
X VARCHAR2(2000) := '';
X1 VARCHAR2(2000) := '';
C1 VARCHAR2(2000) := '';
BEGIN
L := length(N);

IF N < 0 OR N > 999999999 THEN
DBMS_OUTPUT.PUT_LINE('INVALID AMOUNT');
else

if ( N = 0 ) THEN
X := 'ZERO ';
elsif ( N <= 99999 ) THEN
X := to_char(to_date(N,'J'),'JSP') || ' ';
else

if ( to_number(substr(N, L - 5 + 1)) = 0 ) then
X := '';
else
X := to_char(to_date(to_number(substr(N, L - 5 + 1)),'J'),'JSP') || ' ';
end if;

if ( L = 6 ) then
X1 := to_char(to_date(to_number(substr(N, 1, L - 5)),'J'),'JSP') || ' LAKH ';
else
if ( to_number(substr(N, L - 5 -1, 2)) = 0 ) then
X1 := '';
else
X1 := to_char(to_date(to_number(substr(N, L - 5 - 1, 2)),'J'),'JSP') || ' LAKH ';
end if;

if ( L >= 8 ) then
C1 := to_char(to_date(to_number(substr(N, 1, L-7)),'J'),'JSP')||' CRORE ';
end if;
end if;
end if;

if ( N = 0 OR N = 1 ) THEN
DBMS_OUTPUT.PUT_LINE(N||' => '||X||'RUPEE ONLY');
else
DBMS_OUTPUT.PUT_LINE(N||' => '||C1||X1||X||'RUPEES ONLY');
end if;
end if;
END CHEQ;
/
show errors

NYSIIS function (an improvement on SoundeX)
rem -----------------------------------------------------------------------
rem Filename: nysiis.pls
rem Purpose: NYSIIS function (an improvement on soundex)
rem Notes: Convert a name to a phonetic coding of up to six characters
rem Date: 19-Jul-2004
rem Author: Trevor Fairhurst, trevgf@yahoo.com
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION NYSIIS (v_text IN VARCHAR2) RETURN VARCHAR2 is

v_sub varchar2(300);
v_length number(10);
v_textin varchar2(300);
v_key varchar2(1);

begin
SELECT UPPER ( v_text ) into v_textin from dual;
dbms_output.put_line( 'Entered surname :' || v_textin);

dbms_output.put_line( ' [1] remove all S and Z chars from the end of the surname ' );
LOOP
SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
dbms_output.put_line('Last letter :' || v_sub);
if v_sub = 'S' OR v_sub = 'Z' THEN
SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
dbms_output.put_line('As last letter s or z drop last letter giving :' || v_textin || ' and check new last letter');
else
dbms_output.put_line('Last letter not s or z completed step 1');
EXIT;
end if;
END LOOP;
dbms_output.put_line('Step 1 completed giving :' || v_textin );

dbms_output.put_line( ' [2] transcode initial strings MAC => MC and PF => F and PH => F ' );
if SUBSTR ( v_textin , 1 , 3 ) = 'MAC' THEN
SELECT 'MC' || SUBSTR ( v_textin , 4 ) into v_textin from dual ;
elsif SUBSTR ( v_textin , 1 , 2 ) = 'PH' THEN
SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
elsif SUBSTR ( v_textin , 1 , 2 ) = 'PF' THEN
SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
end if;

dbms_output.put_line('Step 2 completed giving :' || v_textin );

dbms_output.put_line( ' [3] transcode trailing strings IX => IC and EX => EC and YE,EE,IE => Y and NT,ND => D ' );
SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
dbms_output.put_line('Last 2 letters :' || v_sub);
if UPPER(v_sub) in ('IX','EX','YE','EE','IE','NT','ND') THEN
SELECT decode ( UPPER(v_sub) , 'IX','IC',
'EX','EC',
'YE','Y',
'EE','Y',
'IE','Y',
'NT','D',
'ND','D', NULL ) into v_sub from dual ;
SELECT SUBSTR ( v_textin , 1, (length (v_textin) - 1)) || v_sub into v_textin from dual ;
end if;
dbms_output.put_line('Step 3 completed giving :' || v_textin );

dbms_output.put_line( '[4] transcode EV to EF if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'EV','EF') into v_textin from dual ;
dbms_output.put_line('Step 4 completed giving :' || v_textin );

dbms_output.put_line( '[5] first character of name as first character of key continue with remaining characters');
SELECT SUBSTR ( v_textin , 1 , 1) into v_key from dual;
SELECT SUBSTR ( v_textin , 2 ) into v_textin from dual;
dbms_output.put_line('Step 5 completed first character of key :' || v_key );

dbms_output.put_line( '[6] replace all vowels with A');
SELECT TRANSLATE( v_textin,'AEIOU','AAAAA') into v_textin from dual ;
dbms_output.put_line('Step 6 completed giving :' || v_textin );

dbms_output.put_line( '[7] remove any W that follows a vowel');
SELECT REPLACE( v_textin,'AW','A') into v_textin from dual ;
dbms_output.put_line('Step 7 completed giving :' || v_textin );

dbms_output.put_line( '[8] transcode GHT to GT ');
SELECT REPLACE( v_textin,'GHT','GT') into v_textin from dual ;
dbms_output.put_line('Step 8 completed giving :' || v_textin );

dbms_output.put_line( '[9] transcode DG to G ');
SELECT REPLACE( v_textin,'DG','G') into v_textin from dual ;
dbms_output.put_line('Step 9 completed giving :' || v_textin );

dbms_output.put_line( '[10] transcode PH to F ');
SELECT REPLACE( v_textin,'PH','F') into v_textin from dual ;
dbms_output.put_line('Step 10 completed giving :' || v_textin );

dbms_output.put_line( '[11] if not first character, eliminate all H preceded or followed by a vowel ');
if length(v_textin) > 1 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'HA','A') into v_textin from dual ;
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'AH','A') into v_textin from dual ;
end if;
dbms_output.put_line('Step 11 completed giving :' || v_textin );

dbms_output.put_line( '[12] change KN to N, else K to C ');
SELECT REPLACE( v_textin,'KN','N') into v_textin from dual ;
SELECT TRANSLATE( v_textin,'K','C') into v_textin from dual ;
dbms_output.put_line( 'Step 12 completed giving :' || v_textin );

dbms_output.put_line( '[13] transcode M to N if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'M','N') into v_textin from dual ;
dbms_output.put_line('Step 13 completed giving :' || v_textin );

dbms_output.put_line( '[14] transcode Q to G if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Q','G') into v_textin from dual ;
dbms_output.put_line('Step 14 completed giving :' || v_textin );

dbms_output.put_line( '[15] transcode transcode SH to S ');
SELECT REPLACE( v_textin,'SH','S') into v_textin from dual ;
dbms_output.put_line('Step 15 completed giving :' || v_textin );

dbms_output.put_line( '[16] transcode transcode SCH to S ');
SELECT REPLACE( v_textin,'SCH','S') into v_textin from dual ;
dbms_output.put_line('Step 16 completed giving :' || v_textin );

dbms_output.put_line( '[17] transcode transcode YW to Y ');
SELECT REPLACE( v_textin,'YW','Y') into v_textin from dual ;
dbms_output.put_line('Step 17 completed giving :' || v_textin );

dbms_output.put_line( '[18] if not first or last character, change Y to A');
if length(v_textin) > 2 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2, length(v_textin) - 2),'Y','A') || SUBSTR ( v_textin , (length (v_textin))) into v_textin from dual ;
end if;
dbms_output.put_line('Step 18 completed giving :' || v_textin );

dbms_output.put_line( '[19] transcode transcode WR to R ');
SELECT REPLACE( v_textin,'WR','R') into v_textin from dual ;
dbms_output.put_line('Step 19 completed giving :' || v_textin );

dbms_output.put_line( '[20] if not first character, change Z to S ');
if length(v_textin) > 1 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Z','S') into v_textin from dual ;
end if;
dbms_output.put_line('Step 20 completed giving :' || v_textin );

dbms_output.put_line( '[21] transcode terminal AY to Y');
if length(v_textin) > 1 THEN
SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
if v_sub = 'AY' THEN
SELECT SUBSTR(v_textin, 1 , length(v_textin) - 2 ) || 'Y' into v_textin from dual ;
end if;
end if;
dbms_output.put_line('Step 21 completed giving :' || v_textin );

dbms_output.put_line( '[22] remove trailing vowels');
LOOP
SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
dbms_output.put_line('Last letter :' || v_sub);
if v_sub = 'A' THEN
SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
dbms_output.put_line('As last letter A drop last letter giving :' || v_textin || ' and check new last letter');
else
dbms_output.put_line('Last letter not A step 22 completed');
EXIT;
end if;
END LOOP;
dbms_output.put_line('Step 22 completed giving :' || v_textin );

dbms_output.put_line( '[23] collapse all strings of repeated characters');
if length(v_textin) > 2 THEN
LOOP
SELECT v_textin into v_sub from dual;
SELECT REPLACE( v_textin,'AA','A') into v_textin from dual ;
SELECT REPLACE( v_textin,'BB','B') into v_textin from dual ;
SELECT REPLACE( v_textin,'CC','C') into v_textin from dual ;
SELECT REPLACE( v_textin,'DD','D') into v_textin from dual ;
SELECT REPLACE( v_textin,'FF','F') into v_textin from dual ;
SELECT REPLACE( v_textin,'GG','G') into v_textin from dual ;
SELECT REPLACE( v_textin,'HH','H') into v_textin from dual ;
SELECT REPLACE( v_textin,'JJ','J') into v_textin from dual ;
SELECT REPLACE( v_textin,'LL','L') into v_textin from dual ;
SELECT REPLACE( v_textin,'NN','N') into v_textin from dual ;
SELECT REPLACE( v_textin,'PP','P') into v_textin from dual ;
SELECT REPLACE( v_textin,'RR','R') into v_textin from dual ;
SELECT REPLACE( v_textin,'SS','S') into v_textin from dual ;
SELECT REPLACE( v_textin,'TT','T') into v_textin from dual ;
SELECT REPLACE( v_textin,'VV','V') into v_textin from dual ;
SELECT REPLACE( v_textin,'WW','W') into v_textin from dual ;
SELECT REPLACE( v_textin,'XX','X') into v_textin from dual ;
SELECT REPLACE( v_textin,'ZZ','Z') into v_textin from dual ;
dbms_output.put_line( v_textin );
if v_sub = v_textin OR v_sub is NULL THEN
EXIT;
end if;
END LOOP;
end if;

dbms_output.put_line('Step 23 completed giving :' || v_textin );


dbms_output.put_line('[24] put back in first letter and return' );
SELECT v_key || v_textin into v_textin from dual;

RETURN( v_textin );

end NYSIIS;
/

Converts a string of text into seperate soundex values
rem --------------------------------------------------------------------------
rem Filename: M_SOUNDEX.SQL
rem Purpose: Converts a string of text into seperate soundex values. Treating
rem it as space deliminated words. Useful when searching text strings for a sounds like.
rem
rem Notes: USEAGE "select M_SOUNDEX('the cat sat on the mat') from dual;"
rem M_SOUNDEX('THECATSATONTHEMAT')
rem -----------------------------------
rem T000 C300 S300 O500 T000 M300
rem
rem
rem select M_SOUNDEX('the cat sat on the mat') from dual where
rem M_SOUNDEX('the cat sat on the mat') like ('%' || SOUNDEX('cot') || '%');
rem
rem Date: 01-Mar-2005
rem Author: Trevor Fairhurst, trevgf@yahoo.com
rem --------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION "M_SOUNDEX" (v_text IN VARCHAR2) RETURN VARCHAR2 is
v_number number(10);
v_textin varchar2(4000);
v_textout varchar2(4000);
begin
SELECT UPPER (TRIM( v_text )) into v_textin from dual;
dbms_output.put_line( 'Entered text :' || v_textin);
SELECT '' into v_textout from dual;
LOOP
SELECT instr( v_textin , ' ' , 1 , 1 ) into v_number from dual;
if v_number = 0 THEN
SELECT v_textout || ' ' || SOUNDEX(v_textin) into v_textout from dual ;
EXIT;
else
SELECT v_textout || ' ' || SOUNDEX(substr (v_textin , 0 , v_number - 1 )) into v_textout from dual;
SELECT substr (v_textin , v_number + 1 ) into v_textin from dual;
end if;
END LOOP;
RETURN( v_textout );
end M_SOUNDEX;
/

General DBA Scripts
1. Show database uptime in days and hours
rem -----------------------------------------------------------------------
rem Filename: uptime.sql
rem Purpose: Display database uptime in days and hours
rem to SYS or SYSTEM
rem Date: 12-Jan-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from sys.v_$session
where sid=1 /* this is PMON */
/
Create database user like an exiting user (with exact privs)

rem -----------------------------------------------------------------------
rem Filename: cr8like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem database user. User data will not be copied.
rem Date: 02-Nov-1998
rem Author: Frank Naude, Oracle FAQ
rem Updated: Konstantin Krivosheyev - 7 Dec 2002
rem Updated: Frank Naude - 18 Dec 2003, 2 Dec 2004
rem -----------------------------------------------------------------------

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Tablespace Quotas...
select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');

-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/
• Switch from one database user to another without password (su.sql)

rem -----------------------------------------------------------------------
rem Filename: su.sql
rem Purpose: Switch from one database user to another (local db only)
rem Syntax: @su
rem Notes: Require ALTER USER and SELECT ON DBA_USERS privileges
rem Date: 12-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off head off
col nl newline
spool su.tmp

select 'alter user &&1 identified by &&1;' nl,
'connect &&1/&&1' nl,
'alter user &&1 identified by values '''||u.password||''';' nl
from sys.dba_users u
where u.username = upper('&&1')
and u.username <> user
/

spool off

@su.tmp

REM Delete the file. Use "rm" on Unix, "del" for Win/Dos
! rm su.tmp
! del su.tmp

set termout on

select 'Connected as '||USER||' on '||global_name||'.'
from global_name;

set termout on head on



• Dynamically ZIP large process trace files
----------------------------------------------------------------------------
-- -
-- Purpose: Dynamically zip a process's trace file -
-- -
-- Synopsis: @trc_zip -
-- -
-- Description: This script creates a named pipe in place of the process's -
-- trace file and spawns a gzip process to compress it. -
-- -
----------------------------------------------------------------------------

column trc_file new_value trc_file noprint
column zip_file new_value zip_file noprint

select p.value || '/ora_' || u.spid || '.trc' trc_file,
p.value || '/ora_' || u.spid || '.trc.gz' zip_file
from sys.v_$session s,
sys.v_$process u,
sys.v_$parameter p
where s.audsid = userenv('SESSIONID')
and u.addr = s.paddr
and p.name = 'user_dump_dest'
/

set define :
host mknod :trc_file p && nohup gzip < :trc_file > :zip_file &
set define &

alter session set max_dump_file_size = unlimited
/
Tabular display of redo-log archiving history (logs/hour)
rem -----------------------------------------------------------------------
rem Filename: archdist.sql
rem Purpose: Tabular display of redo-log archiving history (logs/hour)
rem - Can only run from sqlplus
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pagesize 50000
set veri off
set colsep ""

set termout off
def time="time" -- Oracle7
col time new_value time
select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time
from dual
where &&_O_RELEASE like '8%' -- Oracle8
/
set termout on

select substr(&&time, 1, 5) day,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'99') "23"
from sys.v_$log_history
group by substr(&&time,1,5)
/

set colsep " "
List control file structures with usage limits
rem -----------------------------------------------------------------------
rem Filename: ctlimits.sql
rem Purpose: List control file structures with usage limits
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
col PCT_USED format 990.09

-- Controlfile creation parameters:
-- Type DATAFILE is for MAXDATAFILES
-- Type REDO LOG is for MAXLOGFILES
-- Type LOG HISTORY is for MAXLOGHISTORY
-- Type REDO THREAD is for MAXINSTANCES
-- No entry for MAXLOGMEMBERS (?)

select type, records_used, records_total,
records_used/records_total*100 "PCT_USED"
from sys.v_$controlfile_record_section
/
Log all database errors to a table
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 Author: Nico Booyse (booysen@saps.org.za)
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;
/
Demonstrate database and schema level triggers
rem -----------------------------------------------------------------------
rem Filename: db-trig.sql
rem Purpose: Demonstrate database triggers (available from Oracle 8i)
rem Need DBA or CREATE ANY TRIGGER privs
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

create or replace trigger restrict_login
after logon on database
-- after logoff on database
-- after servererror on database
-- after startup on database
-- after shutdown on database
-- after create on database
-- after drop on database
-- after alter on database
declare
flag number := 0;
begin
select 1 into flag from sys.v_$session where program like '%sqlplus%';
if flag = 1 then
raise_application_error(-20000, 'No access from sqlplus');
end if;
end;
/
show errors


CREATE OR REPLACE TRIGGER startup_db
after startup on database
begin
dbms_shared_pool.keep ('SYS.STANDARD','P');
dbms_shared_pool.keep ('SYS.DBMS_STANDARD','P');
end;
/
show errors
Limit resources using the Database Resource Manager
rem -----------------------------------------------------------------------
rem Filename: rsrc.sql
rem Purpose: Demonstrate resource manager capabilities (limit CPU,
rem degree and sessions, available from Oracle 8i)
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

---------------------------------------------------------------------------
-- Create plan with consumer groups
---------------------------------------------------------------------------

exec dbms_resource_manager.create_pending_area;

exec dbms_resource_manager.delete_plan_cascade('night_plan');

exec dbms_resource_manager.create_plan('night_plan', 'Plan to use after 6PM');
exec dbms_resource_manager.create_consumer_group('batch', 'Group for batch reports');

exec dbms_resource_manager.create_plan_directive('night_plan', 'batch', 'Rules for overnight batch jobs', -
cpu_p1 => 75, parallel_degree_limit_p1 => 20);
exec dbms_resource_manager.create_plan_directive('night_plan', 'OTHER_GROUPS', 'Rules for overnight batch jobs', -
cpu_p1 => 25, parallel_degree_limit_p1 => 0, -
max_active_sess_target_p1 => 1);

exec dbms_resource_manager.validate_pending_area;
exec dbms_resource_manager.submit_pending_area;

---------------------------------------------------------------------------
-- List plans and consumer groups
---------------------------------------------------------------------------

set pages 50000
col plan format a12
col status format a7
col cpu_p1 format 999
col cpu_p2 format 999
col cpu_p3 format 999
col group_or_subplan format a17
col parallel_degree_limit_p1 format 999

select plan, num_plan_directives, status, mandatory from sys.dba_rsrc_plans;

select plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3, parallel_degree_limit_p1 as parallel, status
from sys.dba_rsrc_plan_directives
order by plan;

---------------------------------------------------------------------------
-- Switch a user to a new consumer group
---------------------------------------------------------------------------

exec dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'batch', FALSE);
exec dbms_resource_manager.set_initial_consumer_group('SCOTT', 'batch');

-- exec dbms_resource_manager.switch_consumer_group_for_user('SCOTT', 'batch'); -- Switch on-line users

select username, initial_rsrc_consumer_group from sys.dba_users where username = 'SCOTT';

---------------------------------------------------------------------------
-- Enable resource management for this instance
---------------------------------------------------------------------------
alter system set resource_manager_plan = 'NIGHT_PLAN';

---------------------------------------------------------------------------
-- Monitor the resource manager
---------------------------------------------------------------------------

col program format a40
select program, resource_consumer_group from sys.v_$session where username = 'SCOTT';

-- select * from sys.v_$rsrc_plan;
select * from sys.v_$rsrc_consumer_group;

Log Miner - extract undo statements from log files

rem -----------------------------------------------------------------------
rem Filename: logmgr.sql
rem Purpose: Log Miner: extract undo statements from online and archived
rem redo log files based on selection criteria.
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- Create a dictionary file
-- (init.ora parameter utl_file_dir must be set)
exec dbms_logmnr_d.build('mydictfile', '/tmp');

-- Register log files, can be from a different db
-- (NEWFILE=start new list/ ADDFILE=add next file)
exec dbms_logmnr.add_logfile(
LogFileName =>
'/app/oracle/arch/oradba/log_1_0000000027.oradba',
Options => dbms_logmnr.NEW);
exec dbms_logmnr.add_logfile(
LogFileName =>
'/app/oracle/arch/oradba/log_1_0000000028.oradba',
Options => dbms_logmnr.ADDFILE);

-- Start the logminer session
exec dbms_logmnr.start_logmnr(DictFileName => '/tmp/mydictfile');

-- Query v_$logmnr_contents view to extract required info
select timestamp, sql_undo
from sys.v_$logmnr_contents
where seg_name = 'EMPLOYEES';

-- Stop the logminer session
exec dbms_logmnr.end_logmnr;

Database cursor usage (open_cursors parameter)

rem -----------------------------------------------------------------------
rem Filename: cursors.sql
rem Purpose: Track database cursor usage
rem Date: 29-Nov-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

prompt Open Cursor Limit
col value format a30 head "Open Cursors Parameter Value"

select value
from sys.v_$parameter
where name = 'open_cursors';

prompt Summary of Current Curor Usage
col name format a25

select min(value) min, max(value) max, avg(value) avg
from sys.v_$sesstat
where statistic# = (select statistic#
from sys.v_$statname
where name like 'opened cursors current');

prompt Top 10 Users With Most Open Cursors
col program format a15 trunc
col osuser format a15 trunc
col username format a15 trunc

select * from (
select s.sid, s.username, s.osuser, s.program, v.value "Open
Cursors"
from sys.v_$sesstat v, sys.v_$session s
where v.sid = s.sid
and v.statistic# = (select statistic#
from sys.v_$statname
where name like 'opened cursors current')
order by v.value desc
)
where rownum < 11;
On-line table reorganizaton using the DBMS_REDEFINITION package
rem -----------------------------------------------------------------------
rem Filename: tabreorg.sql
rem Purpose: Show how a table can be reorganized on-line using the
rem DBMS_REDEFINITION package introduced in Oracle9i.
rem Date: 22-May-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

---------------------------------------------------------------------
-- Connect as SYSDBA to grant privs to scott...
---------------------------------------------------------------------
connect / as sysdba

grant execute on dbms_redefinition to scott;
grant dba to scott;

---------------------------------------------------------------------
-- Create new empty interim table...
---------------------------------------------------------------------
connect scott/tiger
CREATE TABLE emp_work AS SELECT * FROM emp WHERE 1=2;

---------------------------------------------------------------------
-- Test if table can be redefined...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'emp', 2);

---------------------------------------------------------------------
-- Start table redefinition...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'emp', 'emp_work', NULL, 2);

--------------------------------------------------------------------
-- Add ALL constraints, indexes, triggers, grants, etc...
---------------------------------------------------------------------
ALTER TABLE emp ADD PRIMARY KEY (empno);

---------------------------------------------------------------------
-- Finish the redefinition process (this will swap the two tables)...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_work');

---------------------------------------------------------------------
-- Drop the interim working table...
---------------------------------------------------------------------
DROP TABLE emp_work;