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
  ) AS
    v_part part;
  BEGIN
   //p_name: 'file'; p_filename:文件名;v_newline: 0D0A; p_content_typ:application/pdf
    v_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
  ) AS
    v_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-length
    v_length := v_length + v_length_end + v_length_bo + v_length_nl;
    FOR i IN p_parts.first .. p_parts.last LOOP
      v_length := v_length + length(p_parts(i).ds_header);

      IF (p_parts(i).ds_blob IS NOT NULL) THEN
        v_length := v_length + dbms_lob.getlength(p_parts(i).ds_blob);
      ELSE
        v_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) THEN
        v_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 parts
    FOR i IN p_parts.first .. p_parts.last LOOP
      utl_http.write_raw(p_req,
                         utl_raw.cast_to_raw(p_parts(i).ds_header));

      IF (p_parts(i).ds_blob IS NOT NULL) THEN

        dbms_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 LOOP
          utl_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);

      ELSE
        utl_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) THEN
        utl_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代码一样。