Upload documents to database using EPG

I did see interesting post how upload documents to Oracle database using Oracle HTTP server.

Here is example how you can do same with Embed PL/SQL Gateway.

First create table to store documents:

CREATE TABLE documents(
    name        VARCHAR2(256) UNIQUE NOT NULL,
    mime_type   VARCHAR2(128),
    doc_size    NUMBER,
    dad_charset VARCHAR2(128),
    last_updated DATE,
    content_type VARCHAR2(128),
    blob_content BLOB
  )
/

Download and compile package.

Create public synonyms and grant privileges to DAD user:

/* public synonyms and grants */
CREATE PUBLIC SYNONYM doc_api FOR doc_api;
GRANT EXECUTE ON doc_api TO ANONYMOUS;
GRANT ALL ON documents TO ANONYMOUS;
CREATE PUBLIC SYNONYM documents FOR documents;

Create new DAD. You need run commands as SYS:

/* create new DAD */
BEGIN
dbms_epg.create_dad('DOCAPI','/pls/*');
dbms_epg.set_dad_attribute('DOCAPI','database-username','ANONYMOUS');
dbms_epg.set_dad_attribute('DOCAPI','default-page','doc_api.upload');
dbms_epg.set_dad_attribute('DOCAPI','document-table-name','documents');
dbms_epg.set_dad_attribute('DOCAPI','document-path','docs');
dbms_epg.set_dad_attribute('DOCAPI','nls-language','american_america.al32utf8');
dbms_epg.set_dad_attribute('DOCAPI','document-procedure','doc_api.download');
dbms_epg.set_dad_attribute('DOCAPI','request-validation-function','doc_api.authorize');
END;
/

Go to URL:

http://<host>:<port>/pls/doc_api.upload

and upload files directly to DOCUMENTS table.