Skip to Main Content
  • Questions
  • Uploading files in a Rest webservice using PlSql UTL_HTTP

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rodrigo.

Asked: March 30, 2020 - 10:17 am UTC

Answered by: Chris Saxon - Last updated: April 06, 2020 - 1:40 pm UTC

Category: PL/SQL - Version: PL/SQL Release 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi,

I'm calling REST web services using PLSQL UTL_HTTP package, in JSON, with no problems.

However, now i need to invoke a WS but attaching a file.


I can do it with Postman in “form-data” mode.

In PLSQL, I've tried the same thing in the body of the request:

{
"file":"/C:Users/myuser/Desktop/image_01.jpg"
}


as this is the syntax that Postman uses. The WS executes, but returns with an error tag.


I've also tried using this (code snipet), but the result is the same, it executes but the result is not what it shoud be:



DECLARE

  l_buffer    RAW(32767);

  l_amount    INTEGER := 32767;

  l_pos       INTEGER := 1;

  l_blob_len  PLS_INTEGER;

BEGIN

   /* p_file is the file to upload as a BLOB parameter */

  IF p_file IS NOT NULL THEN

    BEGIN

      l_blob_len := dbms_lob.getlength(p_file);

      utl_http.set_header(v_req, 'Content-Type', 'application/octet-stream'); -- v_req is a parameter

      utl_http.set_header(v_req, 'Content-Length', l_blob_len);

     
      WHILE l_pos < l_blob_len LOOP

        dbms_lob.read(p_file, l_amount, l_pos, l_buffer);

        utl_http.write_raw(v_req, l_buffer);

        l_pos := l_pos + l_amount;

      END LOOP;

    EXCEPTION

      WHEN OTHERS THEN

        p_err := SQLCODE;

        p_msg := 'ERROR: ' || SQLERRM;

    END;

  END IF;

END;



Any help would be greatly appreciated.

Rodrigo Coutinho
Portugal

and we said...

I take it:

/C:Users/myuser/Desktop/image_01.jpg


Is supposed to be a location on YOUR machine?

If so you can't use PL/SQL to read it. The file must exist in a location accessible to the database server.

I'm not sure exactly what you're doing, but you probably need to do something along the lines of:

- Copy the file to the database server
- Use UTL_file to read it
- Attach it to the REST request using UTL_HTTP

If this is a process where an end-user will be uploading files... you're going to need to rethink your strategy.

and you rated our response

  (6 ratings)

Reviews

Correcting my explanation

March 30, 2020 - 5:37 pm UTC

Reviewer: A reader

Hi again,

maybe i mislead you, but the /C:Users/myuser/Desktop/image_01.jpg, was just a quick try.

In the next example i supplied, the "p_file" is indeed a blob file taken from a database column.

It is precisely in the "Attach it to the REST request using UTL_HTTP" as you say, that the problem lies.

I believe i'm close, but haven't got it yet !

Regards
Connor McDonald

Followup  

March 31, 2020 - 12:50 am UTC

Here's a related post that could help

https://asktom.oracle.com/pls/apex/asktom.search?tag=utl-http-post-with-filename

Call AWS REST service from Oracle database

April 01, 2020 - 7:32 pm UTC

Reviewer: Gaurang

Hi Chris - We've a requirement to call AWS REST service from Oracle database. I know we can invove REST service from database BUT there are lots of security in AWS layer.

what we need to do to make this happen? Oracle wallet ?

Appreciate your help
Connor McDonald

Followup  

April 02, 2020 - 3:27 am UTC

Here's a good writeup from the APEX team on that

https://blogs.oracle.com/apex/apex-https-certificates-and-the-oracle-wallet


Continuing my explanation

April 02, 2020 - 9:32 am UTC

Reviewer: Rodrigo Coutinho from Portugal

I'm sorry if this isn't the right way of continuing this question, but i'm new here and didn't find out how to continue.

Replying to your comment that the file in my PC is not accessible to PLSQL, i must say that i have created a database directory and put a file there.

So, how can i now send that file in the WS call ?
I did tried making the same call, but now pointing to the oracle directory, instead of my C: drive, but the result was the same !

Thank you again
Chris Saxon

Followup  

April 02, 2020 - 10:09 am UTC

So what exactly have you done? Have you checked the link Connor posted?

Show us how you've made the file available to the database and the web service call you're making.

Here's the code

April 02, 2020 - 4:59 pm UTC

Reviewer: A reader

The webservice expects this:

Post to http://{IP/DNS}/upload/upload2tickets.php

Body parameters:

KEY VALUE
file (required) 111.pdf file to send


DECLARE
  req        utl_http.req;
  resp       utl_http.resp;
  v_content  blob;
  amount     number := 2000;
  req_length NUMBER;
  v_offset   NUMBER := 1;
  v_buffer   varchar2(4000);
  l_response_header_name varchar2(256);
  l_response_header_value varchar2(1024);
  l_request_body clob;
  l_response_body varchar2(32767);
  
BEGIN

  -- The image file
  SELECT hd.hdk_anexo_1
  INTO   v_content
  FROM   table hd
  WHERE  hd.hdk_id = 999;

