You'll never walk alone!

Wednesday, February 29, 2012

Convert Font

CREATE OR REPLACE PACKAGE CONVERTOR AS
/*
* VNI-to-Unicode conversion
*
* The process consists of 3 parts:
*
* 1) converts single-byte (ASCII) characters except O+ and U+ because they are also
* used as base in some VNI double-byte characters
* 2) converts VNI double-byte characters
* 3) converts O+ and U+
*
* @author Quan Nguyen
* @version 1.2, 23 October 05
*/
FUNCTION VNI_TO_UNI (p_vni_string varchar2) RETURN VARCHAR2;
FUNCTION OLAS_TO_UNI (p_olas_string varchar2) RETURN VARCHAR2;
FUNCTION OLAS1258_TO_UNI (p_olas1258_string varchar2) RETURN VARCHAR2;
FUNCTION UNI_TO_VNI (p_uni_string varchar2) RETURN VARCHAR2;
END CONVERTOR;

==============================================================
CREATE OR REPLACE PACKAGE BODY CONVERTOR AS

TYPE MAPPING_T IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
--VNI_TO_UNI
FUNCTION VNI_TO_UNI (p_vni_string varchar2) RETURN VARCHAR2 AS
/*
* VNI-to-Unicode conversion
*
* The process consists of 3 parts:
*
* 1) converts single-byte (ASCII) characters except O+ and U+ because they are also
* used as base in some VNI double-byte characters
* 2) converts VNI double-byte characters
* 3) converts O+ and U+
*
* @author Quan Nguyen
* @Modifier Nguyen Tich Thanh
* @version 1.2, 16/05/2011
*/
vni_mapping MAPPING_T;
--v_count PLS_INTEGER := 0;
v_result VARCHAR2(512);
v_pattern VARCHAR2(10);
v_str VARCHAR2(512);
v_startIndex PLS_INTEGER;
v_foundIndex PLS_INTEGER;

BEGIN
-- Initialization for vni_mapping
vni_mapping('O\00C2') := '\00C6';
vni_mapping('o\00E2') := '\00E6';
vni_mapping('y\00F5') := '\1EF9';
vni_mapping('Y\00D5') := '\1EF8';
vni_mapping('y\00FB') := '\1EF7';
vni_mapping('Y\00DB') := '\1EF6';
vni_mapping('y\00F8') := '\1EF3';
vni_mapping('Y\00D8') := '\1EF2';
vni_mapping('\00F6\00EF') := '\1EF1';
vni_mapping('\00D6\00CF') := '\1EF0';
vni_mapping('\00F6\00F5') := '\1EEF';
vni_mapping('\00D6\00D5') := '\1EEE';
vni_mapping('\00F6\00FB') := '\1EED';
vni_mapping('\00D6\00DB') := '\1EEC';
vni_mapping('\00F6\00F8') := '\1EEB';
vni_mapping('\00D6\00D8') := '\1EEA';
vni_mapping('\00F6\00F9') := '\1EE9';
vni_mapping('\00D6\00D9') := '\1EE8';
vni_mapping('u\00FB') := '\1EE7';
vni_mapping('U\00DB') := '\1EE6';
vni_mapping('u\00EF') := '\1EE5';
vni_mapping('U\00CF') := '\1EE4';
vni_mapping('\00F4\00EF') := '\1EE3';
vni_mapping('\00D4\00CF') := '\1EE2';
vni_mapping('\00F4\00F5') := '\1EE1';
vni_mapping('\00D4\00D5') := '\1EE0';
vni_mapping('\00F4\00FB') := '\1EDF';
vni_mapping('\00D4\00DB') := '\1EDE';
vni_mapping('\00F4\00F8') := '\1EDD';
vni_mapping('\00D4\00D8') := '\1EDC';
vni_mapping('\00F4\00F9') := '\1EDB';
vni_mapping('\00D4\00D9') := '\1EDA';
vni_mapping('o\00E4') := '\1ED9';
vni_mapping('O\00C4') := '\1ED8';
vni_mapping('o\00E3') := '\1ED7';
vni_mapping('O\00C3') := '\1ED6';
vni_mapping('o\00E5') := '\1ED5';
vni_mapping('O\00C5') := '\1ED4';
vni_mapping('o\00E0') := '\1ED3';
vni_mapping('O\00C0') := '\1ED2';
vni_mapping('o\00E1') := '\1ED1';
vni_mapping('O\00C1') := '\1ED0';
vni_mapping('o\00FB') := '\1ECF';
vni_mapping('O\00DB') := '\1ECE';
vni_mapping('o\00EF') := '\1ECD';
vni_mapping('O\00CF') := '\1ECC';
vni_mapping('e\00E4') := '\1EC7';
vni_mapping('E\00C4') := '\1EC6';
vni_mapping('e\00E3') := '\1EC5';
vni_mapping('E\00C3') := '\1EC4';
vni_mapping('e\00E5') := '\1EC3';
vni_mapping('E\00C5') := '\1EC2';
vni_mapping('e\00E0') := '\1EC1';
vni_mapping('E\00C0') := '\1EC0';
vni_mapping('e\00E1') := '\1EBF';
vni_mapping('E\00C1') := '\1EBE';
vni_mapping('e\00F5') := '\1EBD';
vni_mapping('E\00D5') := '\1EBC';
vni_mapping('e\00FB') := '\1EBB';
vni_mapping('E\00DB') := '\1EBA';
vni_mapping('e\00EF') := '\1EB9';
vni_mapping('E\00CF') := '\1EB8';
vni_mapping('a\00EB') := '\1EB7';
vni_mapping('A\00CB') := '\1EB6';
vni_mapping('a\00FC') := '\1EB5';
vni_mapping('A\00DC') := '\1EB4';
vni_mapping('a\00FA') := '\1EB3';
vni_mapping('A\00DA') := '\1EB2';
vni_mapping('a\00E8') := '\1EB1';
vni_mapping('A\00C8') := '\1EB0';
vni_mapping('a\00E9') := '\1EAF';
vni_mapping('A\00C9') := '\1EAE';
vni_mapping('a\00E4') := '\1EAD';
vni_mapping('A\00C4') := '\1EAC';
vni_mapping('a\00E3') := '\1EAB';
vni_mapping('A\00C3') := '\1EAA';
vni_mapping('a\00E5') := '\1EA9';
vni_mapping('A\00C5') := '\1EA8';
vni_mapping('a\00E0') := '\1EA7';
vni_mapping('A\00C0') := '\1EA6';
vni_mapping('a\00E1') := '\1EA5';
vni_mapping('A\00C1') := '\1EA4';
vni_mapping('a\00FB') := '\1EA3';
vni_mapping('A\00DB') := '\1EA2';
vni_mapping('a\00EF') := '\1EA1';
vni_mapping('A\00CF') := '\1EA0';
vni_mapping('u\00F5') := '\0169';
vni_mapping('U\00D5') := '\0168';
vni_mapping('a\00EA') := '\0103';
vni_mapping('A\00CA') := '\0102';
vni_mapping('y\00F9') := '\00FD';
vni_mapping('u\00F9') := '\00FA';
vni_mapping('u\00F8') := '\00F9';
vni_mapping('o\00F5') := '\00F5';
vni_mapping('o\00F9') := '\00F3';
vni_mapping('o\00F8') := '\00F2';
vni_mapping('e\00E2') := '\00EA';
vni_mapping('e\00F9') := '\00E9';
vni_mapping('e\00F8') := '\00E8';
vni_mapping('a\00F5') := '\00E3';
vni_mapping('a\00E2') := '\00E2';
vni_mapping('a\00F9') := '\00E1';
vni_mapping('a\00F8') := '\00E0';
vni_mapping('Y\00D9') := '\00DD';
vni_mapping('U\00D9') := '\00DA';
vni_mapping('U\00D8') := '\00D9';
vni_mapping('O\00D5') := '\00D5';
vni_mapping('O\00D9') := '\00D3';
vni_mapping('O\00D8') := '\00D2';
vni_mapping('E\00C2') := '\00CA';
vni_mapping('E\00D9') := '\00C9';
vni_mapping('E\00D8') := '\00C8';
vni_mapping('A\00D5') := '\00C3';
vni_mapping('A\00C2') := '\00C2';
vni_mapping('A\00D9') := '\00C1';
vni_mapping('A\00D8') := '\00C0';

