RISC VS CISC

The simplest way to examine the advantages and disadvantages of RISC architecture is by contrasting it with it's predecessor: CISC (Complex Instruction Set Computers) architecture.

Multiplying Two Numbers in Memory

On the right is a diagram representing the storage scheme for a generic computer. The main memory is divided into locations numbered from (row) 1: (column) 1 to (row) 6: (column) 4. The execution unit is responsible for carrying out all computations. However, the execution unit can only operate on data that has been loaded into one of the six registers (A, B, C, D, E, or F). Let's say we want to find the product of two numbers - one stored in location 2:3 and another stored in location 5:2 - and then store the product back in the location 2:3.

The CISC Approach

The primary goal of CISC architecture is to complete a task in as few lines of assembly as possible. This is achieved by building processor hardware that is capable of understanding and executing a series of operations. For this particular task, a CISC processor would come prepared with a specific instruction (we'll call it "MULT"). When executed, this instruction loads the two values into separate registers, multiplies the operands in the execution unit, and then stores the product in the appropriate register. Thus, the entire task of multiplying two numbers can be completed with one instruction:

MULT 2:3, 5:2

MULT is what is known as a "complex instruction." It operates directly on the computer's memory banks and does not require the programmer to explicitly call any loading or storing functions. It closely resembles a command in a higher level language. For instance, if we let "a" represent the value of 2:3 and "b" represent the value of 5:2, then this command is identical to the C statement "a = a * b."

One of the primary advantages of this system is that the compiler has to do very little work to translate a high-level language statement into assembly. Because the length of the code is relatively short, very little RAM is required to store instructions. The emphasis is put on building complex instructions directly into the hardware.

The RISC Approach

RISC processors only use simple instructions that can be executed within one clock cycle. Thus, the "MULT" command described above could be divided into three separate commands: "LOAD," which moves data from the memory bank to a register, "PROD," which finds the product of two operands located within the registers, and "STORE," which moves data from a register to the memory banks. In order to perform the exact series of steps described in the CISC approach, a programmer would need to code four lines of assembly:

    LOAD A, 2:3
    LOAD B, 5:2
    PROD A, B
    STORE 2:3, A

At first, this may seem like a much less efficient way of completing the operation. Because there are more lines of code, more RAM is needed to store the assembly level instructions. The compiler must also perform more work to convert a high-level language statement into code of this form.

CISC	RISC
Emphasis on hardware	Emphasis on software
Includes multi-clock, complex instructions	Single-clock,reduced instruction only
Memory-to-memory: "LOAD" and "STORE" incorporated in instructions	Register to register: "LOAD" and "STORE" are independent instructions
Small code sizes, high cycles per second	Low cycles per second, large code sizes
Transistors used for storing complex instructions	Spends more transistors on memory registers

However, the RISC strategy also brings some very important advantages. Because each instruction requires only one clock cycle to execute, the entire program will execute in approximately the same amount of time as the multi-cycle "MULT" command. These RISC "reduced instructions" require less transistors of hardware space than the complex instructions, leaving more room for general purpose registers. Because all of the instructions execute in a uniform amount of time (i.e. one clock), pipelining is possible.

Separating the "LOAD" and "STORE" instructions actually reduces the amount of work that the computer must perform. After a CISC-style "MULT" command is executed, the processor automatically erases the registers. If one of the operands needs to be used for another computation, the processor must re-load the data from the memory bank into a register. In RISC, the operand will remain in the register until another value is loaded in its place.

The Performance Equation

The following equation is commonly used for expressing a computer's performance ability:

The CISC approach attempts to minimize the number of instructions per program, sacrificing the number of cycles per instruction. RISC does the opposite, reducing the cycles per instruction at the cost of the number of instructions per program.

RISC Roadblocks Despite the advantages of RISC based processing, RISC chips took over a decade to gain a foothold in the commercial world. This was largely due to a lack of software support.

Although Apple's Power Macintosh line featured RISC-based chips and Windows NT was RISC compatible, Windows 3.1 and Windows 95 were designed with CISC processors in mind. Many companies were unwilling to take a chance with the emerging RISC technology. Without commercial interest, processor developers were unable to manufacture RISC chips in large enough volumes to make their price competitive.

Another major setback was the presence of Intel. Although their CISC chips were becoming increasingly unwieldy and difficult to develop, Intel had the resources to plow through development and produce powerful processors. Although RISC chips might surpass Intel's efforts in specific areas, the differences were not great enough to persuade buyers to change technologies.

The Overall RISC Advantage

Today, the Intel x86 is arguable the only chip which retains CISC architecture. This is primarily due to advancements in other areas of computer technology. The price of RAM has decreased dramatically. In 1977, 1MB of DRAM cost about $5,000. By 1994, the same amount of memory cost only $6 (when adjusted for inflation). Compiler technology has also become more sophisticated, so that the RISC use of RAM and emphasis on software has become ideal.

Reference http://www-cs-faculty.stanford.edu/~eroberts/courses/soco/projects/2000-01/risc/risccisc/

How to encrypt database passwords using SecureIdentityLoginModule

1. Encrypt password

For JBoss EAP 4.3, using the script below below:

        
JBOSSHOME=/apps/jboss-epp-5.1/jboss-as
PROFILE=production
echo "Please enter the password to be encrypted"
read password
java -cp $JBOSSHOME/lib/jboss-common.jar:$JBOSSHOME/lib/jboss-jmx.jar:$JBOSSHOME/server/$PROFILE/lib/jbosssx.jar:$JBOSSHOME/server/$PROFILE/lib/jboss-jca.jar org.jboss.resource.security.SecureIdentityLoginModule $password

For JBoss EAP 5.1, using the script below below:

        
JBOSSHOME=/apps/jboss-epp-5.1/jboss-as
PROFILE=production
echo "Please enter the password to be encrypted"
read password
java -cp $JBOSSHOME/client/jboss-logging-spi.jar:$JBOSSHOME/lib/jbosssx.jar org.jboss.resource.security.SecureIdentityLoginModule $password

2. Set the username and encrypted password (generated in the previous step) and the managedConnectionFactoryName for your datasource

<policy>
    <!-- Example usage of the SecureIdentityLoginModule -->
    <application-policy name="EncryptDBPassword">
        <authentication>
            <login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required">
                <module-option name="username">admin</module-option>
                <module-option name="password">5dfc52b51bd35553df8592078de921bc</module-option>
                <!-- Use this managedConnectionFactoryName for non-XA datasource -->
                <module-option name="managedConnectionFactoryName">jboss.jca:name=PostgresDS,service=LocalTxCM</module-option>                <!-- Use this managedConnectionFactoryName for XA datasource --> 

                <!-- <module-option name="managedConnectionFactoryName">jboss.jca:name=PostgresDS,service=XATxCM</module-option> --&gt; 

            </login-module>
        </authentication>
    </application-policy>
</policy>

3. Edit your datasource, removing the username and password properties and adding the security-domain you created in the previous step

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
    <local-tx-datasource>
        <jndi-name>PostgresDS</jndi-name>
        <connection-url>jdbc:postgresql://127.0.0.1:5432/test?protocolVersion=2</connection-url>
        <driver-class>org.postgresql.Driver</driver-class>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <security-domain>EncryptDBPassword</security-domain>
        <metadata>
            <type-mapping>PostgreSQL 8.0</type-mapping>
        </metadata>
    </local-tx-datasource>
</datasources>

4. The login-config.xml entry for the EncryptDBPassword would look like:

<policy>
    <!-- Example usage of the SecureIdentityLoginModule -->
    <application-policy name="EncryptDBPassword">
        <authentication>
            <login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required">
                <module-option name="username">admin</module-option>
                <module-option name="password">5dfc52b51bd35553df8592078de921bc</module-option>
                <module-option name="managedConnectionFactoryName">jboss.jca:name=PostgresDS,service=LocalTxCM</module-option>
            </login-module>
        </authentication>
    </application-policy>
</policy>

Reference to https://community.jboss.org/wiki/EncryptingDataSourcePasswords

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

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

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"