utl_http.set_wallet(
    path => 'file:/xxx/apps/xxx/xxx/19.0.0/wallet',
    password => 'password'
  );

  utl_http.set_proxy ('proxy.xxx.xxx:8080');

  req := utl_http.begin_request(
                      url => 'https://cloudcc.nos.pt/upload/upload2tickets.php',
                      method => 'POST',
                      http_version => 'HTTP/1.1'
                    );

  utl_http.set_header(req, 'user-agent',    'mozilla/4.0');
  utl_http.set_header(req, 'Authorization', 'Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJpZmFwLmNsb3VkY2Mubm9zLnB0IiwiaXNzVXVpZCI6IjdlNGU1OGQwLWM3NDAtNDA2Ni05YzY1LWY3NmQzY2Y2MDE0OSIsImp0aSI6IjQwZDViZjMwLWYwNDktNDM3Yy1iYTFhLTk4MmUxODFiYmQ5ZCIsIm5hbWUiOiJ1c2VyX2FwaSIsInBlcm0iOltdLCJyYW5nZUxpc3QiOltdLCJpYXQiOjE1ODU1Nzc1MzYsImV4cCI6MTU4NjE4MjMzNn0.fHWRbxOUtTx-zb-14AeCBaL-8U5BbpinuW7tYtqzh');
  utl_http.set_header(req, 'content-type',  'image/jpeg');

  req_length := dbms_lob.getlength(v_content);

  if req_length <= 32767   then 
    utl_http.set_header(req, 'Content-Length', req_length);
    utl_http.write_raw(req, v_content);
  elsif req_length > 32767 THEN

    utl_http.set_header(req, 'Transfer-Encoding', 'Chunked');

    while (v_offset < req_length) LOOP
      dbms_lob.read(v_content, amount, v_offset, v_buffer);
      utl_http.write_raw(req, v_buffer);
      v_offset := v_offset + amount;
    end loop;
  end if;

  resp := UTL_HTTP.get_response(req);
  
  for i in 1 .. utl_http.get_header_count(resp) loop
    utl_http.get_header(resp, i, l_response_header_name, l_response_header_value);
    dbms_output.put_line('Response Header> ' || l_response_header_name || ': ' || l_response_header_value);
  end loop;
  
  utl_http.read_text(resp, l_response_body, 32767);
  dbms_output.put_line('Response body>');
  dbms_output.put_line(l_response_body);

  -- Process the response from the HTTP call
    IF resp.status_code = utl_http.HTTP_OK AND resp.reason_phrase = 'OK' THEN
      dbms_output.put_line ('Webservice without errors');
    ELSE
      dbms_output.put_line ('Webservice errors=' || resp.status_code || '-' || resp.reason_phrase);
    END IF;
    utl_http.end_response (resp);

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line ('ERROR Others=' || SQLERRM);
    utl_http.end_response (resp);
END;



Result:
Response Header> Date: Thu, 02 Apr 2020 15:49:30 GMT
Response Header> Server: Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips PHP/5.4.16
Response Header> X-Powered-By: PHP/5.4.16
Response Header> Expires: Thu, 19 Nov 1981 08:52:00 GMT
Response Header> Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Response Header> Pragma: no-cache
Response Header> Vary: Origin
Response Header> Content-Length: 45
Response Header> Connection: close
Response Header> Content-Type: text/html; charset=UTF-8
Response body>
{"uuid":"","info":null,"error":"","file":[]}
Webservice without errors


It was suposed to return a value in the "uuid" field, but it comes out empty.

However, i also question if this is suppose to work, since the WS spec states a Key/Value pair.
Maybe it only works that way and not like my code tries to do, since if i call it from Postman with the Key/Value in form-data, it works !!
I don't know what Postman does in the background, but i get the feeling i must include the "file" key, somewhere in my code!

Alternatively, i also tried to mimmick Postman and include in the body:

{
"file":"oracle_directory/filename"
}


but no luck !
Chris Saxon

Followup  

April 03, 2020 - 10:16 am UTC

Where are you getting the PDF itself and encoding it? I can't see anywhere in that code that calls util_file (or similar) to load the document.

continue ...

April 02, 2020 - 5:00 pm UTC

Reviewer: A reader

since the WS spec states a Key/Value pair.
Maybe it only works that way and not like my code tries to do, since if i call it from Postman with the Key/Value in form-data, it works !!
I don't know what Postman does in the background, but i get the feeling i must include the "file" key, somewhere in my code!

Alternatively, i also tried to mimmick Postman and include in the body:

{
"file":"oracle_directory/filename"
}


but no luck !
Chris Saxon

Followup  

April 03, 2020 - 10:17 am UTC

I don't know how postman does this, I'm guessing it has some magic to read the file when making the request.

Passing the directory/filename in your request won't cut it. You need to write code to read the file and encode it.

Blob File

April 03, 2020 - 10:33 am UTC

Reviewer: A reader

The file is in a table/column, in the beggining of the code:

-- The image file
 SELECT hd.hdk_anexo_1
  INTO   v_content
  FROM   table hd
  WHERE  hd.hdk_id = 999;


The hd.hdk_anexo_1 is a BLOB column that contains a jpeg file.


Chris Saxon

Followup  

April 06, 2020 - 1:40 pm UTC

I'm not sure what the issue is here. Log the requests you're sending via PL/SQL so you can try sending them manually via Postman or similar.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.