Live ORACLE

if you are Oracle Developer ,than this Blog most likely will make you happy.

About my Blog

This Blog particularized for Oracle Developers ... you will see interesting Codes for SQL , PL/SQL as well as new ideas for Developer Suite and Client Tools that will help you in your professional life ... and I hope I reach for your satisfaction.

About Me

I'm Hany Freedom , 25 years old , I live in EL-Minia City in Egypt , I'm Moderator at ArabOUG.org the best Arabic Forum for ORACLE on the Net. if you interested to know more about me .... just Click Here.

Oracle Database Design

This Book demonstrate how to design blueprint for your Schema.


Download the Book from this Link :-







ُ
Share/Save/Bookmark

Oracle Forms & Reports

This Book explain - by photos - how to deal with Forms and reports.

To download the Book .... Click this Link :-









ُ
Share/Save/Bookmark

Oracle Database Programming

Click this Link to download the Book :-








ُ
Share/Save/Bookmark

Introduction on Oracle Database

Download the Book from that Link :-










Share/Save/Bookmark

Register on Oracle.com

Do you think there are any difficulties with the Registering at ORACLE.com ? ..... if so , than you should download this file below :-





Share/Save/Bookmark

Make a shortcut Icon for your Oracle Project

I made a white paper for how to Create a Shortcut Icon for an Oracle Project on your Desktop for both Oracle 6i & 10g users.

Download the Document from this Link :-



Share/Save/Bookmark

Oracle Magazine (January/February 2009)

Look Inside >> 
January/February 2009

Share/Save/Bookmark

Privacy Policy for (Hany4U.BlogSpot.com)


The privacy of our visitors to http://hany4u.blogspot.com/ is important to us.

At Hany4U.BlogSpot.com, we recognize that privacy of your personal information is important. Here is information on what types of personal information we receive and collect when you use and visit Hany4U.BlogSpot.com, and how we safeguard your information. We never sell your personal information to third parties.

Log Files
As with most other websites, we collect and use the data contained in log files. The information in the log files include your IP (internet protocol) address, your ISP (internet service provider, such as AOL or Shaw Cable), the browser you used to visit our site (such as Internet Explorer or Firefox), the time you visited our site and which pages you visited throughout our site.

Cookies and Web Beacons
We do use cookies to store information, such as your personal preferences when you visit our site. and this for Google Analytics purposes.

We also use third party advertisements on Hany4U.BlogSpot.com to support our site. Some of these advertisers may use technology such as cookies and web beacons when they advertise on our site, which will also send these advertisers (such as Google through the Google AdSense program) information including your IP address, your ISP , the browser you used to visit our site, and in some cases, whether you have Flash installed. This is generally used for geotargeting purposes (showing New York real estate ads to someone in New York, for example) or showing certain ads based on specific sites visited (such as showing cooking ads to someone who frequents cooking sites).

DoubleClick DART cookies
We also may use DART cookies for ad serving through Google’s DoubleClick, which places a cookie on your computer when you are browsing the web and visit a site using DoubleClick advertising (including some Google AdSense advertisements). This cookie is used to serve ads specific to you and your interests (”interest based targeting”). The ads served will be targeted based on your previous browsing history (For example, if you have been viewing sites about visiting Las Vegas, you may see Las Vegas hotel advertisements when viewing a non-related site, such as on a site about hockey). DART uses “non personally identifiable information”. It does NOT track personal information about you, such as your name, email address, physical address, telephone number, social security numbers, bank account numbers or credit card numbers. You can opt-out of this ad serving on all sites using this advertising by visiting http://www.doubleclick.com/privacy/dart_adserving.aspx

You can choose to disable or selectively turn off our cookies or third-party cookies in your browser settings, or by managing preferences in programs such as Norton Internet Security. However, this can affect how you are able to interact with our site as well as other websites. This could include aborting some Google Analytics functions , hence our service will be worse.

Deleting cookies does not mean you are permanently opted out of any advertising program. Unless you have settings that disallow cookies, the next time you visit a site running the advertisements, a new cookie will be added.