-- Processing
-- Part 1
v_result := asciistr(p_vni_string);
v_result := replace(v_result, '\00D1', '\0110'); -- DD
v_result := replace(v_result, '\00F1', '\0111'); -- dd
v_result := replace(v_result, '\00D3', '\0128'); -- I~
v_result := replace(v_result, '\00F3', '\0129'); -- i~
v_result := replace(v_result, '\00D2', '\1ECA'); -- I.
v_result := replace(v_result, '\00F2', '\1ECB'); -- i.
v_result := replace(v_result, '\00C6', '\1EC8'); -- I?
v_result := replace(v_result, '\00E6', '\1EC9'); -- i?
v_result := replace(v_result, '\00CE', '\1EF4'); -- Y.
v_result := replace(v_result, '\00EE', '\1EF5'); -- y.

-- Part 2
-- Transform "O\u00C2" -> "\u00C6" to later transform back to "\u00D4" in Part 3
--v_count := REGEXP_COUNT(v_result, '\\', 1, 'c');
v_startIndex := 1;
v_foundIndex := 0;
v_pattern := '';
v_str := v_result;
v_result := '';
v_foundIndex := INSTR(v_str, '\', v_startIndex);
while ( v_foundIndex > 0)
loop
v_pattern := substr(v_str, v_foundIndex, 5);
case
when v_pattern = '\00D4' then
v_pattern := substr(v_str, v_foundIndex, 10);
when v_pattern = '\00D6' then
v_pattern := substr(v_str, v_foundIndex, 10);
when v_pattern = '\00F4' then
v_pattern := substr(v_str, v_foundIndex, 10);
when v_pattern = '\00F6' then
v_pattern := substr(v_str, v_foundIndex, 10);
else
v_pattern := substr(v_str, v_foundIndex - 1, 6);
end case;

if vni_mapping.EXISTS(v_pattern) then
if length(v_pattern) = 10 then
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || vni_mapping(v_pattern);
v_startIndex := v_foundIndex + 10;
else
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - 1 - v_startIndex);
v_result := v_result || vni_mapping(v_pattern);
v_startIndex := v_foundIndex - 1 + 6;
end if;
else
v_pattern := substr(v_str, v_foundIndex, 5);
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || v_pattern;
v_startIndex := v_foundIndex + 5;
end if;
v_foundIndex := INSTR(v_str, '\', v_startIndex);
end loop;
v_str := v_result || substr(v_str,v_startIndex);
v_result := v_str;

-- Part 3
v_result := replace(v_result, '\00D4', '\01A0'); -- O+
v_result := replace(v_result, '\00F4', '\01A1'); -- o+
v_result := replace(v_result, '\00D6', '\01AF'); -- U+
v_result := replace(v_result, '\00F6', '\01B0'); -- u+
v_result := replace(v_result, '\00C6', '\00D4'); -- O^
v_result := replace(v_result, '\00E6', '\00F4'); -- o^
RETURN unistr(v_result);
END VNI_TO_UNI;

-- OLAS1252_TO_UNI
FUNCTION OLAS_TO_UNI (p_olas_string varchar2) RETURN VARCHAR2 AS
/*
* OLAS codepage 1252 to Unicode conversion
*
* The process consists of 3 parts:
*
* 1) process which patterns have 6 chars
* 2) and then, process which patterns have 10 chars
* 3) process which patterns have 5 chars
*
* @author Nguyen Tich Thanh
* @version 1.0, 16/05/2011
*/
olas_mapping MAPPING_T;
v_result VARCHAR2(512);
v_pattern VARCHAR2(10);
v_str VARCHAR2(512);
v_startIndex PLS_INTEGER;
v_foundIndex PLS_INTEGER;
BEGIN
-- Initialization for olas_mapping
olas_mapping('A\00DD') := '\1EA0'; -- Ạ
olas_mapping('A\00DE') := '\00C1'; -- Á
olas_mapping('A\00EC') := '\00C3'; -- Ã
olas_mapping('A\00FD') := '\1EA2'; -- Ả
olas_mapping('A\00FE') := '\00C0'; -- À
olas_mapping('E\00DD') := '\1EB8'; -- Ẹ
olas_mapping('E\00DE') := '\00C9'; -- É
olas_mapping('E\00EC') := '\1EBC'; -- Ẽ
olas_mapping('E\00FD') := '\1EBA'; -- Ẻ
olas_mapping('E\00FE') := '\00C8'; -- È
olas_mapping('I') := 'I'; -- I
olas_mapping('I\00DD') := '\1ECA'; -- Ị
olas_mapping('I\00DE') := '\00CD'; -- Í
olas_mapping('I\00EC') := '\0128'; -- Ĩ
olas_mapping('I\00FD') := '\1EC8'; -- Ỉ
olas_mapping('I\00FE') := '\00CC'; -- Ì
olas_mapping('O\00DD') := '\1ECC'; -- Ọ
olas_mapping('O\00DE') := '\00D3'; -- Ó
olas_mapping('O\00EC') := '\00D5'; -- Õ
olas_mapping('O\00FD') := '\1ECE'; -- Ỏ
olas_mapping('O\00FE') := '\00D2'; -- Ò
olas_mapping('U\00DD') := '\1EE4'; -- Ụ
olas_mapping('U\00DE') := '\00DA'; -- Ú
olas_mapping('U\00EC') := '\0168'; -- Ũ
olas_mapping('U\00FD') := '\1EE6'; -- Ủ
olas_mapping('U\00FE') := '\00D9'; -- Ù
olas_mapping('Y\00DD') := '\1EF4'; -- Ỵ
olas_mapping('Y\00DE') := '\00DD'; -- Ý
olas_mapping('Y\00EC') := '\1EF8'; -- Ỹ
olas_mapping('Y\00FD') := '\1EF6'; -- Ỷ
olas_mapping('Y\00FE') := '\1EF2'; -- Ỳ
olas_mapping('\00C2\00DD') := '\1EAC'; -- Ậ
olas_mapping('\00C2\00DE') := '\1EA4'; -- Ấ
olas_mapping('\00C2\00EC') := '\1EAA'; -- Ẫ
olas_mapping('\00C2\00FD') := '\1EA8'; -- Ẩ
olas_mapping('\00C2\00FE') := '\1EA6'; -- Ầ
olas_mapping('\00C3\00DE') := '\1EAE'; -- Ắ
olas_mapping('\00C3\00EC') := '\1EB4'; -- Ẵ
olas_mapping('\00C3\00FD') := '\1EB2'; -- Ẳ
olas_mapping('\00C3\00FE') := '\1EB0'; -- Ằ
olas_mapping('\00CA\00DD') := '\1EC6'; -- Ệ
olas_mapping('\00CA\00DE') := '\1EBE'; -- Ế
olas_mapping('\00CA\00EC') := '\1EC4'; -- Ễ
olas_mapping('\00CA\00FD') := '\1EC2'; -- Ể
olas_mapping('\00CA\00FE') := '\1EC0'; -- Ề
olas_mapping('\00D2\00DD') := '\1EF0'; -- Ự
olas_mapping('\00D2\00DE') := '\1EE8'; -- Ứ
olas_mapping('\00D2\00EC') := '\1EEE'; -- Ữ
olas_mapping('\00D2\00FD') := '\1EEC'; -- Ử
olas_mapping('\00D2\00FE') := '\1EEA'; -- Ừ
olas_mapping('\00D4\00DD') := '\1ED8'; -- Ộ
olas_mapping('\00D4\00DE') := '\1ED0'; -- Ố
olas_mapping('\00D4\00EC') := '\1ED6'; -- Ỗ
olas_mapping('\00D4\00FD') := '\1ED4'; -- Ổ
olas_mapping('\00D4\00FE') := '\1ED2'; -- Ồ
olas_mapping('\00D5\00DD') := '\1EE2'; -- Ợ
olas_mapping('\00D5\00DE') := '\1EDA'; -- Ớ
olas_mapping('\00D5\00EC') := '\1EE0'; -- Ỡ
olas_mapping('\00D5\00FD') := '\1EDE'; -- Ở
olas_mapping('\00D5\00FE') := '\1EDC'; -- Ờ
olas_mapping('\00E2\00DD') := '\1EAD'; -- ậ
olas_mapping('\00E2\00DE') := '\1EA5'; -- ấ
olas_mapping('\00E2\00EC') := '\1EAB'; -- ẫ
olas_mapping('\00E2\00FD') := '\1EA9'; -- ẩ
olas_mapping('\00E2\00FE') := '\1EA7'; -- ầ
olas_mapping('\00E3\00DD') := '\1EB7'; -- ặ
olas_mapping('\00E3\00DE') := '\1EAF'; -- ắ
olas_mapping('\00E3\00EC') := '\1EB5'; -- ẵ
olas_mapping('\00E3\00FD') := '\1EB3'; -- ẳ
olas_mapping('\00E3\00FE') := '\1EB1'; -- ằ
olas_mapping('\00EA\00DD') := '\1EC7'; -- ệ
olas_mapping('\00EA\00DE') := '\1EBF'; -- ế
olas_mapping('\00EA\00EC') := '\1EC5'; -- ễ
olas_mapping('\00EA\00FD') := '\1EC3'; -- ể
olas_mapping('\00EA\00FE') := '\1EC1'; -- ề
olas_mapping('\00F2\00DD') := '\1EF1'; -- ự
olas_mapping('\00F2\00DE') := '\1EE9'; -- ứ
olas_mapping('\00F2\00EC') := '\1EEF'; -- ữ
olas_mapping('\00F2\00FD') := '\1EED'; -- ử
olas_mapping('\00F2\00FE') := '\1EEB'; -- ừ
olas_mapping('\00F4\00DD') := '\1ED9'; -- ộ
olas_mapping('\00F4\00DE') := '\1ED1'; -- ố
olas_mapping('\00F4\00EC') := '\1ED7'; -- ỗ
olas_mapping('\00F4\00FD') := '\1ED5'; -- ổ
olas_mapping('\00F4\00FE') := '\1ED3'; -- ồ
olas_mapping('\00F5\00DD') := '\1EE3'; -- ợ
olas_mapping('\00F5\00DE') := '\1EDB'; -- ớ
olas_mapping('\00F5\00EC') := '\1EE1'; -- ỡ
olas_mapping('\00F5\00FD') := '\1EDF'; -- ở
olas_mapping('\00F5\00FE') := '\1EDD'; -- ờ
olas_mapping('a\00DD') := '\1EA1'; -- ạ
olas_mapping('a\00DE') := '\00E1'; -- á
olas_mapping('a\00EC') := '\00E3'; -- ã
olas_mapping('a\00FD') := '\1EA3'; -- ả
olas_mapping('a\00FE') := '\00E0'; -- à
olas_mapping('e\00DD') := '\1EB9'; -- ẹ
olas_mapping('e\00DE') := '\00E9'; -- é
olas_mapping('e\00EC') := '\1EBD'; -- ẽ
olas_mapping('e\00FD') := '\1EBB'; -- ẻ
olas_mapping('e\00FE') := '\00E8'; -- è
olas_mapping('i\00DD') := '\1ECB'; -- ị
olas_mapping('i\00DE') := '\00ED'; -- í
olas_mapping('i\00EC') := '\0129'; -- ĩ
olas_mapping('i\00FD') := '\1EC9'; -- ỉ
olas_mapping('i\00FE') := '\00EC'; -- ì
olas_mapping('o\00DD') := '\1ECD'; -- ọ
olas_mapping('o\00DE') := '\00F3'; -- ó
olas_mapping('o\00EC') := '\00F5'; -- õ
olas_mapping('o\00FD') := '\1ECF'; -- ỏ
olas_mapping('o\00FE') := '\00F2'; -- ò
olas_mapping('u\00DD') := '\1EE5'; -- ụ
olas_mapping('u\00DE') := '\00FA'; -- ú
olas_mapping('u\00EC') := '\0169'; -- ũ
olas_mapping('u\00FD') := '\1EE7'; -- ủ
olas_mapping('u\00FE') := '\00F9'; -- ù
olas_mapping('y\00DD') := '\1EF5'; -- ỵ
olas_mapping('y\00DE') := '\00FD'; -- ý
olas_mapping('y\00EC') := '\1EF9'; -- ỹ
olas_mapping('y\00FD') := '\1EF7'; -- ỷ
olas_mapping('y\00FE') := '\1EF3'; -- ỳ

-- Processing
v_result := asciistr(p_olas_string);
v_startIndex := 1;
v_foundIndex := 0;
v_pattern := '';
v_str := v_result;
v_result := '';
v_foundIndex := INSTR(v_str, '\', v_startIndex);
while ( v_foundIndex > 0)
loop
-- xy ly pattern = 6 chars
v_pattern := substr(v_str, v_foundIndex - 1, 6);
if not olas_mapping.EXISTS(v_pattern) then
-- xy ly pattern = 10 chars
v_pattern := substr(v_str, v_foundIndex, 10);
end if;
if olas_mapping.EXISTS(v_pattern) then
if length(v_pattern) = 10 then
-- xy ly pattern = 10 chars
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || olas_mapping(v_pattern);
v_startIndex := v_foundIndex + 10;
else
-- xy ly pattern = 6 chars
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - 1 - v_startIndex);
v_result := v_result || olas_mapping(v_pattern);
v_startIndex := v_foundIndex - 1 + 6;
end if;
else
-- xy ly pattern = 5 chars
v_pattern := substr(v_str, v_foundIndex, 5);
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || v_pattern;
case
when v_pattern = '\00E3' then
v_result := replace(v_result, v_pattern, '\0103'); --ã
when v_pattern = '\00C3' then
v_result := replace(v_result, v_pattern, '\0102'); --Ã
when v_pattern = '\00E2' then
v_result := replace(v_result, v_pattern, '\00E2'); --â
when v_pattern = '\00C2' then
v_result := replace(v_result, v_pattern, '\00C2'); --Â
when v_pattern = '\00EA' then
v_result := replace(v_result, v_pattern, '\00EA'); --ê
when v_pattern = '\00CA' then
v_result := replace(v_result, v_pattern, '\00CA'); --Ê
when v_pattern = '\00F4' then
v_result := replace(v_result, v_pattern, '\00F4'); --ô
when v_pattern = '\00D4' then
v_result := replace(v_result, v_pattern, '\00D4'); --Ô
when v_pattern = '\00F2' then
v_result := replace(v_result, v_pattern, '\01B0'); --ò
when v_pattern = '\00D2' then
v_result := replace(v_result, v_pattern, '\01AF'); --Ò
when v_pattern = '\00F0' then
v_result := replace(v_result, v_pattern, '\0111'); --ð
when v_pattern = '\00D0' then
v_result := replace(v_result, v_pattern, '\0110'); --Ð
when v_pattern = '\00D5' then
v_result := replace(v_result, v_pattern, '\01A0'); --Õ
when v_pattern = '\00F5' then
v_result := replace(v_result, v_pattern, '\01A1'); --õ
else
v_result := v_result;
end case;
v_startIndex := v_foundIndex + 5;
end if;
v_foundIndex := INSTR(v_str, '\', v_startIndex);
end loop;
v_str := v_result || substr(v_str,v_startIndex);
v_result := v_str;
RETURN unistr(v_result);
END OLAS_TO_UNI;

-- OLAS 1258 TO UNI
FUNCTION OLAS1258_TO_UNI (p_olas1258_string varchar2) RETURN VARCHAR2 AS
/*
* OLAS codepage 1258 to Unicode conversion
*
* The process consists of 3 parts:
*
* 1) process which patterns have 6 chars
* 2) and then, process which patterns have 10 chars
* 3) process which patterns have 5 chars
*
* @author Nguyen Tich Thanh
* @version 1.0, 16/05/2011
*/
olas1258_mapping MAPPING_T;
v_result VARCHAR2(512);
v_pattern VARCHAR2(10);
v_str VARCHAR2(512);
v_startIndex PLS_INTEGER;
v_foundIndex PLS_INTEGER;
BEGIN
-- Initialization for olas1258_mapping
olas1258_mapping('a\00EC') := '\00E1'; --aì
olas1258_mapping('a\00CC') := '\00E0'; --aÌ
olas1258_mapping('a\00D2') := '\1EA3'; --aÒ
olas1258_mapping('a\00DE') := '\00E3'; --aÞ
olas1258_mapping('a\00F2') := '\1EA1'; --aò
olas1258_mapping('A\00EC') := '\00C1'; --Aì
olas1258_mapping('A\00CC') := '\00C0'; --AÌ
olas1258_mapping('A\00D2') := '\1EA2'; --AÒ
olas1258_mapping('A\00DE') := '\00C3'; --AÞ
olas1258_mapping('A\00F2') := '\1EA0'; --Aò
olas1258_mapping('\00E3\00EC') := '\1EAF'; --ãì
olas1258_mapping('\00E3\00CC') := '\1EB1'; --ãÌ
olas1258_mapping('\00E3\00D2') := '\1EB3'; --ãÒ
olas1258_mapping('\00E3\00DE') := '\1EB5'; --ãÞ
olas1258_mapping('\00E3\00F2') := '\1EB7'; --ãò
olas1258_mapping('\00C3\00EC') := '\1EAE'; --Ãì
olas1258_mapping('\00C3\00CC') := '\1EB0'; --ÃÌ
olas1258_mapping('\00C3\00D2') := '\1EB2'; --ÃÒ
olas1258_mapping('\00C3\00DE') := '\1EB4'; --ÃÞ
olas1258_mapping('\00C3\00F2') := '\1EA0'; --Ãò
olas1258_mapping('\00E2\00EC') := '\1EA5'; --âì
olas1258_mapping('\00E2\00CC') := '\1EA7'; --âÌ
olas1258_mapping('\00E2\00D2') := '\1EA9'; --âÒ
olas1258_mapping('\00E2\00DE') := '\1EAB'; --âÞ
olas1258_mapping('\00E2\00F2') := '\1EAD'; --âò
olas1258_mapping('\00C2\00EC') := '\1EA4'; --Âì
olas1258_mapping('\00C2\00CC') := '\1EA6'; --ÂÌ
olas1258_mapping('\00C2\00D2') := '\1EA8'; --ÂÒ
olas1258_mapping('\00C2\00DE') := '\1EAA'; --ÂÞ
olas1258_mapping('\00C2\00F2') := '\1EAC'; --Âò
olas1258_mapping('e\00EC') := '\00E9'; --eì
olas1258_mapping('e\00CC') := '\00E8'; --eÌ
olas1258_mapping('e\00D2') := '\1EBB'; --eÒ
olas1258_mapping('e\00DE') := '\1EBD'; --eÞ
olas1258_mapping('e\00F2') := '\1EB9'; --eò
olas1258_mapping('E\00EC') := '\00C9'; --Eì
olas1258_mapping('E\00CC') := '\00C8'; --EÌ
olas1258_mapping('E\00D2') := '\1EBA'; --EÒ
olas1258_mapping('E\00DE') := '\1EBC'; --EÞ
olas1258_mapping('E\00F2') := '\1EB8'; --Eò
olas1258_mapping('\00EA\00EC') := '\1EBF'; --êì
olas1258_mapping('\00EA\00CC') := '\1EC1'; --êÌ
olas1258_mapping('\00EA\00D2') := '\1EC3'; --êÒ
olas1258_mapping('\00EA\00DE') := '\1EC5'; --êÞ
olas1258_mapping('\00EA\00F2') := '\1EC7'; --êò
olas1258_mapping('\00CA\00EC') := '\1EBE'; --Êì
olas1258_mapping('\00CA\00CC') := '\1EC0'; --ÊÌ
olas1258_mapping('\00CA\00D2') := '\1EC2'; --ÊÒ
olas1258_mapping('\00CA\00DE') := '\1EC4'; --ÊÞ
olas1258_mapping('\00CA\00F2') := '\1EC6'; --Êò
olas1258_mapping('o\00EC') := '\00F3'; --oì
olas1258_mapping('o\00CC') := '\00F2'; --oÌ
olas1258_mapping('o\00D2') := '\1ECF'; --oÒ
olas1258_mapping('o\00DE') := '\00F5'; --oÞ
olas1258_mapping('o\00F2') := '\1ECD'; --oò
olas1258_mapping('O\00EC') := '\00D3'; --Oì
olas1258_mapping('O\00CC') := '\00D2'; --OÌ
olas1258_mapping('O\00D2') := '\1ECE'; --OÒ
olas1258_mapping('O\00DE') := '\00D5'; --OÞ
olas1258_mapping('O\00F2') := '\1ECC'; --Oò
olas1258_mapping('\00F4\00EC') := '\1ED1'; --ôì
olas1258_mapping('\00F4\00CC') := '\1ED3'; --ôÌ
olas1258_mapping('\00F4\00D2') := '\1ED5'; --ôÒ
olas1258_mapping('\00F4\00DE') := '\1ED7'; --ôÞ
olas1258_mapping('\00F4\00F2') := '\1ED9'; --ôò
olas1258_mapping('\00D4\00EC') := '\1ED0'; --Ôì
olas1258_mapping('\00D4\00CC') := '\1ED2'; --ÔÌ
olas1258_mapping('\00D4\00D2') := '\1ED4'; --ÔÒ
olas1258_mapping('\00D4\00DE') := '\1ED6'; --ÔÞ
olas1258_mapping('\00D4\00F2') := '\1ED8'; --Ôò
olas1258_mapping('y\00EC') := '\00FD'; --yì
olas1258_mapping('y\00CC') := '\1EF3'; --yÌ
olas1258_mapping('y\00D2') := '\1EF7'; --yÒ
olas1258_mapping('y\00DE') := '\1EF9'; --yÞ
olas1258_mapping('y\00F2') := '\1EF5'; --yò
olas1258_mapping('Y\00EC') := '\00DD'; --Yì
olas1258_mapping('Y\00CC') := '\1EF2'; --YÌ
olas1258_mapping('Y\00D2') := '\1EF6'; --YÒ
olas1258_mapping('Y\00DE') := '\1EF8'; --YÞ
olas1258_mapping('Y\00F2') := '\1EF4'; --Yò
olas1258_mapping('u\00EC') := '\00FA'; --uì
olas1258_mapping('u\00CC') := '\00F9'; --uÌ
olas1258_mapping('u\00D2') := '\1EE7'; --uÒ
olas1258_mapping('u\00DE') := '\0169'; --uÞ
olas1258_mapping('u\00F2') := '\1EE5'; --uò
olas1258_mapping('U\00EC') := '\00DA'; --Uì
olas1258_mapping('U\00CC') := '\00D9'; --UÌ
olas1258_mapping('U\00D2') := '\1EE6'; --UÒ
olas1258_mapping('U\00DE') := '\0168'; --UÞ
olas1258_mapping('U\00F2') := '\1EE4'; --Uò
olas1258_mapping('\00FD\00EC') := '\1EE9'; --ýì
olas1258_mapping('\00FD\00CC') := '\1EEB'; --ýÌ
olas1258_mapping('\00FD\00D2') := '\1EED'; --ýÒ
olas1258_mapping('\00FD\00F2') := '\1EF1'; --ýò
olas1258_mapping('\00DD\00EC') := '\1EE8'; --Ýì
olas1258_mapping('\00DD\00CC') := '\1EEA'; --ÝÌ
olas1258_mapping('\00DD\00D2') := '\1EEC'; --ÝÒ
olas1258_mapping('\00DD\00DE') := '\1EEE'; --ÝÞ
olas1258_mapping('\00DD\00F2') := '\1EF0'; --Ýò
olas1258_mapping('i\00EC') := '\00ED'; --iì
olas1258_mapping('i\00CC') := '\00EC'; --iÌ
olas1258_mapping('i\00D2') := '\1EC9'; --iÒ
olas1258_mapping('i\00DE') := '\0129'; --iÞ
olas1258_mapping('i\00F2') := '\1ECB'; --iò
olas1258_mapping('I\00EC') := '\00CD'; --Iì
olas1258_mapping('I\00CC') := '\00CC'; --IÌ
olas1258_mapping('I\00D2') := '\1EC8'; --IÒ
olas1258_mapping('I\00DE') := '\0128'; --IÞ
olas1258_mapping('\00D5\00EC') := '\1EDA'; --Õì
olas1258_mapping('\00D5\00CC') := '\1EDC'; --ÕÌ
olas1258_mapping('\00D5\00D2') := '\1EDE'; --ÕÒ
olas1258_mapping('\00D5\00DE') := '\1EE0'; --ÕÞ
olas1258_mapping('\00D5\00F2') := '\1EE2'; --Õò
olas1258_mapping('\00F5\00EC') := '\1EDB'; --õì
olas1258_mapping('\00F5\00CC') := '\1EDD'; --õÌ
olas1258_mapping('\00F5\00D2') := '\1EDF'; --õÒ
olas1258_mapping('\00F5\00DE') := '\1EE1'; --õÞ
olas1258_mapping('\00F5\00F2') := '\1EE3'; --õò
olas1258_mapping('\00EA\00DE') := '\1EC5'; --êÞ
olas1258_mapping('\00FD\00DE') := '\1EEF'; --ýÞ
olas1258_mapping('I\00F2') := '\1ECA'; --Iò
olas1258_mapping('I') := 'I'; --I

-- Processing
-- Part 1
v_result := asciistr(p_olas1258_string);
v_startIndex := 1;
v_foundIndex := 0;
v_pattern := '';
v_str := v_result;
v_result := '';
v_foundIndex := INSTR(v_str, '\', v_startIndex);
-- xy ly
while ( v_foundIndex > 0)
loop
-- assign v_pattern = 6 chars
v_pattern := substr(v_str, v_foundIndex - 1, 6);
if not olas1258_mapping.EXISTS(v_pattern) then
-- assign v_pattern = 10 chars
v_pattern := substr(v_str, v_foundIndex, 10);
end if;
if olas1258_mapping.EXISTS(v_pattern) then
if length(v_pattern) = 10 then
--xu ly 10 chars
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || olas1258_mapping(v_pattern);
v_startIndex := v_foundIndex + 10;
else
--xu ly 6 chars
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - 1 - v_startIndex);
v_result := v_result || olas1258_mapping(v_pattern);
v_startIndex := v_foundIndex - 1 + 6;
end if;
else
--xu ly 5 chars
v_pattern := substr(v_str, v_foundIndex, 5);
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || v_pattern;
case
when v_pattern = '\00E3' then
v_result := replace(v_result, v_pattern, '\0103'); --ã
when v_pattern = '\00C3' then
v_result := replace(v_result, v_pattern, '\0102'); --Ã
when v_pattern = '\00E2' then
v_result := replace(v_result, v_pattern, '\00E2'); --â
when v_pattern = '\00C2' then
v_result := replace(v_result, v_pattern, '\00C2'); --Â
when v_pattern = '\00EA' then
v_result := replace(v_result, v_pattern, '\00EA'); --ê
when v_pattern = '\00CA' then
v_result := replace(v_result, v_pattern, '\00CA'); --Ê
when v_pattern = '\00F4' then
v_result := replace(v_result, v_pattern, '\00F4'); --ô
when v_pattern = '\00D4' then
v_result := replace(v_result, v_pattern, '\00D4'); --Ô
when v_pattern = '\00FD' then
v_result := replace(v_result, v_pattern, '\01B0'); --ý
when v_pattern = '\00DD' then
v_result := replace(v_result, v_pattern, '\01AF'); --Ý
when v_pattern = '\00F2' then
v_result := replace(v_result, v_pattern, '\1ECA'); --ò
when v_pattern = '\00F0' then
v_result := replace(v_result, v_pattern, '\0111'); --ð
when v_pattern = '\00D0' then
v_result := replace(v_result, v_pattern, '\0110'); --Ð
when v_pattern = '\00D5' then
v_result := replace(v_result, v_pattern, '\01A0'); --Õ
when v_pattern = '\00F5' then
v_result := replace(v_result, v_pattern, '\01A1'); --õ
else
v_result := v_result;
end case;
v_startIndex := v_foundIndex + 5;
end if;
v_foundIndex := INSTR(v_str, '\', v_startIndex);
end loop;
v_str := v_result || substr(v_str,v_startIndex);
v_result := v_str;
RETURN unistr(v_result);
END OLAS1258_TO_UNI;

-- Unicode to Vni
FUNCTION UNI_TO_VNI (p_uni_string varchar2) RETURN VARCHAR2 AS
/*
* Unicode to Vni conversion
*
* @author Nguyen Tich Thanh
* @version 1.0, 16/05/2011
*/
uni_mapping MAPPING_T;
v_result VARCHAR2(512);
v_pattern VARCHAR2(10);
v_str VARCHAR2(512);
v_startIndex PLS_INTEGER;
v_foundIndex PLS_INTEGER;
BEGIN
-- Initialization for uni_mapping
uni_mapping('\00E1') := 'a\00F9'; --á
uni_mapping('\00E0') := 'a\00F8'; --à
uni_mapping('\1EA3') := 'a\00FB'; --ả
uni_mapping('\00E3') := 'a\00F5'; --ã
uni_mapping('\1EA1') := 'a\00EF'; --ạ
uni_mapping('\00C1') := 'A\00D9'; --Á
uni_mapping('\00C0') := 'A\00D8'; --À
uni_mapping('\1EA2') := 'A\00DB'; --Ả
uni_mapping('\00C3') := 'A\00D5'; --Ã
uni_mapping('\1EA0') := 'A\00CF'; --Ạ
uni_mapping('\1EAF') := 'a\00E9'; --ắ
uni_mapping('\1EB1') := 'a\00E8'; --ằ
uni_mapping('\1EB3') := 'a\00FA'; --ẳ
uni_mapping('\1EB5') := 'a\00FC'; --ẵ
uni_mapping('\1EB7') := 'a\00EB'; --ặ
uni_mapping('\1EAE') := 'A\00C9'; --Ắ
uni_mapping('\1EB0') := 'A\00C8'; --Ằ
uni_mapping('\1EB2') := 'A\00DA'; --Ẳ
uni_mapping('\1EB4') := 'A\00DC'; --Ẵ
uni_mapping('\1EA0') := 'A\00CF'; --Ạ
uni_mapping('\1EA5') := 'a\00E1'; --ấ
uni_mapping('\1EA7') := 'a\00E0'; --ầ
uni_mapping('\1EA9') := 'a\00E5'; --ẩ
uni_mapping('\1EAB') := 'a\00E3'; --ẫ
uni_mapping('\1EAD') := 'a\00E4'; --ậ
uni_mapping('\1EA4') := 'A\00C1'; --Ấ
uni_mapping('\1EA6') := 'A\00C0'; --Ầ
uni_mapping('\1EA8') := 'A\00C5'; --Ẩ
uni_mapping('\1EAA') := 'A\00C3'; --Ẫ
uni_mapping('\1EAC') := 'A\00C4'; --Ậ
uni_mapping('\00E9') := 'e\00F9'; --é
uni_mapping('\00E8') := 'e\00F8'; --è
uni_mapping('\1EBB') := 'e\00FB'; --ẻ
uni_mapping('\1EBD') := 'e\00F5'; --ẽ
uni_mapping('\1EB9') := 'e\00EF'; --ẹ
uni_mapping('\00C9') := 'E\00D9'; --É
uni_mapping('\00C8') := 'E\00D8'; --È
uni_mapping('\1EBA') := 'E\00DB'; --Ẻ
uni_mapping('\1EBC') := 'E\00D5'; --Ẽ
uni_mapping('\1EB8') := 'E\00CF'; --Ẹ
uni_mapping('\1EBF') := 'e\00E1'; --ế
uni_mapping('\1EC1') := 'e\00E0'; --ề
uni_mapping('\1EC3') := 'e\00E5'; --ể
uni_mapping('\1EC5') := 'e\00E3'; --ễ
uni_mapping('\1EC7') := 'e\00E4'; --ệ
uni_mapping('\1EBE') := 'E\00C1'; --Ế
uni_mapping('\1EC0') := 'E\00C0'; --Ề
uni_mapping('\1EC2') := 'E\00C5'; --Ể
uni_mapping('\1EC4') := 'E\00C3'; --Ễ
uni_mapping('\1EC6') := 'E\00C4'; --Ệ
uni_mapping('\00F3') := 'o\00F9'; --ó
uni_mapping('\00F2') := 'o\00F8'; --ò
uni_mapping('\1ECF') := 'o\00FB'; --ỏ
uni_mapping('\00F5') := 'o\00F5'; --õ
uni_mapping('\1ECD') := 'o\00EF'; --ọ
uni_mapping('\00D3') := 'O\00D9'; --Ó
uni_mapping('\00D2') := 'O\00D8'; --Ò
uni_mapping('\1ECE') := 'O\00DB'; --Ỏ
uni_mapping('\00D5') := 'O\00D5'; --Õ
uni_mapping('\1ECC') := 'O\00CF'; --Ọ
uni_mapping('\1ED1') := 'o\00E1'; --ố
uni_mapping('\1ED3') := 'o\00E0'; --ồ
uni_mapping('\1ED5') := 'o\00E5'; --ổ
uni_mapping('\1ED7') := 'o\00E3'; --ỗ
uni_mapping('\1ED9') := 'o\00E4'; --ộ
uni_mapping('\1ED0') := 'O\00C1'; --Ố
uni_mapping('\1ED2') := 'O\00C0'; --Ồ
uni_mapping('\1ED4') := 'O\00C5'; --Ổ
uni_mapping('\1ED6') := 'O\00C3'; --Ỗ
uni_mapping('\1ED8') := 'O\00C4'; --Ộ
uni_mapping('\00FD') := 'y\00F9'; --ý
uni_mapping('\1EF3') := 'y\00F8'; --ỳ
uni_mapping('\1EF7') := 'y\00FB'; --ỷ
uni_mapping('\1EF9') := 'y\00F5'; --ỹ
uni_mapping('\1EF5') := '\00EE'; --ỵ
uni_mapping('\00DD') := 'Y\00D9'; --Ý
uni_mapping('\1EF2') := 'Y\00D8'; --Ỳ
uni_mapping('\1EF6') := 'Y\00DB'; --Ỷ
uni_mapping('\1EF8') := 'Y\00D5'; --Ỹ
uni_mapping('\1EF4') := '\00CE'; --Ỵ
uni_mapping('\00FA') := 'u\00F9'; --ú
uni_mapping('\00F9') := 'u\00F8'; --ù
uni_mapping('\1EE7') := 'u\00FB'; --ủ
uni_mapping('\0169') := 'u\00F5'; --ũ
uni_mapping('\1EE5') := 'u\00EF'; --ụ
uni_mapping('\00DA') := 'U\00D9'; --Ú
uni_mapping('\00D9') := 'U\00D8'; --Ù
uni_mapping('\1EE6') := 'U\00DB'; --Ủ
uni_mapping('\0168') := 'U\00D5'; --Ũ
uni_mapping('\1EE4') := 'U\00CF'; --Ụ
uni_mapping('\1EE9') := '\00F6\00F9'; --ứ
uni_mapping('\1EEB') := '\00F6\00F8'; --ừ
uni_mapping('\1EED') := '\00F6\00FB'; --ử
uni_mapping('\1EF1') := '\00F6\00EF'; --ự
uni_mapping('\1EE8') := '\00D6\00D9'; --Ứ
uni_mapping('\1EEA') := '\00D6\00D8'; --Ừ
uni_mapping('\1EEC') := '\00D6\00DB'; --Ử
uni_mapping('\1EEE') := '\00D6\00D5'; --Ữ
uni_mapping('\1EF0') := '\00D6\00CF'; --Ự
uni_mapping('\00ED') := '\00ED'; --í
uni_mapping('\00EC') := '\00EC'; --ì
uni_mapping('\1EC9') := '\00E6'; --ỉ
uni_mapping('\0129') := '\00F3'; --ĩ
uni_mapping('\1ECB') := '\00F2'; --ị
uni_mapping('\00CD') := '\00CD'; --Í
uni_mapping('\00CC') := '\00CC'; --Ì
uni_mapping('\1EC8') := '\00C6'; --Ỉ
uni_mapping('\0128') := '\00D3'; --Ĩ
uni_mapping('\1EDA') := '\00D4\00D9'; --Ớ
uni_mapping('\1EDC') := '\00D4\00D8'; --Ờ
uni_mapping('\1EDE') := '\00D4\00DB'; --Ở
uni_mapping('\1EE0') := '\00D4\00D5'; --Ỡ
uni_mapping('\1EE2') := '\00D4\00CF'; --Ợ
uni_mapping('\1EDB') := '\00F4\00F9'; --ớ
uni_mapping('\1EDD') := '\00F4\00F8'; --ờ
uni_mapping('\1EDF') := '\00F4\00FB'; --ở
uni_mapping('\1EE1') := '\00F4\00F5'; --ỡ
uni_mapping('\1EE3') := '\00F4\00EF'; --ợ
uni_mapping('\1EC5') := 'e\00E3'; --ễ
uni_mapping('\1EEF') := '\00F6\00F5'; --ữ
uni_mapping('\1ECA') := '\00D2'; --Ị
uni_mapping('\0103') := 'a\00EA'; --ă
uni_mapping('\0102') := 'A\00CA'; --Ă
uni_mapping('\00E2') := 'a\00E2'; --â
uni_mapping('\00C2') := 'A\00C2'; --Â
uni_mapping('\00EA') := 'e\00E2'; --ê
uni_mapping('\00CA') := 'E\00C2'; --Ê
uni_mapping('\00F4') := 'o\00E2'; --ô
uni_mapping('\00D4') := 'O\00C2'; --Ô
uni_mapping('\01B0') := '\00F6'; --ư
uni_mapping('\01AF') := '\00D6'; --Ư
uni_mapping('\1ECA') := '\00D2'; --Ị
uni_mapping('\0111') := '\00F1'; --đ
uni_mapping('\0110') := '\00D1'; --Đ
uni_mapping('\01A0') := '\00D4'; --Ơ
uni_mapping('\01A1') := '\00F4'; --ơ

-- Processing
-- Part 1
v_result := asciistr(p_uni_string);
v_startIndex := 1;
v_foundIndex := 0;
v_pattern := '';
v_str := v_result;
v_result := '';
v_foundIndex := INSTR(v_str, '\', v_startIndex);
-- xy ly
while ( v_foundIndex > 0)
loop
-- assign v_pattern = 5 chars
v_pattern := substr(v_str, v_foundIndex , 5);
if uni_mapping.EXISTS(v_pattern) then
--xu ly 5 chars
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || uni_mapping(v_pattern);
v_startIndex := v_foundIndex + 5;
else
--xu ly 5 chars
v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
v_result := v_result || v_pattern;
v_startIndex := v_foundIndex + 5;
end if;
v_foundIndex := INSTR(v_str, '\', v_startIndex);
end loop;
v_str := v_result || substr(v_str,v_startIndex);
v_result := v_str;
RETURN unistr(v_result);
END UNI_TO_VNI;

END CONVERTOR;

Reset Sequence

CREATE OR REPLACE PROCEDURE reset_sequence ( seq_name IN VARCHAR2,
startvalue IN PLS_INTEGER) AS
cval   INTEGER;
inc_by VARCHAR2(25);
BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM DUAL'  INTO cval;
  cval := cval - startvalue + 1;
  IF cval < 0 THEN
    inc_by := ' INCREMENT BY ';
    cval:= ABS(cval);
  ELSE
    inc_by := ' INCREMENT BY -';
  END IF;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by ||  cval;
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM DUAL'  INTO cval;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name ||  ' INCREMENT BY 1';
END reset_sequence;

Reference: http://psoug.org/reference/sequences.html

Tuesday, August 30, 2011

GLOBAL TEMPORARY TABLES NOTES

each session will allocate it's own unique segment. a GTT has no "segment" associated with it
until you use it and when you

a) commit or
b) logout

the segment goes "poof"

so, you have 365 sesssions -- and therefor 365 segments that live for a brief moment of time

Monday, August 29, 2011

application context

Updated May 4th, 2009

See this link for a superior approach:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908

Sounds like a good use of REF CURSORS to me. Lets say you wanted to build a generic
routine that would look at the inputs passed to it and build a WHERE clause for each
NON-NULL parameter passed. This would result in a large number of statically defined
cursors so we'll use a ref cursor instead.

I'll demonstrate below. I'll write a write that will print out some EMP data. This
routine will take upto 3 inputs to constrain the result set. I want to have upto 8
different cursors possible here

o 1 with NO where clause (all inputs null)
o 3 with a single predicate
o 3 with "pairs" of predicate conditions
o 1 with all three predicate conditions

Additionally, since the use of BIND VARIABLES is one of the MOST important things in
programming Oracle -- I'll want to make sure I use them as well. This will be trickly
since I don't know if I'll have 0, 1, 2, or 3 of them until runtime. I'll use an
application context to solve that problem.

Here is a sample implementation:

ops$tkyte@ORA8I.WORLD> create or replace context MY_CTX using MY_PROCEDURE
2 /

Context created.

That created our application context and bound it to our yet to be created procedure
"MY_PROCEDURE". Note that only MY_PROCEDURE will be able to set values in this context.
See

http://asktom.oracle.com/~tkyte/article2/index.html

for more info on application contexts and their use

ops$tkyte@ORA8I.WORLD> create or replace
procedure p ( p_str in varchar2 )
2 is
3 l_str long := p_str || chr(10);
4 l_piece long;
5 n number;
6 begin
7 loop
8 exit when l_str is null;
9 n := instr( l_str, chr(10) );
10 l_piece := substr( l_str, 1, n-1 );
11 l_str := substr( l_str, n+1 );
12 loop
13 exit when l_piece is null;
14 dbms_output.put_line( substr( l_piece, 1,
250 ) );
15 l_piece := substr( l_piece, 251 );
16 end loop;
17 end loop;
18 end;
19 /

Procedure created.

P is just a little procedure I use to print things out nicer then dbms_output would. I
use it below to dump the dynamically generated query so we can see what was built for
each execution. It is not really relevant to the example, just part of the
demonstration...

ops$tkyte@ORA8I.WORLD> create or replace
2 procedure my_procedure( p_ename in varchar2 default NULL,
3 p_hiredate in date default NULL,
4 p_sal in number default NULL)
5 as
6 type rc is REF CURSOR;
7
8 l_cursor rc;
9 l_query varchar2(512)
10 default 'select * from emp where 1 = 1 ';
11
12 cursor l_template is select * from emp;
13 l_rec l_template%rowtype;
14

Here I use what I call a "TEMPLATE" cursor. I like to use these with my ref cursors.
I use them to define a record to fetch into. Here, in this simple example, I could have
skipped it and just defined l_rec as EMP%rowtype -- but I wanted to show how this would
work if you didn't select * from a single table but had many columns from many tables.
This just helps me create a nice record. The template query ONLY has a SELECT and a
FROM. I never put a WHERE clause on it (even when joining) since I never use it any
where. I just use it to get the default datatypes, names and so on for a record
definition right below it.

15 begin
16
17 if ( p_ename is NOT NULL ) then
18 dbms_session.set_context( 'MY_CTX', 'ENAME',
19 '%'||upper(p_ename)||'%');
20 l_query := l_query ||
21 ' and ename like
22 sys_context( ''MY_CTX'', ''ENAME'' ) ';
23 end if;
24

for each input -- i'm inspecting it to see if it is non-null. If it is, I add to the
where clause and set the value in the context. Notice how in the where clause -- I
always use the SYS_CONTEXT function. I NEVER put the literal value in to the query (that
would be very bad and would trash the shared pool -- very extremely important to use bind
variables). Note also the use of '' to get a single ' into the where clause!

25 if ( p_hiredate is NOT NULL ) then
26 dbms_session.set_context( 'MY_CTX', 'HIREDATE',
27 to_char(p_hiredate,'yyyymmddhh24miss'));
28 l_query := l_query ||
29 ' and hiredate >
30 to_date(
31 sys_context( ''MY_CTX'',
32 ''HIREDATE'' ),
33 ''yyyymmddhh24miss'') ';
34 end if;

Note here how I am careful to preserve the date and time component -- if necessary!
Always wrap the sys_context in a TO_DATE call if you are comparing to a DATE to avoid
implicit conversions in the query at runtime!

35
36 if ( p_sal is NOT NULL ) then
37 dbms_session.set_context( 'MY_CTX', 'SAL', p_sal);
38 l_query := l_query ||
39 ' and sal >
40 to_number(
41 sys_context( ''MY_CTX'',
42 ''SAL'' )
43 ) ';
44 end if;
45

Same caveat for the NUMBER here. Use TO_NUMBER to avoid IMPLICIT conversions

46 p( l_query );
47
48 open l_cursor for l_query;
49
50 loop
51 fetch l_cursor into l_rec;
52 exit when l_cursor%notfound;
53
54 dbms_output.put_line( l_rec.ename || ',' ||
55 l_rec.hiredate || ',' ||
56 l_rec.sal );
57 end loop;
58
59 close l_cursor;
60 end;
61 /

Procedure created.

and that is it. I now have a routine that will open 1 of 8 possible different
cursors. Here is a small test run just to see how it works

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> exec my_procedure
select * from emp where 1 = 1
SMITH,17-dec-1980 00:00:00,800
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300
KING,,5

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a' )
select * from emp where 1 = 1 and ename like
sys_context( 'MY_CTX', 'ENAME' )
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_sal => 1000 )
select * from emp where 1 = 1 and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_hiredate => add_months(sysdate,-240) )
select * from emp where 1 = 1 and hiredate >
to_date(
sys_context( 'MY_CTX',
'HIREDATE' ),
'yyyymmddhh24miss')
ALLEN,20-feb-1981 00:00:00,1600
WARD,22-feb-1981 00:00:00,1250
JONES,02-apr-1981 00:00:00,2975
MARTIN,28-sep-1981 00:00:00,1250
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450
SCOTT,09-dec-1982 00:00:00,3000
KING,17-nov-1981 00:00:00,5000
TURNER,08-sep-1981 00:00:00,1500
ADAMS,12-jan-1983 00:00:00,1100
JAMES,03-dec-1981 00:00:00,950
FORD,03-dec-1981 00:00:00,3000
MILLER,23-jan-1982 00:00:00,1300

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec my_procedure( p_ename => 'a', p_sal => 2000 )
select * from emp where 1 = 1 and ename like
sys_context( 'MY_CTX', 'ENAME' ) and sal >
to_number(
sys_context( 'MY_CTX',
'SAL' )
)
BLAKE,01-may-1981 00:00:00,2850
CLARK,09-jun-1981 00:00:00,2450

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD>

Difference Between Full Index Scans and Fast Full Index Scans

A fast full index scan reads the entire index, unsorted, as it exists on disk. It is basically using the index as a “skinny” version of the table. The query in question would be accessing only attributes in the index. (We are not using the index as a way to get to the table—we are using the index instead of the table.) We use multiblock I/O and read all the leaf, branch, and root blocks. We ignore the branch and root blocks when executing the query and just process the (unordered) data in the leaf blocks.

A full index scan reads the index a block at a time, from start to finish. It reads the root block, navigates down the left-hand side of the index (or the right-hand side for a descending full scan), and then when it hits the leaf block, it reads across the entire bottom of the index—a block at a time—in sorted order. It uses single-block, not multiblock, I/O for this operation.

Reference from "Ask Tom"

Tuesday, May 31, 2011

RMAN 'Format_String' clause

RMAN 'Format_String' clause

The formatting of this information varies by platform.

Syntax:
%c The copy number of the backup piece within a set of duplexed
backup pieces. If you did not duplex a backup, then this variable
is 1 for backup sets and 0 for proxy copies.
If one of these commands is enabled, then the variable shows the
copy number. The maximum value for %c is 256.

%d The name of the database.

%D The current day of the month (in format DD)

%F Combination of DBID, day, month, year, and sequence into a unique
and repeatable generated name.

%M The month (format MM)

%n The name of the database, padded on the right with x characters
to a total length of eight characters. (AKA: Porn star alias name)
For example, if the sales is the database name, %n= salesxxx.

%p The piece number within the backup set. This value starts at 1
for each backup set and is incremented by 1 as each backup piece
is created. Note: If you specify PROXY, then the %p variable must
be included in the FORMAT string either explicitly or implicitly within %U.

%s The backup set number. This number is a counter in the control file that
is incremented for each backup set. The counter value starts at 1 and is
unique for the lifetime of the control file. If you restore a backup
control file, then duplicate values can result.
Also, CREATE CONTROLFILE initializes the counter back to 1.

%t The backup set timestamp, which is a 4-byte value derived as the
number of seconds elapsed since a fixed reference time.
The combination of %s and %t can be used to form a unique name for
the backup set.

%T The year, month, and day (YYYYMMDD)

%u An 8-character name constituted by compressed representations of
the backup set number and the time the backup set was created.

%U A convenient shorthand for %u_%p_%c that guarantees uniqueness in
generated backup filenames.
If you do not specify a format, RMAN uses %U by default.

%Y The year (YYYY)

%% Specifies the actual '%' character. e.g. %%Y translates to %Y.

Friday, August 20, 2010

Synchronization of primary and standby database due to log transfer gap. [ID 150214.1]

Synchronization of primary and standby database due to log transfer gap. [ID 150214.1]
Modified 06-MAY-2009 Type BULLETIN Status PUBLISHED

PURPOSE
-------

The purpose of this document is;
a) How to verify whether there is a gap in the log transfer from the primary
to the standby database.
b) How to solve this problem.

