Have you ever tried to use LONG columns in the WHERE clause of your SQL statement? Something like ‘WHERE long_column like ‘%SEARCH%’? If so, you know this does not work! (You get ‘ORA-00932: inconsistent datatypes’) This tip shows how you
How to check an XML file is well formed in Oracle
CREATE OR REPLACE FUNCTION isWellFormed (xmlfile CLOB) Return BOOLEAN IS parser xmlparser.Parser; error varchar2(200); XMLParseError EXCEPTION; BEGIN — create a parser parser := xmlparser.newParser; — parse the XML document xmlparser.ParseCLOB(parser,xmlfile); — free the parser xmlparser.freeParser(parser); return true; EXCEPTION WHEN XMLParseError
How to test for numeric characters in a string
CREATE OR REPLACE FUNCTION is_numeric (string IN VARCHAR2) RETURN BOOLEAN /* || Function tests for NUMERIC characters, if 0-9 does NOT appear in the string then || FALSE is returned. */ IS i NUMBER; v_boolean BOOLEAN DEFAULT FALSE; BEGIN FOR
How to test for alphabetic characters in a string
CREATE OR REPLACE FUNCTION is_alpha (string IN VARCHAR2) RETURN BOOLEAN /* || Function tests for ALPHABETIC characters, if A-Z or a-z does NOT appear in the string then || FALSE is returned. */ IS i NUMBER; v_boolean BOOLEAN DEFAULT TRUE;
How to get file path when using MOD PLSQL
CREATE OR REPLACE Function GETFILEPATH return varchar2 is script_name varchar2(255) default owa_util.get_cgi_env(‘SCRIPT_NAME’); path_info varchar2(255) default owa_util.get_cgi_env(‘PATH_INFO’); pos number; BEGIN script_name := script_name || path_info; pos := instr(script_name,’docs’); script_name := substr(script_name,pos+5,length(script_name)-pos-4); return script_name; END; /
How to get number of weeks between two dates
CREATE OR REPLACE FUNCTION get_weeks_between (v_start_date IN DATE, v_end_date IN DATE) return number is v_no_weeks number; BEGIN SELECT TRUNC (v_end_date – v_start_date) / 7 INTO v_no_weeks FROM dual; return v_no_weeks; END; /
How to get the file suffix from a full path
CREATE OR REPLACE FUNCTION get_file_suffix (p_file IN VARCHAR2) RETURN varchar2 IS v_file_suffix VARCHAR2(5); BEGIN BEGIN /* || This Function returns a varchar2 string of the file suffix – any suffix length is returned, || If no suffix exists then NULL
How to get the file name from a full path
CREATE OR REPLACE FUNCTION get_file_name (p_file IN VARCHAR2) RETURN varchar2 IS v_file_name VARCHAR2(50); BEGIN /* || This Function returns a varchar2 string of the file name including the suffix. || Written by: Daniel Coe || Date: 10/09/2002 */ IF instr(p_file,’/’)
How to get the directory path from a full path
CREATE OR REPLACE FUNCTION get_dir_path (p_file IN VARCHAR2) RETURN varchar2 IS v_dir_path VARCHAR2(1000); BEGIN /* || This Function returns a varchar2 string of the directory path of the file name. — not including trailing slash. || Written by: Daniel Coe
How to get the directory name from a path
CREATE OR REPLACE FUNCTION get_dir_name (p_file IN VARCHAR2) RETURN varchar2 IS v_dir_name VARCHAR2(1000); BEGIN /* || This Function returns a varchar2 string of the name of folder above the file name. || Written by: Daniel Coe || Date: 10/09/2002 */