PL/SQL UTL_HTTP.REQUEST - Curly Braces/Semicolon

Question

I'm trying to call a URL using the UTL_HTTP package in Oracle Autonomous Database and getting an ORA-06512:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYS.UTL_HTTP", line 1810
ORA-06512: at "SYS.UTL_HTTP", line 144
ORA-06512: at "SYS.UTL_HTTP", line 1745
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

All network ACLS etc. are configured correctly.

The URL in question requires JSON directly in the URL which I understand is bad practice, however I don't have control over the API. If I remove the curly braces ( {} ) and semicolons ( : ), the request using UTL_HTTP works but the output is incorrect as the JSON becomes invalid.

I've tried encoding the URL using an online encoder as well as the UTL_URL.ESCAPE function, which gives the same error as above.

The encoding from UTL_URL.ESCAPE IS:

An example of the URL format is:

https://example.com/rest.php?entity=contact&action=get&json={"name": "Lorum Ipsum", "end_date": {"IS NULL": 1}, "return": "id,contact_id"}

I've tried a straight select using utl_http.request:

SELECT UTL_HTTP.REQUEST('https://example.com/rest.php?entity=contact&action=get&json={"name": "Lorum Ipsum", "end_date": {"IS NULL": 1}, "return": "id,contact_id"}') FROM DUAL;

And calling using PL/SQL:

declare
    req UTL_HTTP.REQ;
    resp UTL_HTTP.RESP;
    val VARCHAR2(2000);
    str varchar2(1000);
begin
    req := UTL_HTTP.BEGIN_REQUEST('https://example.com/rest.php?entity=contact&action=get&json={"name": "Lorum Ipsum", "end_date": {"IS NULL": 1}, "return": "id,contact_id"}');
    resp := UTL_HTTP.GET_RESPONSE(req);
    LOOP
        UTL_HTTP.READ_LINE(resp, val, TRUE);
        DBMS_OUTPUT.PUT_LINE(val);
    END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY
THEN
    UTL_HTTP.END_RESPONSE(resp);
END;

/

How do I call this url using UTL_HTTP?

please edit the question and show the relevant pl/sql code. thanks — OldProgrammer, May 25 '21 at 15:12
How did you try encoding the URL? Using `utl_url.escape` or some other function? Could you show the output? — kfinity, May 25 '21 at 15:36
@kfinity I've edited the original question with more information on the encoding. I can't provide the exact output from the actual URL as it contains data, but the example url comes out with the same hex encodings for URLs. `https://example.com/rest.php?entity=contact&action=get&json=%7B%22name%22:%20%22Lorum%20Ipsum%22,%20%22end_date%22:%20%7B%22IS%20NULL%22:%201%7D,%20%22return%22:%20%22id,contact_id%22%7D` I have just noticed the colons aren't getting encoded by the UTL_URL package which could be the issue. — noiix, May 25 '21 at 15:57

score 1 · Accepted Answer · answered May 25 '21 at 17:40

They talk about this a bit in the UTL_URL documentation, but colons are reserved characters - they're valid in some parts of a URL, but not in others. (Curly braces are not allowed at all.) There's been some debate about whether or not they're valid in the Query portion of a URL - the ABNF in the RFC seems to say yes, but a lot of implementations don't allow it.

By default, UTL_URL.ESCAPE doesn't escape reserved characters (because that would screw up the https:// part, etc), but you can change that by passing TRUE as the second argument. In general, best practice seems to be escaping reserved characters in any query string that might contain them:

req := UTL_HTTP.BEGIN_REQUEST('https://example.com/rest.php?entity=contact&action=get&json=' 
       || utl_url.escape('{"name": "Lorum Ipsum", "end_date": {"IS NULL": 1}, "return": "id,contact_id"}'
            ,TRUE));

See if that helps?

This fixes the issue. The documentation does state that it won't escape colons, for good reason as you pointed out. Using a concatenation like you did, or using the replacing the colons with %3A. Your solution is more elegant. Thanks! — noiix, May 26 '21 at 11:23

PL/SQL UTL_HTTP.REQUEST - Curly Braces/Semicolon

1 Answers1