I'm trying to debug an "HTTP Request Failed" error on PL/SQL for a function trying to consume a SOAP web service. The suggestion is to use get_detailed_sqlerrm to get the details of the error message, but when I try to run the suggested query, it throws an "invalid SQL statement" error.
My query:
UTL_HTTP.get_detailed_sqlerrm RETURN VARCHAR2; I'm using the PL/SQL Developer IDE and the database is Oracle 11g.
11 Answer
You should probably run
select utl_http.get_detailed_sqlerrm from dual; Just beware - it isn't a general purpose function, works for UTL_HTTP errors only. For example, it won't work for SQL errors:
SQL> select deptno, min(sal) from emp; select deptno, min(sal) from emp * ERROR at line 1: ORA-00937: not a single-group group function SQL> select utl_http.get_detailed_sqlerrm from dual; GET_DETAILED_SQLERRM -------------------------------------------------------------------------------- ... nor for PL/SQL errors:
SQL> begin 2 select empno from emp; 3 end; 4 / select empno from emp; * ERROR at line 2: ORA-06550: line 2, column 3: PLS-00428: an INTO clause is expected in this SELECT statement SQL> select utl_http.get_detailed_sqlerrm from dual; GET_DETAILED_SQLERRM -------------------------------------------------------------------------------- SQL> ... but would work for UTL_HTTP-related errors:
SQL> declare 2 l_request utl_http.req; 3 begin 4 l_request := utl_http.begin_request('); 5 end; 6 / declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 4 SQL> select utl_http.get_detailed_sqlerrm from dual; GET_DETAILED_SQLERRM -------------------------------------------------------------------------------- ORA-24247: network access denied by access control list (ACL) SQL> 
