Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, SB.

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

Last updated: September 09, 2021 - 5:37 am UTC

Version: Oracle Database 12c

Viewed 10K+ times! This question is

You Asked

Tom,

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.
PL/SQL :

DECLARE
 req   utl_http.req;
 resp  utl_http.resp;
 l_value VARCHAR2(1024);
 l_url_import    VARCHAR2(500) := 'https://api.teamdynamix.com/TDWebApi/api/people';
 l_url VARCHAR2(100) := 'https://api.teamdynamix.com/TDWebApi/api/auth/loginadmin';
 l_content VARCHAR2(1000) := 'C:\SB\TeamDynamix\Files\xavier_to_teamdynamics.xlsx' ;
BEGIN
 
  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, 'Authorization', 'Bearer BEID: XXXXXX-XXXX-XXX-XXXXXXXX, WebServicesKey: XXXXXX-XXX-XXX-XXXXXXXX' );  
 --- UTL_HTTP.SET_HEADER(req, 'Content-Length', 100000000);--LENGTH(l_Content));
  UTL_HTTP.WRITE_text(req,  l_content);
  resp := utl_http.get_response(req); 
  dbms_output.put_line('Response');
  LOOP
    utl_http.read_line(resp, l_value, TRUE);
    dbms_output.put_line('Value......'||l_value);
  END LOOP;
  utl_http.end_response(resp);
EXCEPTION
  WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
  WHEN OTHERS THEN
    dbms_output.put_line('Error');
END;
/


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 "https://services.xavier.edu/TDWebApi/api/auth/loginadmin" -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' https://services.xavier.edu/tdwebapi/api/people/import -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.
Sailu

and we 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

declare
  req   utl_http.req;
  resp  utl_http.resp;
  content blob;
  req_length int;
  offset     int;
begin
  
  dbms_lob.loadblobfromfile(content,bfilename('xavier_to_teamdynamix.xlsx',...);
  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
      dbms_lob.read(content, 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

https://apexplained.wordpress.com/2016/03/21/utl_http-and-a-multipartform-data-request-body/

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

http://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_web_service.htm#AEAPI537

which might hide a lot of this complexity for you

Rating

  (2 ratings)

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

Comments

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?

Thanks.
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

:-)

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