For more information : Click Here to know how Interest-based advertising works.


Share/Save/Bookmark

Download and Install Oracle DB and Developer Step by Step

I made new Book demonstrate how to Download and Installing Oracle Database 10g and Oracle Forms 10g Screen Shot by Screen Shot .

Download the Book from this link >


Share/Save/Bookmark

Foreign Key by TOAD

Those Screen shots demonstrates

how to build Foreign Key by TOAD :-


------------( 1 )-----------

------------( 2 )-----------

------------( 3 )-----------

------------( 4 )-----------

------------( 5 )-----------


------------( 6 )-----------


------------( 7 )-----------


------------( 8 )-----------


------------( 9 )-----------


------------( 10 )-----------


------------( 11 )-----------


------------( 12 )-----------


------------( 13 )-----------




Share/Save/Bookmark

Package to generate HTML-type documentation for Oracle objects

CREATE OR REPLACE PACKAGE documentator
AS

/* **************************************
Set Dependencies
* **************************************/
PROCEDURE getRefObjects(
IN_OBJECT VARCHAR2,
IN_OBJECT_TYPE VARCHAR2);

/* **************************************
Get Table Dependencies
* **************************************/
PROCEDURE getTblRefObjects(
IN_OBJECT VARCHAR2,
IN_OBJECT_TYPE VARCHAR2);

/* **************************************
Get common object details
* **************************************/
PROCEDURE getTopObjectDetails(
IN_OBJECT VARCHAR2,
IN_OBJECT_TYPE VARCHAR2);

/* **************************************
Set Doc Header
* **************************************/
PROCEDURE setDocHeader(
IN_OBJECT VARCHAR2,
IN_OBJECT_TYPE VARCHAR2);

/* **************************************
Get all privileges
* **************************************/
PROCEDURE getUnitAllPrivs(IN_OBJECT VARCHAR2);

/* **************************************
Initialize variables
* **************************************/
PROCEDURE initDoc(IN_OBJECT VARCHAR2);

/* **************************************
Finish documents preparation and send emails
* **************************************/
PROCEDURE finishDoc(
IN_OBJECT VARCHAR2,
IN_FILE_NAME VARCHAR2,
IN_EMAIL VARCHAR2);

/* **************************************
Prepare document for Procedure/Function/Package
* **************************************/
PROCEDURE getPkgDoc(
IN_OBJECT VARCHAR2,
IN_FILE_NAME VARCHAR2);

/* **************************************
Prepare document for Table
* **************************************/
PROCEDURE getTblDoc(
IN_OBJECT VARCHAR2,
IN_OBJECT_TYPE VARCHAR2,
IN_FILE_NAME VARCHAR2);

/* **************************************
Main procedure to generate document
* **************************************/
PROCEDURE generateDocInfo(
IN_OBJECT VARCHAR2,
IN_FILE_NAME VARCHAR2,
IN_EMAIL VARCHAR2);

/* **************************************
Main procedure to generate document for all objects for the given object type
* **************************************/
PROCEDURE generateDocInfoByType(
IN_OBJECT_TYPE VARCHAR2,
IN_FILE_NAME VARCHAR2,
IN_EMAIL VARCHAR2);

/* **************************************
Main procedure to generate document for all objects for the given object types
* **************************************/
PROCEDURE generateDocInfoByTypes(
IN_OBJECT_TYPE VARCHAR2,
IN_FILE_NAME VARCHAR2,
IN_EMAIL VARCHAR2);

/* **************************************
Get list of procedures/functions with arguments
* **************************************/
PROCEDURE getArgumentsList(IN_OBJECT VARCHAR2);

/* **************************************
Get trigger columns
* **************************************/
PROCEDURE getTriggerColumns(IN_OBJECT VARCHAR2);

/* **************************************
Get trigger details
* **************************************/
PROCEDURE getTriggerDetails(IN_OBJECT VARCHAR2);

/* **************************************
Get PL/SQL unit Source
* **************************************/
PROCEDURE getUnitSource(
IN_OBJECT VARCHAR2,
IN_OBJECT_TYPE VARCHAR2);

