Inline External table in Oracle

It is good to be back. Hope everyone is safe. 
Let us pray for everyone whose lives have been impacted by #covid19.

Oracle has been enhancing "external table" features in each release . 

Here is one of my post describing the support for wildcard characters in the file location. 
 http://mfzahirdba.blogspot.com/2015/07/external-table-enhancements-in-oracle.html

There were few external table enhancements in Oracle 18c. 
One of them is that you do not need to create table first and then reference that external table in your query.

In Oracle 18c and later , I can define the external table definition inline and access the files without defining the external table.

Let us use the same example files in http://mfzahirdba.blogspot.com/2015/07/external-table-enhancements-in-oracle.html

Here is an example . 

Sign on and create directory and grant the access to regular user ( in our case , zahir).

SQL> create directory EX_TAB_DIR as 'c:\datadumps\';

Directory created.

SQL> grant read , write , execute on directory EX_TAB_DIR to zahir;

Grant succeeded.

Sign on to the database with the user account.

SQL> show user
USER is "ZAHIR"


SQL> ho dir c:\datadumps
 Volume in drive C has no label.
 Volume Serial Number is E247-41EF

 Directory of c:\datadumps

04/21/2020  05:09 PM   
          .
04/21/2020  05:09 PM   
          ..
04/21/2020  04:52 PM                28 emp1.txt
04/21/2020  04:52 PM                25 emp2.txt
04/21/2020  04:53 PM                50 emp500.txt
           


SQL> ho type c:\datadumps\emp1.txt type c:\datadumps\emp2.txt type c:\datadumps\emp500.txt

c:\datadumps\emp1.txt


101,zahir,F
102,mohamed,R
The system cannot find the file specified.
Error occurred while processing: type.

c:\datadumps\emp2.txt


201,Hameed,A
252,Abdul,RThe system cannot find the file specified.
Error occurred while processing: type.

c:\datadumps\emp500.txt


511,Balasubramaniam,A
577,Yesudas,A
501,Janaki,A



SQL>  col emp_id format a5
SQL> col emp_name  format a20
SQL> col emp_type format a5
SQL>  SELECT
  2      emp_id,
  3      emp_name,
  4      emp_type
  5  FROM
  6      external
  7  ( ( emp_id   VARCHAR2(1000),
  8             emp_name VARCHAR2(1000),
  9             emp_type VARCHAR2(1000)
 10             )
 11      TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR"
 12      ACCESS PARAMETERS
 13      ( debug = 3
 14      RECORDS DELIMITED BY NEWLINE
 15          CHARACTERSET we8mswin1252
 16          BADFILE ex_tab_dir : 'emp_ext.bad_xt'
 17          LOGFILE 'emp_ext.log_xt'
 18      FIELDS TERMINATED BY "," LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
 19  )
 20  LOCATION ( 'emp*.txt' ) reject limit unlimited )
 21   ;

EMP_I EMP_NAME             EMP_T
----- -------------------- -----
101   zahir                F
102   mohamed              R
201   Hameed               A
252   Abdul                R
511   Balasubramaniam      A
577   Yesudas              A
501   Janaki               A

7 rows selected.


In the program listing , the external table is defined inline between the lines 7 and 20.With this method, it is easier to load the data from flat files into the database without defining the table first. 

Comments Welcome. 

Schema only user accounts in Oracle

Prior to Oracle 18c ,  there is no difference between user and schema . It has been used interchangeably . 

SQL Server has introduced the concept of "SCHEMA" only accounts quite a while ago. 
Now , Oracle 18c ( aka 12c R2) has this new feature . 

We can create a schema only account with the clause " no authentication".  Once this schema gets created , the objects can be accessed by either proxy users , other users . 

Prior to this , typically , I create a user with no "create session" privilege ; store only the persistent information in that user . Then there will be another user that does the data processing and the logic  is exposed as an API.   

With 18c and " no authentication" clause , it is easier from management/administration  perspective.

Example shown below 


SQL> create user sales  no authentication ;

User created.

SQL> alter user sales quota unlimited on users;

User altered.


SQL> alter user sales default tablespace users;

User altered.

SQL> create table sales.employees ( empname char(50)) ;

Table created.

SQL> create user webappuser identified by Pa55w0rd ;

User created.

SQL> grant select , insert , update , delete on  sales.employees to webappuser;

Grant succeeded.

SQL> grant create session to webappuser ;

Grant succeeded.

SQL> alter user webappuser default tablespace users;

User altered.


SQL> alter user webappuser quota unlimited on users;

User altered.


Now , login as webappuser .

SQL> insert into sales.employees values ('Zahir');

1 row created.

SQL> commit;

Commit complete.

SQL> show user
USER is "WEBAPPUSER"
SQL>