General PL/SQL Scripts

NYSIIS function (an improvement on SoundeX)

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…

Converts a string of text into separate soundex values

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

Spell out numbers to words (handy for cheque printing)

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))),…

Print cheque amounts in Indian Style

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 :=…

List tables from schema with more than X rows

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 occurrences of a substring with another substring

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…

Same as above, but implemented using UTL_SMTP

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