PL/SQL Call webservice https ORA-03113 end-of-file on communication channel

Question

I am trying to talk with Google calendar. I have problem with authentication. I write down the code below.

create or replace function authenticate_service(
        p_email in varchar2,
        p_password in varchar2)    return varchar2  is
        l_request           utl_http.req;
        l_response          utl_http.resp;
        l_params            varchar2(255);
        l_resp_data         varchar2(4000);
        l_auth_token        varchar2(4000);     begin

        -- access the oracle wallet to allow us to make an https request
        utl_http.set_wallet(
            path => 'file: ***',
            password => '***');

        -- set up the request body with our credentials
        l_params := 'Email=' || p_email || '&Passwd=' || p_password ||
                        '&service=cl' || '&source=e-DBA-test-1.0';

        l_request := utl_http.begin_request(
                        'https://www.google.com/accounts/ClientLogin',
                        'POST',
                        'HTTP/1.1');

        -- set the request headers
        utl_http.set_header(
            l_request,
            'Content-Type',
            'application/x-www-form-urlencoded');
        utl_http.set_header(
            l_request,
            'Content-Length',
            length(l_params));

        -- write out the request body
        utl_http.write_text( l_request, l_params );

        -- get the response
        l_response := utl_http.get_response( r => l_request );

        dbms_output.put_line('Status Code: '||l_response.status_code);

     begin
        loop
            utl_http.read_line( r => l_response, data => l_resp_data,remove_crlf => TRUE);
            if substr(l_resp_data, 1, 5) = 'Auth=' then
                l_auth_token := substr(l_resp_data, 6);
            end if;
        end loop;
     exception
        when utl_http.end_of_body then
            null;
     end;
        utl_http.end_response ( l_response );
    dbms_output.put_line(l_auth_token);      return l_auth_token;
        end authenticate_service;

Everything works fine but... When I try to call authentication a few times a row, sometimes I found this error Oracle ORA-03113: end-of-file on communication channel and then ORA-03114: not connected to ORACLE. I have no idea why it happens and how to fix this.

You should (ask your DBA to) check server side in the alert log/trace files if there are any error messages. The cause of the problem probably is server side error. — Rob van Laarhoven
ORA-03114 basically means that the connection between client and server is broken. This usually means the client process on the server or the SQL*Net connection on the server died. — Rob van Laarhoven
In log.xml I found this Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xAE0BE38] [PC:0x7FEBB33A7F8A, nzos_Create_Ctx()+264] [flags: 0x0, count: 1] and ORA-07445: napotkano wyj±tek: zrzut pamięci [nzos_Create_Ctx()+264] [SIGSEGV] [ADDR:0xAE0BE38] [PC:0x7FEBB33A7F8A] [Address not mapped to object] [] — widzimusie
I'm afraid this is an error we can not help you with. It's Polish ;-) and second for these type of errors you should log a SR with Oracle Technical support, it's an exception in the Oracle source code. blogs.oracle.com/db/entry/ora-7445_troubleshooting — Rob van Laarhoven

DazzaL DazzaL · Accepted Answer · 2012-11-13T15:48:14

you should ask oracle support, raise a tar. that function "nzos_Create_Ctx" is related to the certificate area but there's no matches on oracle support that i can see for this error. You may want to play around with the wallet as it may not be compatible in some way. ie are you sure the wallet is valid etc. oracle would want you to send in the trace dump file + testcase to diagnose this.

how did you create the wallet and what certificates did you put in it (no client certificates right?)

to follow up, i tried your code and it works properly on a test db here (11g). i created a wallet (standard type, not pkcs11 option) using oracle wallet manager (without making a cert request) and added the intermediate Thwarte certificate. i then called your function (changing on the wallet location to:

 utl_http.set_wallet(
        path => 'file:/u01/app/oracle/product/11.2.0/owm/wallets/oracle',
        password => 'test1234');

and got back a proper response:

SQL> select authenticate_service('[email protected]', 'foo') from dual;

AUTHENTICATE_SERVICE('[email protected]','foo')
--------------------------------------------------------------------------------
DQAAAMUAAAA5n...etc...

ie my certificate store looked like this:

OWM on linux

PL/SQL Call webservice https ORA-03113 end-of-file on communication channel

1 Answers