Oracle, how to pass a '&' in url and other special characters


Function to normalize url link, substitute special characters in link:

CREATE OR REPLACE FUNCTION urlencode( p_str in varchar2 ) return varchar2
AS
    l_tmp varchar2(12000);
    l_len number default length(p_str);
    l_bad varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"' || chr(10);
    l_char char(1);
BEGIN
    IF ( p_str is NULL ) then
        RETURN NULL;
    END IF;
    FOR i IN 1 .. l_len LOOP
        l_char := substr(p_str,i,1);
        IF ( instr( l_bad, l_char ) > 0 )
        THEN
            l_tmp := l_tmp || '%' || to_char(ascii(l_char), 'fm0X');
        ELSE
            l_tmp := l_tmp || l_char;
        END IF;
    END LOOP;
RETURN l_tmp;
END;
Example:

SET SERVEROUTPUT ON

DECLARE

link varchar2(2000) :=  'https://mail.google.com/mail/u/1/#inbox/FMfcgxwBVMgHsGkcMPpbLJFxMgkDmNNz&^';

linkencoded varchar2(2000);

BEGIN

linkencoded := urlencode(link);

DBMS_OUTPUT.PUT_LINE (linkencoded);

END;

Comments

Popular posts from this blog

Certbot