PL/SQL调用REST API上传附件

概述

本文主要介绍如何用PL/SQL程序调用REST API上传附件。初始代码源自github, 经过修改测试。

代码示例

  TYPE part IS RECORD(ds_header VARCHAR2(2048),ds_value  VARCHAR2(1024),ds_blob   BFILE);TYPE parts IS TABLE OF part;
v_boundary CONSTANT VARCHAR2(60) := '---------------------------30837156019033';
v_end      CONSTANT VARCHAR2(10) := '--';//准备http请求里包含的内容,封装在p_parts参数中PROCEDURE add_file(p_parts        IN OUT parts,p_name         IN VARCHAR2,p_filename     VARCHAR2,p_content_type VARCHAR2,p_blob         BFILE) ASv_part part;BEGIN//p_name: 'file'; p_filename:文件名;v_newline: 0D0A; p_content_typ:application/pdfv_part.ds_header := 'Content-Disposition: form-data; name="' || p_name ||'"; filename="' || p_filename || '"' || v_newline ||'Content-Type: ' || p_content_type || v_newline || v_newline;v_part.ds_blob := p_blob;p_parts.extend();p_parts(p_parts.last) := v_part;END add_file;
//把parts里的内容写入http请求PROCEDURE send(p_req   IN OUT utl_http.req,p_parts IN OUT parts) ASv_length     NUMBER := 0;v_length_bo  NUMBER := length(v_boundary);v_length_nl  NUMBER := length(v_newline);v_length_end NUMBER := length(v_end);v_step  PLS_INTEGER := 12000;v_count PLS_INTEGER := 0;BEGIN-- calculate the content-lengthv_length := v_length + v_length_end + v_length_bo + v_length_nl;FOR i IN p_parts.first .. p_parts.last LOOPv_length := v_length + length(p_parts(i).ds_header);IF (p_parts(i).ds_blob IS NOT NULL) THENv_length := v_length + dbms_lob.getlength(p_parts(i).ds_blob);ELSEv_length := v_length + length(p_parts(i).ds_value);END IF;v_length := v_length + v_length_nl;v_length := v_length + v_length_end + v_length_bo;IF (i != p_parts.last) THENv_length := v_length + v_length_nl;END IF;END LOOP;v_length := v_length + v_length_end + v_length_nl;utl_http.set_header(p_req,'Content-Type','multipart/form-data; boundary=' || v_boundary);utl_http.set_header(p_req,'Content-Length',v_length);//用write_raw才不会有乱码，试了很多次才确定write_raw可以utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_end));utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_boundary));utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_newline));-- write partsFOR i IN p_parts.first .. p_parts.last LOOPutl_http.write_raw(p_req,utl_raw.cast_to_raw(p_parts(i).ds_header));IF (p_parts(i).ds_blob IS NOT NULL) THENdbms_lob.open(p_parts(i).ds_blob,dbms_lob.lob_readonly);v_count := trunc((dbms_lob.getlength(p_parts(i).ds_blob) - 1) / v_step);FOR j IN 0 .. v_count LOOPutl_http.write_raw(p_req,dbms_lob.substr(p_parts(i).ds_blob,v_step,j * v_step + 1));END LOOP;dbms_lob.close(p_parts(i).ds_blob);ELSEutl_http.write_raw(p_req,utl_raw.cast_to_raw(p_parts(i).ds_value));END IF;utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_newline));utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_end));utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_boundary));IF (i != p_parts.last) THENutl_http.write_raw(p_req,utl_raw.cast_to_raw(v_newline));END IF;END LOOP;utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_end));utl_http.write_raw(p_req,utl_raw.cast_to_raw(v_newline));END send;                     

说明

调用add_file和send两个存储过程可以准备好附件上传http请求，其余部分和调用普通REST API代码一样。