Forum Stats

  • 3,768,190 Users
  • 2,252,758 Discussions
  • 7,874,486 Comments

Discussions

Unable to make POST Call using PLSQL

I am trying to make a post call with client credentials for an api through plsql code.

Everytime I make the call, I get tns timeout.

Attaching the code for reference and also the output.


Any suggestion on how I can tweak the code. PLSQL code on successful run should return back the access token.


Code Snippet:

SET DEFINE OFF;

SET SERVEROUTPUT ON;


DECLARE


 http_req      utl_http.req;

 l_response     utl_http.resp;

 l_value  CLOB;

 l_raw  VARCHAR2(32767);

 l_clob  CLOB;

 l_count       NUMBER;

 req_body      VARCHAR2(2000);

 v_client_id     VARCHAR2(500) := '0******************************7';

 v_client_secret   VARCHAR2(500) := 'l*************************************0';


  

BEGIN


req_body := 'grant_type=client_credentials&client_id=' || v_client_id || '&client_secret=' || v_client_secret; 

  

  

dbms_output.put_line('Setting wallet');

utl_http.set_wallet('file:/u01/app/oracle/product/12.1.0.2/dbhome_1/owm/wallets', 'xa2_Df43xP11');

 dbms_output.put_line('Setting wallet Done'); 

http_req := utl_http.begin_request( 'https://api-test.blackrock.com/oauth/token' 

 , 'POST'

 , 'HTTP/1.1');

UTL_HTTP.set_header ( http_req

, 'Content-Type'

, 'application/x-www-form-urlencoded' );

  

UTL_HTTP.set_header ( http_req

, 'Content-Length'

, LENGTH (req_body));

  

UTL_HTTP.write_text ( http_req

, req_body);

dbms_output.put_line('Initiate Request');

l_response := utl_http.get_response(http_req);

dbms_output.put_line(req_body);

utl_http.read_text(l_response, l_raw);

UTL_HTTP.end_response (l_response);

UTL_HTTP.end_request (http_req);

dbms_output.put_line('value: '||l_raw);

apex_json.parse(l_raw);

dbms_output.put_line(apex_json.get_varchar2(p_path=>'access_token'));


EXCEPTION 

WHEN OTHERS THEN


dbms_output.put_line(SQLERRM);

END;

Tagged:

