ADO and Unicode

• unicode_test table This table must be created in a UTF8 Oracle 8.1.6+ Instance.
• add_unicode: This stored procedure is used to add a Unicode code point as a UTF-8 encode series of bytes into the database. The ADO command object correctly encodes the Unicode code as UTF-8. The field value for character_field is UTF-8. You can verify the hex values stored in the database with the following SQL statement: select rawtohex(character_field) from unicode_test where unicode_number='U0021'. Do a UTF-8 to Unicode code conversion. See the Unicode Primer book for instructions on the conversion.
• update_unicode: This stored procedure is used to update the code_point by searching on the p_unicode_number input parameter..
• get_unicode: This stored procedure is used to retrieve a cursor record by code_point only, unicode_number value only, or both. The cursor field code_point is assigned to the p_code_point output parameter. The ADO command object will reference the output parameter for the code_point value.

CREATE TABLE UNICODE_TEST
   (
   UNICODE_NUMBER                     NOT NULL VARCHAR2(20)
   CODE_POINT                               VARCHAR2(200)
   );

   CREATE OR REPLACE PROCEDURE add_unicode (
       p_code_point         Varchar2,
       p_unicode_number                 Varchar2
   ) IS
   BEGIN

      insert into unicode_test
   (code_point, unicode_number)
   values
   (p_code_point,p_unicode_number);

   commit;

   END add_unicode;

   CREATE OR REPLACE PROCEDURE update_unicode (
       p_code_point         Varchar2,
       p_unicode_number   Varchar2
   ) IS
   BEGIN

      update unicode_test
       set
       code_point=p_code_point
           where unicode_number = p_unicode_number;

   commit;

   END update_unicode;

   CREATE OR REPLACE PROCEDURE get_unicode (p_unicode_number in varchar2,
   p_code_point in varchar2,
   p_out_code_point out varchar2) as
   begin

   if (length(p_unicode_number)> 0 and length(p_code_point)>0) then
       SELECT code_point into
       p_out_code_point
       FROM UNICODE_TEST
       WHERE UNICODE_NUMBER=p_unicode_number
       and
       code_point_field=p_code_point;
   elsif length(p_unicode_number)>0 then

       SELECT code_point into
       p_out_code_pont
       FROM UNICODE_TEST
       WHERE UNICODE_NUMBER=p_unicode_number;

   elsif length(p_code_point)>0 then

       SELECT code_point into
       p_out_code_point
       FROM UNICODE_TEST
       WHERE
       code_point=p_code_point;

   end if;

   END get_unicode;