The problem is that the primary and standby database are not able to operate
in managed recovery mode while the setup followed the Oracle reference manuals.

The cause is a gap in archives produced by primary database, the standby
database is waiting for these archive logs. It may be necessary to synchronize
the primary and standby database.

SCOPE & APPLICATION
-------------------
1. Detection of the log gap primary and standby database.

a) Use a sql statement.
b) Check the alert files.
c) Check for fal server process.
d) Checking archive destinations.

2. Synchronize the primary and standby database.

a) Make the missing archives available for the standby database.
b) Recover the standby database automatic.

3. Check the log gap primary and standby database is solved.

a) Use a sql statement.
b) Check the alert files.

After correcting the log gap problem place the standby database in
sustained managed recovery mode as in Note:120855.1.

1. Detection of the log gap primary and standby database.

a) Using a sql statement.
-----------------------

To find out which logs have not been received by this standby destination,
issue the following query at the primary database:

SQL> SELECT local.thread#, local.sequence# from
(select thread#, sequence# from v$archived_log where dest_id=1) local
where
local.sequence# not in
(select sequence# from v$archived_log where dest_id=2 and
thread# = local.thread#);

THREAD# SEQUENCE#
---------- ----------
1 521
1 522
1 523
1 524
1 525
Another possible statement to check this is (at the standby database):
SELECT thread#, low_sequence#, high_sequence#
from V$archive_gap;

Note: the sql statements give additional information (confirmation)
of information of alert files. If these sql statements return rows
it doesn't necessarily mean ther's an actual gap! Always check the
alert files as in 1b.

b) Check the alert files.
-------------------------

Alert file primary database:

Thu Jun 21 13:23:13 2001
ARC0: Beginning to archive log 1 thread 1 sequence 525
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Error 12541 connecting to standby host 'ssym_nlsu22.world'
ARC0: Error 12541 Creating archive log file to 'ssym_nlsu22.world'
ARC0: Completed archiving log 1 thread 1 sequence 525
Thu Jun 21 13:40:21 2001
Thread 1 advanced to log sequence 527
Current log# 1 seq# 527 mem# 0: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG1Oa
Current log# 1 seq# 527 mem# 1: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG2Oa
Thu Jun 21 13:40:21 2001
ARC0: Beginning to archive log 2 thread 1 sequence 526
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Completed archiving log 2 thread 1 sequence 526

Alert file standby:

$ tail -50 alert_ssy*|more

Thu Jun 21 13:34:53 2001
Media Recovery Start: Managed Standby Recovery
Successfully started datafile 1 media recovery
Datafile #1: '/ots0/app/oracle/product/9.0.1/dbs/sroo/backup/system09.dbf'
Media Recovery Log
Media Recovery Waiting for thread 1 seq# 522
Thu Jun 21 13:40:23 2001
Fetching gap sequence for thread 1, gap sequence 522-525
Trying FAL server:
Error fetching gap sequence, no FAL server specified
Thu Jun 21 13:40:38 2001
Failed to request gap sequence. Thread #: 1, gap sequence: 522-525
All FAL server has been attempted.

c) Fal server process.
-------------------