/* **************************************
Get type status
* **************************************/
PROCEDURE getTypeStatus(
IN_OBJECT VARCHAR2,
IN_OBJECT_TYPE VARCHAR2);

/* **************************************
Get type attributes
* **************************************/
PROCEDURE getTypeAttrs(IN_OBJECT VARCHAR2);

/* **************************************
Get list of type methods
* **************************************/
PROCEDURE getTypeMethods(IN_OBJECT VARCHAR2);

/* **************************************
Get table columns
* **************************************/
PROCEDURE getTblCols(IN_OBJECT VARCHAR2);

/* **************************************
Get Table Constraints
* **************************************/
PROCEDURE getTblConstraints(IN_OBJECT VARCHAR2);

/* **************************************
Get all Table privileges
* **************************************/
PROCEDURE getTblPrivs(IN_OBJECT VARCHAR2);

/* **************************************
Get Table Indexes
* **************************************/
PROCEDURE getTblIndexes(IN_OBJECT VARCHAR2);

PROCEDURE sendEmailAttachments(
IN_FROM_EMAIL VARCHAR2,
IN_TO_EMAILS VARCHAR2,
IN_CC_EMAILS VARCHAR2,
IN_SUBJ VARCHAR2,
IN_TEXT CLOB,
IN_MIME_TYPE VARCHAR2 DEFAULT 'text/plain',
IN_FILENAME_1 VARCHAR2,
IN_CONTENT_1 CLOB,
IN_FILENAME_2 VARCHAR2 DEFAULT NULL,
IN_CONTENT_2 CLOB DEFAULT NULL,
IN_FILENAME_3 VARCHAR2 DEFAULT NULL,
IN_CONTENT_3 CLOB DEFAULT NULL);

END documentator;
/
show error


Share/Save/Bookmark

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



Share/Save/Bookmark

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



Share/Save/Bookmark

convert numbers to chars for printing cheques amount 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


Share/Save/Bookmark

This function will convert a number to words, handy for ptinting cheques

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;


Share/Save/Bookmark

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
/


Share/Save/Bookmark

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

/



Share/Save/Bookmark

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



Share/Save/Bookmark

Maintain an audit log of DDL changes (alter/ drop/ create) 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
/


Share/Save/Bookmark

Access Internet Web pages from SQL or 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;


Share/Save/Bookmark

Send e-mail messages and attachments from PL/SQL (using UTL_SMTP)

rem ----------------------------------------------------------------------
rem Filename: utlsmtp.sql
rem Purpose: Send e-mail messages and attachments from PL/SQL
rem Date: 01-Sep-2006
rem Author: Frank Naude - OraFAQ
rem ----------------------------------------------------------------------

DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'test@mycompany.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||

'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="excel.csv"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="excel.csv"'|| crlf ||
crlf ||
'CSV,file,attachement'|| crlf || -- Content of attachment
crlf ||

'-------SECBOUND--' -- End MIME mail
);

utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/



Share/Save/Bookmark

Send e-mail messages and attachments from PL/SQL

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



Share/Save/Bookmark

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


Share/Save/Bookmark

Execute operating system commands from PL/SQL

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



Share/Save/Bookmark

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
/



Share/Save/Bookmark

Example PL/SQL code to 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


Share/Save/Bookmark

Create a database view on top of a file

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-%'
/


Share/Save/Bookmark

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


Share/Save/Bookmark

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


Share/Save/Bookmark

Load a binary file (images, documents, etc) into a database table

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


Share/Save/Bookmark

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


Share/Save/Bookmark

Algorithms

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



Share/Save/Bookmark

Print 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


Share/Save/Bookmark

Check if a year is a leap year (FUNCTION)

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



Share/Save/Bookmark

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 <prand@spu.edu> 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;



Share/Save/Bookmark

Package with functions to convert numbers between the Decimal, Binary, Octal and Hexidecimal numbering systems

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;



Share/Save/Bookmark
Newer Posts Older Posts Home Page
 
http://www.dpriver.com/images/sqlpp-banner-2.png

Thanks for reading my Blog ... you Visitor Number :-