Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, SB.

Asked: May 04, 2017 - 2:04 pm UTC

Last updated: November 28, 2023 - 3:39 am UTC

Version: Oracle Database 12c

Viewed 10K+ times! This question is

You Asked


I am trying to POST the webservice all using UTL_HTTP function with the file from pl/sql procedures. The same processed working from Curl scripts.

 req   utl_http.req;
 resp  utl_http.resp;
 l_value VARCHAR2(1024);
 l_url_import    VARCHAR2(500) := '';
 l_url VARCHAR2(100) := '';
 l_content VARCHAR2(1000) := 'C:\SB\TeamDynamix\Files\xavier_to_teamdynamics.xlsx' ;
  req := UTL_HTTP.begin_request( l_url, 'POST', UTL_HTTP.HTTP_VERSION_1_1 );    
  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0 (compatible)' );  
  UTL_HTTP.SET_HEADER(req, 'Content-Type', 'application/json');
  UTL_HTTP.SET_HEADER(req, 'Accept', 'application/json');
 --- UTL_HTTP.SET_HEADER(req, 'Content-Length', 100000000);--LENGTH(l_Content));
  UTL_HTTP.WRITE_text(req,  l_content);
  resp := utl_http.get_response(req); 
    utl_http.read_line(resp, l_value, TRUE);
  WHEN utl_http.end_of_body THEN

Curl: Working script

. $HOME/.bash_profile

#file name as it will be stored in the dataload folder
file=$UTLFILE/general/xavier_to_teamdynamix.xlsx    #PeopleApiImportTemplate.xlsx #xavier_to_teamdynamics.xlsx

#run the curl command and store the output from command in variable return_key
echo "File Name: [$file]"
read return_key < <(curl "" -H "Content-type: application/json" -d "{'BEID': 'XXXXXX-XXXX-XXX-XXXXXXXX', 'WebServicesKey': 'XXXXXX-XXX-XXX-XXXXXXXX'}")

echo "RK [$return_key]"

#build final command with file and return_key variables
final_command='curl -i -F filedata=@'$file' -H "Authorization: Bearer '$return_key'"'

echo "Final --> [$final_command]"

#eval will run the final command string and output the results
eval $final_command

echo " "
echo " "

Can you please help me how to write this curl script in pl/sql using utl_https/any other oracle functions.

Thanks for your help.

and Connor said...

To upload a file, you need to send the data (typically in chunks) as raw.

Here is some *pseudo* code outlining the general approach

  req   utl_http.req;
  resp  utl_http.resp;
  content blob;
  req_length int;
  offset     int;
  utl_http.set_header(req, 'content-type', 'image/jpeg');
  req_length := dbms_lob.getlength(content);

  if req_length <= 32767   then 

    utl_http.set_header(req, 'Content-Length', req_length);
    utl_http.write_raw(req, content);

  elsif req_length >32767 then
    utl_http.set_header(req, 'Transfer-Encoding', 'Chunked');
    while (offset <= req_length)
    loop, amount, offset, buffer);
      utl_http.write_raw(req, buffer);
      offset := offset + amount;
    end loop;
  end if;

  resp := UTL_HTTP.get_response(req);

There's plenty of blog posts out there on the topic, eg

but can I suggest also taking a look at the APEX_WEB_SERVICE package

which might hide a lot of this complexity for you


  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment


UTL_HTTP raise ORA-29273 and 12547

Joao Vicente Aversa Novaes, May 21, 2020 - 1:22 pm UTC

Hi everybody!

I'm facing ORA-29273 and ORA-12547 using the above solution trying to upload files. The process executes WRITE_RAW some times before crashes with these errors. The file has 13 Mb. My Oracle version is 11.2.

I tried the same with apex_web_service.make_rest_request with the same results.

The difference between my code and this one is the headers I',m setting:

UTL_HTTP.SET_HEADER(vRhttp_req, 'Content-Disposition: form-data; name="file"; filename="' || pVfile || '"' );
UTL_HTTP.SET_HEADER(vRhttp_req, 'Content-Type', 'multipart/form-data; boundary="'gc0p4Jq0M2Yt08jU534c0p'"');
UTL_HTTP.SET_HEADER(vRhttp_req, 'file, '||pVfile); -- pVfile is the file's name
UTL_HTTP.SET_HEADER(vRhttp_req, 'Transfer-Encoding','chunked');

I've tried increase timeout, but it doesn't change anything in the result.

Any ideas?

Connor McDonald
May 22, 2020 - 12:10 am UTC

Timeout can also the property of the recipient not the sender. The recipient might be cancelling your transfer because it is taking too long. However, 13mb seems very small for that.

Try increasing your chunk size.

looks like error in your code

Vadym, September 08, 2021 - 8:49 am UTC

Looks like instead of

while (offset < req_length)

it is correct to write

while (offset <= req_length)

Best regards
Connor McDonald
September 09, 2021 - 5:37 am UTC

Thanks - updated.

But don't forget we said

Here is some *pseudo* code outlining the general approach


utl_http for curl -H -d switches

LKT, March 21, 2023 - 2:15 pm UTC

I have a requirement where I would like to use utl_http instead of curl -H -d switches using POST method.

I have to send data in the below format.
curl https:url \
-H "X-Version: 3" \
-d "api_key=xxx" \
-d "request_token=yyy" \
-d "checksum=zzz"

Can the above example displayed here can take care of my requirement? I have not used utl_http for POSTing. I dont want to call curl using extproc or java or dbms_scheduler or anything else if utl_http can be used.
Connor McDonald
April 06, 2023 - 7:06 am UTC

Multiple "-d" is synonymous with concenation hence

-d "api_key=xxx" \
-d "request_token=yyy" \
-d "checksum=zzz"

is the same as

"api_key=xxx&request_token=yyy&checksum=zzz" thus

UTL_HTTP.WRITE_text(l_request, 'api_key=xxx&request_token=yyy&checksum=zzz');

And -H is nominating the header, thus

-H "X-Version: 3"

maps to

UTL_HTTP.SET_HEADER(l_request, 'Content-Type', 'X-Version: 3');

using form-data need to uplod a csv file in pl/sql

chaithanya, November 20, 2023 - 12:30 pm UTC

Hi i am trying to upload a csv file in rest api using pl sql could you please help how to achieve it.
Connor McDonald
November 28, 2023 - 3:39 am UTC

Isn't that exactly what these answers are? ie, uploading a file (blob) to a service?

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library