URL Encoder in plSQL

Url encode will replace the blank as %20 — that is 20 hex, which is 32 ascii
which is the ascii code for “space”.

Select to_char( ascii(‘&’),’xx’) from dual; to get the hexcode for &,
but there are other characters to worry about as well. So, the function:

CREATE OR REPLACE FUNCTION URLENCODER (P_STR IN VARCHAR2)
RETURN VARCHAR2
AS
VTMP VARCHAR2(32767) := NULL;
VLEN NUMBER DEFAULT LENGTH (P_STR);
VBAD VARCHAR2(32767) DEFAULT ' >%}\~];?@&<#{|^[`/:=$+''"' || CHR (10);
VCHAR CHAR (1) := NULL;

BEGIN
IF (P_STR IS NULL)
THEN
RETURN NULL;
END IF;

FOR I IN 1 .. VLEN
LOOP
VCHAR := SUBSTR (P_STR, I, 1);
IF (INSTR (VBAD, VCHAR) > 0)
THEN
VTMP := VTMP || '%' || TO_CHAR (ASCII (VCHAR), 'fm0X');
ELSE
VTMP := VTMP || VCHAR;
END IF;
END LOOP;
RETURN VTMP;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/

which lets me code:

variable := ‘
http://xyz/pls/mydata/show?category1=&#8217;
||
urlencode( ‘toys & games’ ) ||
‘&category2=’ || urlencode(‘abc’);

which gives me the string:

‘HTTP://XYZ/PLS/MYDATA/SHOW?CATEGORY1=’||URLENCODE(‘TOYS&GAMES’)||’&CATEGORY2=’||
URLENCODE(‘ABC’)
———————————————————————————
————————————————–
http://xyz/pls/mydata/show?category1=toys%20%26%20games&category2=abc

The reverse side of encoder is decode, here is how to decode url :

create or replace function urldecode

(p_str in varchar2) return varchar2 is

/* Declare */
l_hex varchar2(16) := ‘0123456789ABCDEF’;
l_idx number := 0;
l_ret long := p_str;

begin
if p_str is null then
return p_str;
end if;
loop
l_idx := instr(l_ret, ‘%’, l_idx + 1);
exit when l_idx = 0;
l_ret := substr(l_ret, 1, l_idx - 1) ||
chr((instr(l_hex, substr(l_ret, l_idx + 1, 1)) - 1) * 16 +
instr(l_hex, substr(l_ret, l_idx + 2, 1)) - 1) ||
substr(l_ret, l_idx + 3);
end loop;
return l_ret;
end urldecode;

source : asktom.oracle.com

11 thoughts on “URL Encoder in plSQL

  1. Hi Hernk,
    I am currently facing a similar situatiion where I have to do URL encoding using PL/SQL. The function code pasted on this site is incomplete.

    Can you please let me know how can we achive the URL encoding using pl/sql. Are there any Oracle functions to do it.

    Thans for your help.
    George

  2. hi, thank You for taking time to view my post.
    actually yes, I took it from asktom.oracle.com but I forgot to put script source.
    thanks

  3. hi ,

    to encode and decode, you may use base64 encoder and decoder.

    Function to encode:

    create or replace
    FUNCTION F_ENCODE(
    p_str_value VARCHAR2 )
    RETURN VARCHAR2
    AS
    BEGIN
    IF LENGTH( TRIM(p_str_value) ) > 1 THEN
    RETURN utl_i18n.raw_to_char( utl_encode.base64_encode( utl_i18n.string_to_raw( TRIM(p_str_value), ‘UTF8’ ) ), ‘UTF8’ );
    ELSE
    RETURN NULL;
    END IF;
    END F_ENCODE;

    Function to decode:

    create or replace
    FUNCTION F_ENCODE(
    p_str_value VARCHAR2 )
    RETURN VARCHAR2
    AS
    BEGIN
    IF LENGTH( TRIM(p_str_value) ) > 1 THEN
    RETURN utl_i18n.raw_to_char( utl_encode.base64_decode( utl_i18n.string_to_raw( TRIM(p_str_value), ‘UTF8’ ) ), ‘UTF8’ );
    ELSE
    RETURN NULL;
    END IF;
    END F_ENCODE;

    Cheers,
    Arun

  4. Wow, awesome blog format! How long have you
    ever been running a blog for? you make blogging glance easy.
    The total look of your site is great, as smartly as
    the content!

  5. Wow that was unusual. I just wrote an extremely long comment but after I clicked submit my
    comment didn’t show up. Grrrr… well I’m not writing all that over again. Regardless, just wanted to say fantastic blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s