Posts

Showing posts from January, 2019

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 ONDECLARElink varchar2(2000) :=  'https://mail.google.com/mail/u/1/#inbox/FMfcgxwBVMgHsGkcMPpbLJFxMgkDmNNz&^';linkencoded varchar2(2000);BEGINlinkencoded := urlencode(link);DBMS_OUTPUT.PUT_LINE (linkencoded);END;
Check if UTL_HTTP exists:
I have seen on the internet someone complain about not existing "UTL_HTTP"
SELECT * FROM dba_objects WHERE object_name='UTL_HTTP'
Login do sqlplus:

sys as sysdbaGrant execute to user, in my case im using system user (I know bad idea):
GRANT EXECUTE ON SYS.UTL_HTTP TO system;
Create ACL, you need this if yo are using 11g or up:
pay attention to principal it has to be UPPERCASE, if not it will give you "ORA-44416 Unresolved Principal" error
BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl( acl => 'http_connect.xml', description => 'ACL that lets me talk to the my web server', principal => 'SYSTEM', is_grant => TRUE, privilege => 'connect' ); DBMS_NETWORK_ACL_ADMIN.assign_acl( acl => 'http_connect.xml', HOST => 'maks.tk', lower_port => 80, upper_port =…