A Fal server process is an Oracle server process running on the primary database
servicing fal request from a fal client. An example of a request of a client are
queueing requests to send archived redologfiles from a primary database to one or
more standby databases.

d) Checking archive destinations.
------------------------------

Checking the archive destination of the primary database:

/home/server/sroo/test
$ ls
arch_1_521.arc arch_1_523.arc arch_1_525.arc
arch_1_522.arc arch_1_524.arc arch_1_526.arc testsroo/

Checking the archive destination of the standby database:

/home/server/sroo/test/testsroo
$ ls
arch_1_526.arc

2 Synchronize the primary and standby database.
---------------------------------------------

a) Make the missing archives available for the standby database.
-------------------------------------------------------------

=>Copy and register (if necessary) the missing archives from the
gap to a location that can be reached by the standby database that
is the standby_archive_dest.

$ pwd
/home/server/sroo/test
$ cp *521.arc testsroo
$ cp *522.arc testsroo
$ cp *523.arc testsroo
$ cp *524.arc testsroo
$ cp *525.arc testsroo

SQL> alter database register logfile '/home/server/sroo/test/testsroo/arch_1_521.arc';

b) Recover the standby database automatic.
---------------------------------------

Cancel the managed recovery (check Note:120855.1):

after cancel the standby recovery session shows:
SQL> recover managed standby database;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/ots0/app/oracle/product/9.0.0/dbs/sroo/backup/system09.dbf'
ORA-16037: user requested cancel of managed recovery operation

