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

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

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

(4 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.

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

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.

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.

November 28, 2023 - 3:39 am UTC

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

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

UTL_HTTP raise ORA-29273 and 12547

looks like error in your code

utl_http for curl -H -d switches

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

More to Explore

PL/SQL demos

PL/SQL docs

UTL_HTTP POST with filename

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

UTL_HTTP raise ORA-29273 and 12547

looks like error in your code

utl_http for curl -H -d switches

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

More to Explore

PL/SQL demos

PL/SQL docs