5 Replies Latest reply on Jul 3, 2001 4:43 PM by 3004

    PL/SQL Base64 Encode and Decode

    3004
      Greetings,
      We are currently working on a project which includes the creation of an XML document which is posted to an external process and results are return via XML. One of the other teams in the project has requested to use BASE64 encoding within the CDATA tags in the XML document. (Which begs the question why use CDATA if you are using BASE64 :-)) As a result, we are looking for PL/SQL procedures to encode and decode varchar2.
      Any help would be appreciated!!
        • 1. PL/SQL Base64 Encode and Decode
          3004
          In Oracle 9i, there is a package called UTL_ENCODE that provides those functions for your needs.
          • 2. PL/SQL Base64 Encode and Decode
            3004
            Anything available under the 8.1.6. platform?
            • 3. PL/SQL Base64 Encode and Decode
              3004
              Hi,

              I wrote a Base-64 encoding function in pure PL/SQL as part of the UTL_SMTP demo. You can see the source below. I hope that someone may take it as a starter to write the decode function:

              REM
              REM bs64demo.sql - Demo PL/SQL program that encodes binary data using the
              REM Base64-encoding scheme per the MIME standard (RFC 2045).
              REM
              REM Note: The performance of this Base-64 encoding package is slow due to
              REM excessive string parsing and byte manipulation in PL/SQL. A native
              REM implemenation of this function will be provided in Oracle 9i.

              CREATE OR REPLACE PACKAGE demo_base64 IS

              -- Base64-encode a piece of binary data.
              --
              -- Note that this encode function does not split the encoded text into
              -- multiple lines with no more than 76 bytes each as required by
              -- the MIME standard.
              --
              FUNCTION encode(r IN RAW) RETURN VARCHAR2;

              END;
              /

              CREATE OR REPLACE PACKAGE BODY demo_base64 IS

              TYPE vc2_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
              map vc2_table;

              -- Initialize the Base64 mapping
              PROCEDURE init_map IS
              BEGIN
              map(0) :='A'; map(1) :='B'; map(2) :='C'; map(3) :='D'; map(4) :='E';
              map(5) :='F'; map(6) :='G'; map(7) :='H'; map(8) :='I'; map(9):='J';
              map(10):='K'; map(11):='L'; map(12):='M'; map(13):='N'; map(14):='O';
              map(15):='P'; map(16):='Q'; map(17):='R'; map(18):='S'; map(19):='T';
              map(20):='U'; map(21):='V'; map(22):='W'; map(23):='X'; map(24):='Y';
              map(25):='Z'; map(26):='a'; map(27):='b'; map(28):='c'; map(29):='d';
              map(30):='e'; map(31):='f'; map(32):='g'; map(33):='h'; map(34):='i';
              map(35):='j'; map(36):='k'; map(37):='l'; map(38):='m'; map(39):='n';
              map(40):='o'; map(41):='p'; map(42):='q'; map(43):='r'; map(44):='s';
              map(45):='t'; map(46):='u'; map(47):='v'; map(48):='w'; map(49):='x';
              map(50):='y'; map(51):='z'; map(52):='0'; map(53):='1'; map(54):='2';
              map(55):='3'; map(56):='4'; map(57):='5'; map(58):='6'; map(59):='7';
              map(60):='8'; map(61):='9'; map(62):='+'; map(63):='/';
              END;

              FUNCTION encode(r IN RAW) RETURN VARCHAR2 IS
              i pls_integer;
              x pls_integer;
              y pls_integer;
              v VARCHAR2(32767);
              BEGIN

              -- For every 3 bytes, split them into 4 6-bit units and map them to
              -- the Base64 characters
              i := 1;
              WHILE ( i + 2 <= utl_raw.length(r) ) LOOP
              x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 +
              to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 +
              to_number(utl_raw.substr(r, i + 2, 1), '0X');
              y := floor(x / 262144); v := v &#0124; &#0124; map(y); x := x - y * 262144;
              y := floor(x / 4096); v := v &#0124; &#0124; map(y); x := x - y * 4096;
              y := floor(x / 64); v := v &#0124; &#0124; map(y); x := x - y * 64;
              v := v &#0124; &#0124; map(x);
              i := i + 3;
              END LOOP;

              -- Process the remaining bytes that has fewer than 3 bytes.
              IF ( utl_raw.length(r) - i = 0) THEN
              x := to_number(utl_raw.substr(r, i, 1), '0X');
              y := floor(x / 4); v := v &#0124; &#0124; map(y); x := x - y * 4;
              x := x * 16; v := v &#0124; &#0124; map(x);
              v := v &#0124; &#0124; '==';
              ELSIF ( utl_raw.length(r) - i = 1) THEN
              x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 +
              to_number(utl_raw.substr(r, i + 1, 1), '0X');
              y := floor(x / 1024); v := v &#0124; &#0124; map(y); x := x - y * 1024;
              y := floor(x / 16); v := v &#0124; &#0124; map(y); x := x - y * 16;
              x := x * 4; v := v &#0124; &#0124; map(x);
              v := v &#0124; &#0124; '=';
              END IF;

              RETURN v;

              END;

              BEGIN
              init_map;
              END;
              /
              null
              • 4. PL/SQL Base64 Encode and Decode
                3004
                Hi Mr. Robert Pang,

                Will the code work on Oracle 8.1.5?

                Thank you.

                <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>Originally posted by Robert Pang (robert.pang@oracle.com):
                Hi,

                I wrote a Base-64 encoding function in pure PL/SQL as part of the UTL_SMTP demo. You can see the source below. I hope that someone may take it as a starter to write the decode function:

                .....
                /
                <HR></BLOCKQUOTE>

                null
                • 5. PL/SQL Base64 Encode and Decode
                  3004
                  Yes. It will. It should work for even Oracle 7.3.