Then recover the standby database:

SQL> recover automatic standby database;
ORA-00279: change 61729 generated at 06/21/2001 13:40:21 needed for thread 1
ORA-00289: suggestion : /home/server/sroo/test/testsroo/arch_1_527.arc
ORA-00280: change 61729 for thread 1 is in sequence #527
ORA-00278: log file '/home/server/sroo/test/testsroo/arch_1_527.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/home/server/sroo/test/testsroo/arch_1_527.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}
=> cancel
Media recovery cancelled.

Set the standby database back to work:

SQL> recover managed standby database;

3 Check the log gap primary and standby database is solved.

a) Using an sql statement.
-----------------------

To find out which logs have not been received by this standby destination, issue
the following query at the primary database:

SQL> SELECT local.thread#, local.sequence# from
(select thread#, sequence# from v$archived_log where dest_id=1) local
where
local.sequence# not in
(select sequence# from v$archived_log where dest_id=2 and
thread# = local.thread#);

=> no rows selected => OK!

Note: Even if this sql statement returns rows synchronisation
may be succesfull - always check alert files (next item).

b) Checking the alert files.
-------------------------

Check alert files if archives are now picked up by standby database:

Checking alert file primary:

$ tail -50 alert_sy*

Thu Jun 21 13:23:13 2001
ARC0: Beginning to archive log 1 thread 1 sequence 525
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Error 12541 connecting to standby host 'ssym_nlsu22.world'
ARC0: Error 12541 Creating archive log file to 'ssym_nlsu22.world'
ARC0: Completed archiving log 1 thread 1 sequence 525
Thu Jun 21 13:40:21 2001
Thread 1 advanced to log sequence 527
Current log# 1 seq# 527 mem# 0: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG1Oa
Current log# 1 seq# 527 mem# 1: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG2Oa
Thu Jun 21 13:40:21 2001
ARC0: Beginning to archive log 2 thread 1 sequence 526
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Transmitting activation ID 680595809 (28911161)
ARC0: Completed archiving log 2 thread 1 sequence 526
Thu Jun 21 13:56:37 2001
LGWR: Transmitting activation ID 680595809 (28911161)
LGWR: Beginning to archive log 2 thread 1 sequence 528
Thread 1 advanced to log sequence 528
Current log# 2 seq# 528 mem# 0: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG3Oa
Current log# 2 seq# 528 mem# 1: /ots0/app/oracle/product/9.0.1/dbs/sroo/LOG4Oa
Thu Jun 21 13:56:38 2001
ARC0: Beginning to archive log 1 thread 1 sequence 527
ARC0: Warning; LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC0: Completed archiving log 1 thread 1 sequence 527

Checking alert file standby:

$ tail -50 alert_ssy*

Thu Jun 21 13:54:45 2001
ALTER DATABASE RECOVER automatic standby database
Thu Jun 21 13:54:45 2001
Media Recovery Start
Successfully started datafile 1 media recovery
Datafile #1: '/ots0/app/oracle/product/9.0.1/dbs/sroo/backup/system09.dbf'
Media Recovery Log
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_522.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_523.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_524.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_525.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_526.arc
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_527.arc
ORA-279 signalled during: ALTER DATABASE RECOVER automatic standby database...
Thu Jun 21 13:55:01 2001
ALTER DATABASE RECOVER CANCEL
Media Recovery Cancelled
Completed: ALTER DATABASE RECOVER CANCEL
Thu Jun 21 13:56:00 2001
ALTER DATABASE RECOVER managed standby database
Thu Jun 21 13:56:00 2001
Media Recovery Start: Managed Standby Recovery
Successfully started datafile 1 media recovery
Datafile #1: '/ots0/app/oracle/product/9.0.1/dbs/sroo/backup/system09.dbf'
Media Recovery Log
Media Recovery Waiting for thread 1 seq# 527
Thu Jun 21 13:56:45 2001
Media Recovery Log /home/server/sroo/test/testsroo/arch_1_527.arc
Media Recovery Waiting for thread 1 seq# 528

=> OK!

Show Related Information Related
Products

* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Back to topBack to top

Rate this document
Article Rating
Rate this document
Excellent
Good
Poor
Did this document help you?
Yes
No
Just browsing
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

Comments
Provide some feedback
Cancel