External Tables in Oracle

1. Place the ALL_OBJECTS.csv file in D:\CDD
2.  GRANT CREATE ANY DIRECTORY TO SCOTT;
3. Create directory

CREATE OR REPLACE DIRECTORY EXT_TAB_DATA AS 'D:\CDD';
SELECT * FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME='EXT_TAB_DATA';

DROP TABLE ALL_OBJECTS_DUMP;

CREATE TABLE ALL_OBJECTS_DUMP (
    owner         VARCHAR2(30),
    object_name   VARCHAR2(30),
    object_type   VARCHAR2(19),
    status        VARCHAR2(7),
    created       DATE
)
organization EXTERNAL ( TYPE oracle_loader
    DEFAULT DIRECTORY ext_tab_data
ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL
(
   owner       CHAR(30),
    object_name   CHAR(30),
    object_type   CHAR(19),
    status        CHAR(7),
    created       CHAR(10)
    )
  ) location('ALL_OBJECTS.csv') )
    PARALLEL 5
REJECT LIMIT UNLIMITED;

SELECT * FROM ALL_OBJECTS_DUMP;

Download Files in Oracle APEX

CREATE TABLE ATTACHEMENTS
(
   ID             NUMBER,
   ILE_NAME       VARCHAR2 (400),
   FILE_TYPE      VARCHAR2 (400),
   FILE_SIZE      VARCHAR2 (400),
   FILE_CONTENT   BLOB
);

Create a Apex Page with Region - PL/SQL Dynamic Content 

DECLARE
   v_mime         VARCHAR2 (200) := NULL;
   v_lob_length   NUMBER := 0;
   v_file         VARCHAR2 (100) := NULL;
   v_blobref      BLOB;
BEGIN
   SELECT T.FILE_NAME FILE_NAME,
          T.FILE_TYPE FILE_TYPE,
          DBMS_LOB.GETLENGTH (T.FILE_CONTENT) AS FILE_LENGTH,
          T.FILE_CONTENT
     INTO v_file,
          v_mime,
          v_lob_length,
          v_blobref
     FROM  << ATTACHMENTS>> T
    WHERE FILE_ID = 123;

   sys.HTP.init;
   sys.OWA_UTIL.mime_header (NVL (v_mime, 'application/octet'),
                             FALSE,
                             'UTF-8');
   sys.HTP.p ('Content-length: ' || v_lob_length);
   sys.HTP.p (
      'Content-Disposition: attachement; filename="' || v_file || '"');
   sys.OWA_UTIL.http_header_close;
   sys.WPG_DOCLOAD.download_file (v_blobref);
   APEX_APPLICATION.stop_apex_engine;
EXCEPTION
   WHEN OTHERS
   THEN
      sys.HTP.prn ('error: ' || SQLERRM);
      APEX_APPLICATION.stop_apex_engine;
END;

Shared Interactive Report with filters in Oracle APEX

Create a Interactive Report with below query.

SELECT EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  FROM EMP;

Modify alias name of Primary Report to PRIMARY.

1. Primary Report

https://apex.oracle.com/pls/apex/f?p=120487:2:16532414902526:IR_PRIMARY::::

2. Resets, and clears primary report settings : 

https://apex.oracle.com/pls/apex/f?p=120487:2:16532414902526:IR_PRIMARY::RIR,CIR::

3. Resets, and clears primary report settings. It additionally creates a ENAME = 'KING' filter on the primary report:

https://apex.oracle.com/pls/apex/f?p=120487:2:16532414902526:IR_PRIMARY::RIR,CIR:IR_EMPNO:7521
https://apex.oracle.com/pls/apex/f?p=120487:2:16532414902526:IR_PRIMARY::RIR,CIR:IR_ENAME:KING
https://apex.oracle.com/pls/apex/f?p=120487:2:16532414902526:IR_PRIMARY::RIR,CIR:IR_JOB:SALESMAN
https://apex.oracle.com/pls/apex/f?p=120487:2:16532414902526:IR_PRIMARY::RIR,CIR:IR_SAL:5000
https://apex.oracle.com/pls/apex/f?p=120487:2:16532414902526:IR_PRIMARY::RIR,CIR:IR_DEPTNO:30

Valid operators include:

C = Contains
EQ = Equals (this is the default)
GTE = Greater than or equal to
GT = Greater Than
LIKE = SQL Like operator
LT = Less than
LTE = Less than or equal to
N = Null
NC = Not Contains
NEQ = Not Equals
NLIKE = Not Like
NN = Not Null
NIN = Not In (escape the comma separated values with a leading and trailing backslash, \)
IN = In (escape the comma separated values with a leading and trailing backslash, \)
ROWFILTER = Row Text Contains (this searches all columns displayed in the report with type STRING or NUMBER)

https://apex.oracle.com/pls/apex/f?p=120487:2:104896389797835:::RIR,CIR:IRIN_ENAME:\SMITH,KING\