Best Answer

  • Vikas Yadav 25-Oracle
    Vikas Yadav 25-Oracle Member Posts: 50 Employee
    Accepted Answer

    Below code works to make an api post and get call for client information like, client id and secret key. the bearer token generated from post call is used later to make the get call.

    Make sure to add proxy and wallet setting before the call.

    SET DEFINE OFF;

    SET SERVEROUTPUT ON;


    DECLARE


     http_req      utl_http.req;

     v_http_response     utl_http.resp;

     l_value  CLOB;

     req_body      VARCHAR2(2000);

     v_text       VARCHAR2 (32767);

     v_clob       CLOB;

     l_token VARCHAR2(100);


      

    BEGIN


    dbms_output.put_line('Setting wallet');

    utl_http.set_wallet('<wallet location>', '<wallet password>');

    dbms_output.put_line('Setting wallet Done'); 

    dbms_output.put_line('Setting Proxy');

    utl_http.set_proxy ('<your proxy here>:<proxy port>');

    dbms_output.put_line('Setting Proxy Done'); 


    dbms_output.put_line('1 POST------');

    http_req := utl_http.begin_request('<post url>'

     , 'POST'

     , 'HTTP/1.1' );

               

    dbms_output.put_line('2 POST------');

    UTL_HTTP.set_header ( http_req

    , 'client_id' 

          , '<client id>');

    dbms_output.put_line('3 POST------');   

    UTL_HTTP.set_header ( http_req

    , 'client_secret' 

          , '<client secret>' );

      

    dbms_output.put_line('4 POST------');

    UTL_HTTP.set_header ( http_req

    , 'Content-Length'

    , 0);

      

    dbms_output.put_line('5 POST------');      

    UTL_HTTP.set_header ( http_req

    , 'Content-Type'

    , 'application/x-www-form-urlencoded' );

      


    dbms_output.put_line('Initiate Request');


    v_http_response := UTL_HTTP.get_response (http_req);


        v_clob := EMPTY_CLOB;


        BEGIN

          LOOP

            UTL_HTTP.read_text (v_http_response, v_text, LENGTH (v_text));

            v_clob := v_clob || v_text;

          END LOOP;

          UTL_HTTP.end_response (v_http_response);

        EXCEPTION

          WHEN UTL_HTTP.end_of_body

          THEN

            UTL_HTTP.end_response (v_http_response);

        END;


        UTL_HTTP.end_response (v_http_response);

        dbms_output.put_line(v_clob);

    SELECT JSON_VALUE(v_clob, '$.access_token') into l_token from dual;

    dbms_output.put_line('l_token:'||l_token);

    --- GET

    /*for get call - after getting token*/

    dbms_output.put_line('Setting wallet - GET');

    sys.utl_http.set_wallet( '<wallet location>'

      , '<wallet password>');

    dbms_output.put_line('Setting wallet Done - GET'); 

    dbms_output.put_line('Setting Proxy - GET');

    utl_http.set_proxy ('<proxy address>:<port>');

    dbms_output.put_line('Setting Proxy Done - GET'); 


    dbms_output.put_line('1 GET------');

    dbms_output.put_line('Initiate Request - GET');

    http_req := sys.utl_http.begin_request( '<get call>'

    , 'GET');

      dbms_output.put_line('2 GET------');

    UTL_HTTP.set_header ( http_req

    , 'x-api-key' 

          , '<client id>');

      

    dbms_output.put_line('3 GET------');

    UTL_HTTP.set_header ( http_req

    , 'ACCEPT'

    , 'application/json');

      

    dbms_output.put_line('4 GET------');      

    UTL_HTTP.set_header ( http_req

    , 'Content-Type'

    , 'application/json' );

    dbms_output.put_line('4 .1 get------');

    /*UTL_HTTP.set_header ( http_req

    , 'Content-Length'

    , 0); */

    dbms_output.put_line('5 GET------');      

    UTL_HTTP.set_header ( http_req

    , 'Authorization'

         -- , l_token);

    , 'Bearer'||' '||l_token ); -- bearer token recieved from post call

           

          dbms_output.put_line('5.1 GET------');

    UTL_HTTP.set_body_charset (http_req, 'UTF-8');

      

    dbms_output.put_line('5.2 GET------');

    v_http_response := utl_http.get_response ( http_req ); 

     v_clob := EMPTY_CLOB;


     UTL_HTTP.read_text (v_http_response,

                        v_text,

                        LENGTH (v_text));

              v_clob := v_clob || v_text;



          dbms_output.put_line(v_clob);


    EXCEPTION 

    WHEN OTHERS THEN


    dbms_output.put_line(SQLERRM);

     UTL_HTTP.end_response (v_http_response);

    END;

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,581 Red Diamond

    You have shared secrets via your copy code and paste. Don't.

    Here is a partial code extract from a package used to interface with Google App Cloud, and it includes initialising HTTP_UTL and doing a HTTP POST (default is JSON data). It covers the basics:

    -- ---------------------------------------------------------------------------------------------------------------
    -- initialises the UTL_HTTP interface
    procedure HttpInit is
    begin
        DebugWrite( 'HttpInit():' );
    
        -- our "browser" settings
        UTL_HTTP.set_response_error_check( false );
        UTL_HTTP.set_detailed_excp_support( true );
        UTL_HTTP.set_cookie_support( true );
        UTL_HTTP.set_transfer_timeout( HTTP_TIMEOUT );
        UTL_HTTP.set_follow_redirect( 10 );
        UTL_HTTP.set_persistent_conn_support( false );
    
        -- set wallet for HTTPS access
        if WALLET is not null then
            UTL_HTTP.set_wallet( WALLET, WALLET_PASS );
        end if;
    
        -- configure for proxy access
        if HTTP_PROXY is not null then
            UTL_HTTP.set_proxy( HTTP_PROXY, HTTP_NO_PROXY );
        end if;
    end;
    
    
    -- ---------------------------------------------------------------------------------------------------------------
    -- perform a HTTP POST command and returns response as a CLOB
    function HttpPost( url varchar2, payload varchar2, contentType varchar2 default MIME_JSON, key varchar2 default null ) return clob is
        request   UTL_HTTP.req;
        response  UTL_HTTP.resp;
        output    clob;
        buffer    varchar2(4000);
        endLoop   boolean;
        name      varchar2(256);
        val       varchar2(1024);
    begin
        DebugWrite( 'HttpPost(): url='||url||' contentType='||contentType||' payload='||payload );
        httpStatus.code := null;
        httpStatus.message := null;
        request := UTL_HTTP.begin_request( url, 'POST', UTL_HTTP.HTTP_VERSION_1_1 );
    
        -- set HTTP header
        DebugWrite( '========== HTTP HEADER ==========' );
        httpStatus.authKey := nvl( key, httpStatus.authKey );  -- does the caller override the stored key?
        if httpStatus.authKey is not null then
            UTL_HTTP.set_header( request, 'Authorization', 'JWT '||httpStatus.authKey );
            DebugWrite( 'Authorization:'||'JWT '||httpStatus.authKey );
        end if;
        UTL_HTTP.set_header( request, 'User-Agent', USER_AGENT );
        UTL_HTTP.set_header( request, 'Content-Type', contentType );
        UTL_HTTP.set_header( request, 'Content-Length', length(payload) );
        UTL_HTTP.write_text( request, payload );
    
        -- for debugging the POST header
        DebugWrite( 'User-Agent:'||USER_AGENT );
        DebugWrite( 'Content-Type:'||contentType );
        DebugWrite( 'Content-Length:'||length(payload) );
    
        -- get response from web server
        response := UTL_HTTP.get_response( request );
    
        -- as UTL_HTTP is set not to raise exceptions, we record the status
        -- of the HTTP call in a private global var struct
        httpStatus.code := response.status_code;
        httpStatus.message := response.reason_phrase;
    
        -- debug trace for response to the POST
        DebugWrite( '========== HTTP RESPONSE ==========' );
        DebugWrite( 'HTTP status='||httpStatus.code );
        DebugWrite( 'HTTP message='||httpStatus.message );
        for i in 1 .. UTL_HTTP.get_header_count(response) loop
            UTL_HTTP.get_header(response, i, name, val );
            DebugWrite( name||'='||val );
        end loop;
    
        -- read the response into a CLOB
        DBMS_LOB.CreateTemporary( output, true );
        endLoop := false;
    
        while not endLoop loop
            begin
                UTL_HTTP.read_text( response, buffer, 4000 );
                if (buffer is not null) and length(buffer)>0 then
                    DBMS_LOB.WriteAppend(
                        output, length(buffer), buffer
                    );
                end if;
            exception when UTL_HTTP.END_OF_BODY then
                    endLoop := true;
            end;
        end loop;
        UTL_HTTP.end_response( response );
        DebugWrite( substr(output,1,32767) );
        DebugWrite( '========== HTTP END ==========' );
    
        -- returns the response to the caller - note the response can also
        -- contain an error response as UTL_HTTP is configured not to raise
        -- exceptions for HTTP error codes returned
        return( output );
    end;
    


      

  • Vikas Yadav 25-Oracle
    Vikas Yadav 25-Oracle Member Posts: 50 Employee

    thanks for the update.

    But it seems the client id and client secret needs to be passed in header section to make it work.

    Tested the call through postman and provided the client details in header section and it worked. So I believe the same should be setup in plsql code .

    Any thought on that?

  • Vikas Yadav 25-Oracle
    Vikas Yadav 25-Oracle Member Posts: 50 Employee

    I see my code fails at the ' begin_request' call itself.

    Adding the header details comes later. Any detail on why the begin request itself fails? Have I missed any additional parameter needed for the call?


    Code reference:

    SET DEFINE OFF;

    SET SERVEROUTPUT ON;


    DECLARE


     http_req      utl_http.req;

     l_response     utl_http.resp;

     l_value  CLOB;

     l_raw  VARCHAR2(32767);

     l_clob  CLOB;

     l_count       NUMBER;

     req_body      VARCHAR2(2000);



      

    BEGIN


    dbms_output.put_line('Setting wallet');

    utl_http.set_wallet('file:/u01/app/oracle/product/12.1.0.2/dbhome_1/owm/wallets', 'xa2_Df43xP11');

      dbms_output.put_line('Setting wallet Done'); 

    dbms_output.put_line('1------');

    http_req := utl_http.begin_request( sys.UTL_URL.escape ('https://api-test.blackrock.com/oauth/token')

     , 'POST'

     , 'HTTP/1.1');

    dbms_output.put_line('2------');

    UTL_HTTP.set_header ( http_req

    , 'client_id'

    , '***************************');

    dbms_output.put_line('3------');   

    UTL_HTTP.set_header ( http_req

    , 'client_secret'

    ,'*************************************');

    dbms_output.put_line('4------');

    UTL_HTTP.set_header ( http_req

    , 'Content-Type'

    , 'application/x-www-form-urlencoded' );

    /*   

    UTL_HTTP.set_header ( http_req

    , 'Content-Length'

    , LENGTH (req_body));

    */

    dbms_output.put_line('Initiate Request');

    l_response := utl_http.get_response(http_req);


    dbms_output.put_line('call worked');

    EXCEPTION 

    WHEN OTHERS THEN


    dbms_output.put_line(SQLERRM);

    END;

  • Paulzip
    Paulzip Member Posts: 8,489 Blue Diamond
    edited May 18, 2021 7:49AM

    Your failure is from your network, not from utl_http. The end point doesn't seem to be reachable from your server, and so is timing out. Speak to your IT dept and get them to check the external access restrictions, and get them to check access from the shell / command line with something like cURL.

    You can also set ult_http.set_detailed_excp_support(True); before your call, and sometimes get more detailed exception info. Get rid of your idiotic when OTHERS .

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,581 Red Diamond

    But it seems the client id and client secret needs to be passed in header section to make it work.

    Yes, but do not show secrets when posting code to a public forum - or write secrets to trace/dbms_output etc.

    The TNS timeout indicates a TCP socket timeout.

  • Vikas Yadav 25-Oracle
    Vikas Yadav 25-Oracle Member Posts: 50 Employee
    Accepted Answer

    Below code works to make an api post and get call for client information like, client id and secret key. the bearer token generated from post call is used later to make the get call.

    Make sure to add proxy and wallet setting before the call.

    SET DEFINE OFF;

    SET SERVEROUTPUT ON;


    DECLARE


     http_req      utl_http.req;

     v_http_response     utl_http.resp;

     l_value  CLOB;

     req_body      VARCHAR2(2000);

     v_text       VARCHAR2 (32767);

     v_clob       CLOB;

     l_token VARCHAR2(100);


      

    BEGIN


    dbms_output.put_line('Setting wallet');

    utl_http.set_wallet('<wallet location>', '<wallet password>');

    dbms_output.put_line('Setting wallet Done'); 

    dbms_output.put_line('Setting Proxy');

    utl_http.set_proxy ('<your proxy here>:<proxy port>');

    dbms_output.put_line('Setting Proxy Done'); 


    dbms_output.put_line('1 POST------');

    http_req := utl_http.begin_request('<post url>'

     , 'POST'

     , 'HTTP/1.1' );

               

    dbms_output.put_line('2 POST------');

    UTL_HTTP.set_header ( http_req

    , 'client_id' 

          , '<client id>');

    dbms_output.put_line('3 POST------');   

    UTL_HTTP.set_header ( http_req

    , 'client_secret' 

          , '<client secret>' );

      

    dbms_output.put_line('4 POST------');

    UTL_HTTP.set_header ( http_req

    , 'Content-Length'

    , 0);

      

    dbms_output.put_line('5 POST------');      

    UTL_HTTP.set_header ( http_req

    , 'Content-Type'

    , 'application/x-www-form-urlencoded' );

      


    dbms_output.put_line('Initiate Request');


    v_http_response := UTL_HTTP.get_response (http_req);


        v_clob := EMPTY_CLOB;


        BEGIN

          LOOP

            UTL_HTTP.read_text (v_http_response, v_text, LENGTH (v_text));

            v_clob := v_clob || v_text;

          END LOOP;

          UTL_HTTP.end_response (v_http_response);

        EXCEPTION

          WHEN UTL_HTTP.end_of_body

          THEN

            UTL_HTTP.end_response (v_http_response);

        END;


        UTL_HTTP.end_response (v_http_response);

        dbms_output.put_line(v_clob);

    SELECT JSON_VALUE(v_clob, '$.access_token') into l_token from dual;

    dbms_output.put_line('l_token:'||l_token);

    --- GET

    /*for get call - after getting token*/

    dbms_output.put_line('Setting wallet - GET');

    sys.utl_http.set_wallet( '<wallet location>'

      , '<wallet password>');

    dbms_output.put_line('Setting wallet Done - GET'); 

    dbms_output.put_line('Setting Proxy - GET');

    utl_http.set_proxy ('<proxy address>:<port>');

    dbms_output.put_line('Setting Proxy Done - GET'); 


    dbms_output.put_line('1 GET------');

    dbms_output.put_line('Initiate Request - GET');

    http_req := sys.utl_http.begin_request( '<get call>'

    , 'GET');

      dbms_output.put_line('2 GET------');

    UTL_HTTP.set_header ( http_req

    , 'x-api-key' 

          , '<client id>');

      

    dbms_output.put_line('3 GET------');

    UTL_HTTP.set_header ( http_req

    , 'ACCEPT'

    , 'application/json');

      

    dbms_output.put_line('4 GET------');      

    UTL_HTTP.set_header ( http_req

    , 'Content-Type'

    , 'application/json' );

    dbms_output.put_line('4 .1 get------');

    /*UTL_HTTP.set_header ( http_req

    , 'Content-Length'

    , 0); */

    dbms_output.put_line('5 GET------');      

    UTL_HTTP.set_header ( http_req

    , 'Authorization'

         -- , l_token);

    , 'Bearer'||' '||l_token ); -- bearer token recieved from post call

           

          dbms_output.put_line('5.1 GET------');

    UTL_HTTP.set_body_charset (http_req, 'UTF-8');

      

    dbms_output.put_line('5.2 GET------');

    v_http_response := utl_http.get_response ( http_req ); 

     v_clob := EMPTY_CLOB;


     UTL_HTTP.read_text (v_http_response,

                        v_text,

                        LENGTH (v_text));

              v_clob := v_clob || v_text;



          dbms_output.put_line(v_clob);


    EXCEPTION 

    WHEN OTHERS THEN


    dbms_output.put_line(SQLERRM);

     UTL_HTTP.end_response (v_http_response);

    END;