Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

utl_http call for token API not working Bad request or Invalid credentials

Pankaj SOct 12 2023

I am using utl_http for the first time, using documentation and online resources to build my code.
I am trying to call an API that is returning a token.
I have the API calls working using POSTMAN.

But I am not able to do the same on PLSQL side using utl_http .
I keep getting Bad request error
or credential invliad error, but I know I have right credentials in my code same as POSTMAN.

Not able to figure what I am missing.

here is the image from POSTMAN

header

Body

Here is my code



declare
    req utl_http.req;
    res utl_http.resp;
    
    l_wallet_path 		varchar2(256):= 'file:/mywallet/wallet';
    
    lvc_auth_url       	varchar2(32767):= 'https://myurl.com/api/oauth2/token';  
        
        
    lvc_username  		varchar2(20):='MyUserNAME' ;
    lvc_pwd 			varchar2(20):='MyUserPASS' ; 
    lvc_usr_pwd			varchar2(40):= 'MyUserNAME:MyUserPASS';
    
    lvc_autorization	varchar2(100):= 'Basic QzkzZ0N6amVCbGlaNWlXdEF1dUVnemasaZFcEFpMXdzTE46TXFvdWxpcW85UExBbjM2Ug==';
    lvc_content_type	varchar2(100):=	'application/x-www-form-urlencoded';
    
    lvc_body			varchar2(4000):= '{
                                    "grant_type": "password",
                                    "username": "MyUserNAME",
                                    "password": "MyUserPASS",
                                    "channel"= "Mychannel"
                                   }';
           
   lvc_basic_base64 	varchar2(100):=UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ( lvc_username || ':' || lvc_pwd)));
    
    buffer 				varchar2(4000); 
    endLoop				boolean;
 
begin   

    -- making request
    begin
        --utl_http.set_persistent_conn_support(true, 30);
    
        utl_http.set_transfer_timeout(15);
        utl_http.set_detailed_excp_support(true);
        utl_http.set_wallet(l_wallet_path, 'MywalletPASS');
       
     
        req := utl_http.begin_request(lvc_auth_url, 'POST');
               
        utl_http.set_header(req, 'Content-Type', lvc_content_type);
        
        utl_http.set_header(req, 'Authorization', lvc_autorization);
            
        utl_http.write_text(req, lvc_body);

        utl_http.set_header(req, 'Content-Length', nvl(length(lvc_body),0) );
        
        utl_http.set_header(req, 'Authorization', 'Basic ' || lvc_basic_base64);
             
        res := utl_http.get_response(req);
    
    exception
        when utl_http.request_failed 
            then dbms_output.put_line('ERROR : Request Failed : ' || utl_http.get_detailed_sqlerrm );
                utl_http.end_response(res);
        when others
            then dbms_output.put_line('RESPONSE ERROR' || SQLERRM);
            utl_http.end_response(res);
    end;    
        
    dbms_output.put_line('RESPONSE Received');
    
    -- process the response from the HTTP call
    begin
      
        dbms_output.put_line('Reading the RESPONSE');
       
        dbms_output.put_line ('Status code: ' || res.status_code);

        dbms_output.put_line ('Reason : ' || res.reason_phrase);       
           
        loop
                exit when endLoop;
         
                begin
                        utl_http.read_line( res, buffer, true );
                                
                        if (buffer is not null) and length(buffer)>0 then
                                dbms_output.put_line(buffer);
                        end if;
                                
                exception when utl_http.END_OF_BODY then
                        endLoop := true;
                end;
         
        end loop;        
                    
        utl_http.end_response(res);
                    
        dbms_output.put_line('RESPONSE read complete');
    
    exception
        when utl_http.end_of_body 
            then utl_http.end_response(res);            	
        when others
            then dbms_output.put_line('RESPONSE ERROR' || SQLERRM);
            utl_http.end_response(res);
    end;

exception
        when others
            then dbms_output.put_line('MAIN ERROR : '|| SQLERRM);
            utl_http.end_response(res);  

end;
This post has been answered by Zoltán on Oct 12 2023
Jump to Answer
Comments
Post Details
Added on Oct 12 2023
7 comments
1,026 views