Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, SB.

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

Answered by: Connor McDonald - Last updated: May 22, 2020 - 12:10 am UTC

Category: PL/SQL - Version: Oracle Database 12c

Viewed 1000+ times

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

and you rated our response

  (1 rating)

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

Reviews

UTL_HTTP raise ORA-29273 and 12547

May 21, 2020 - 1:22 pm UTC

Reviewer: Joao Vicente Aversa Novaes from Sao Paulo, Brazil

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

Followup  

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.

More to Explore

DBMS_LOB

More on PL/SQL routine DBMS_LOB here