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.