Denes Kubicek - ApEx Solutions   
  (email:  deneskubicek@yahoo.de)
PUBLIC_USER

Back

Login
Section I Section II Section III Section IV Section V Section VI Section VII Section VIII
Save IR PDF
Big Table Report
Export PDF
Link
Id
Order Number
Ordered Date
First Ext Sales
Item Sum Selling Price
Resource
31111200212516-MAY-03Lennon, John166OM
31211200212616-MAY-03Gabriel, Peter5600.5OM
31311200212716-MAY-03Williams, Robbie908.85OM
31411200212816-MAY-03Williams, Jason0OM
31511200212916-MAY-03Lennon, John6600OM
1 - 5 Next
The ACL list at apex.oracle.com doesn't allow to get this working.
PDF Report
Document Display
Code
1. All Required Objects:
CREATE SEQUENCE to_blob_seq START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE TABLE to_blob
(
  mbb_id            NUMBER PRIMARY KEY,
  mbb_url           VARCHAR2(4000),
  mbb_file_name     VARCHAR2(4000),
  mbb_file_length   NUMBER,
  mbb_file_type     VARCHAR2(400),
  mbb_file          BLOB,
  mbb_message       VARCHAR2(4000),
  mbb_created_on    DATE,
  mbb_created_by    VARCHAR2(40)
);


CREATE OR REPLACE TRIGGER to_blob_tr
   BEFORE INSERT
   ON to_blob
   FOR EACH ROW
BEGIN
   IF :NEW.mbb_id IS NULL
   THEN
      SELECT to_blob_seq.NEXTVAL
        INTO :NEW.mbb_id
        FROM DUAL;
   END IF;

   :NEW.mbb_created_on := SYSDATE;
   :NEW.mbb_created_by := NVL (v ('APP_USER'), USER);
END to_blob_tr;
/


CREATE OR REPLACE PACKAGE to_blob_pkg
AS
   PROCEDURE load_blob_from_url (p_mbb_url IN VARCHAR2, p_mbb_id OUT NUMBER);

   PROCEDURE display_image (p_mbb_id IN NUMBER);
END to_blob_pkg;
/

CREATE OR REPLACE PACKAGE BODY to_blob_pkg
AS
   PROCEDURE load_blob_from_url (p_mbb_url IN VARCHAR2, p_mbb_id OUT NUMBER)
   AS
      v_http_request      UTL_HTTP.req;
      v_http_response     UTL_HTTP.resp;
      v_raw               RAW (32767);
      v_mbb_file          BLOB;
      v_mbb_file_length   NUMBER;
      v_mbb_message       VARCHAR2 (4000);
      v_mbb_file_type     VARCHAR2 (400)  := 'application/pdf';
      v_mbb_file_name     VARCHAR2 (4000);
   BEGIN
      SELECT to_blob_seq.NEXTVAL
        INTO p_mbb_id
        FROM DUAL;

      v_mbb_file_name := 'pdf_download_' || p_mbb_id || '.pdf';
      DBMS_LOB.createtemporary (v_mbb_file, FALSE);
      v_http_request := UTL_HTTP.begin_request (p_mbb_url);
      v_http_response := UTL_HTTP.get_response (v_http_request);

      BEGIN
         LOOP
            UTL_HTTP.read_raw (v_http_response, v_raw, 32766);
            DBMS_LOB.writeappend (v_mbb_file, UTL_RAW.LENGTH (v_raw), v_raw);
         END LOOP;
      EXCEPTION
         WHEN UTL_HTTP.end_of_body
         THEN
            UTL_HTTP.end_response (v_http_response);
      END;

      v_mbb_file_length := DBMS_LOB.getlength (v_mbb_file);
      v_mbb_message := 'File Download successfull.';

      INSERT INTO to_blob
                  (mbb_id, mbb_url, mbb_file_name, mbb_file_length,
                   mbb_file_type, mbb_file, mbb_message
                  )
           VALUES (p_mbb_id, p_mbb_url, v_mbb_file_name, v_mbb_file_length,
                   v_mbb_file_type, v_mbb_file, v_mbb_message
                  );

      DBMS_LOB.freetemporary (v_mbb_file);
   EXCEPTION
      WHEN OTHERS
      THEN
         UTL_HTTP.end_response (v_http_response);
         DBMS_LOB.freetemporary (v_mbb_file);
         RAISE;
   END load_blob_from_url;

   PROCEDURE display_image (p_mbb_id IN NUMBER)
   AS
      v_mbb_file          BLOB;
      v_mbb_file_length   NUMBER;
      v_mbb_file_type     VARCHAR2 (400)  := 'application/pdf';
      v_mbb_file_name     VARCHAR2 (4000);
   BEGIN
      SELECT mbb_file_name, DBMS_LOB.getlength (mbb_file), mbb_file_type,
             mbb_file
        INTO v_mbb_file_name, v_mbb_file_length, v_mbb_file_type,
             v_mbb_file
        FROM to_blob
       WHERE mbb_id = p_mbb_id;

      OWA_UTIL.mime_header (NVL (v_mbb_file_type, 'application/octet'), FALSE);
      HTP.p ('Content-length: ' || v_mbb_file_length);
      OWA_UTIL.http_header_close;
      WPG_DOCLOAD.download_file (v_mbb_file);
   END display_image;
END to_blob_pkg;
/
2. Executing Code as a Page Process or a Dynamic Action:
DECLARE
   v_mbb_url   VARCHAR2 (4000)
      :=    'http://apex.oracle.com/pls/apex/f?p='
         || :app_id
         || ':'
         || :app_page_id
         || '::PDF::::';
BEGIN
   to_blob_pkg.load_blob_from_url (v_mbb_url, :p283_mbb_id);
END;