\n
![\"\"](\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/11\/Untitled-1-1.png\")
\nFiles line info.dat, info2.dat, dataDeal.txt are ordinary files that can be read. On the other hand, we have tables in Oracle that show like:
\n
![\"\"](\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/11\/Untitled-11-1-1.png\")
\nThe Oracle system has the concept of external files. The code to create these is:<\/p>\n
External tables allow someone to store data in a file while these files are shown as tables within Oracle. So, we have a two sided object – on one hand it can be shown as a file that is human readable, on the other hand it can be seen as a table. Let us see at the list of files on a hard disk somewhere:
\n
\nFiles line info.dat, info2.dat, dataDeal.txt are ordinary files that can be read. On the other hand, we have tables in Oracle that show like:
\n
\nThe Oracle system has the concept of external files. The code to create these is:<\/p>\n
\ndrop table dataDeal;\nCREATE TABLE dataDeal\n(\nTECHNICAL_KEY NUMBER(10),\nID_ERSTE_ENT NUMBER(10),\nEDT_DEAL DATE,\nSDT_DEAL DATE,\nUNQ_ID_SRC_SYS VARCHAR2(50),\nLV_G_PRD_L3 VARCHAR2(25),\nLVW_FIN_AMT_TP VARCHAR2(25),\nLV_CCY VARCHAR2(25),\nAMT NUMBER(18)\n)\nORGANIZATION EXTERNAL\n(TYPE ORACLE_LOADER\n DEFAULT DIRECTORY external\nACCESS PARAMETERS\n(RECORDS DELIMITED BY NEWLINE\nSKIP 1\nBADFILE 'bad_%a_%p.bad' \nLOGFILE 'log_%a_%p.log' \n fields terminated by '\\t'\n MISSING FIELD VALUES ARE NULL\n ( TECHNICAL_KEY\n, ID_ERSTE_ENT\n,EDT_DEAL date'dd.mm.yy' \n,SDT_DEAL date'dd.mm.yy'\n,UNQ_ID_SRC_SYS\n,LV_G_PRD_L3 \n,LVW_FIN_AMT_TP \n,LV_CCY \n,AMT\n)\n)\n LOCATION (external:'dataDeal - Copy.txt')\n);\n\nselect * from dataDeal;\nselect count(*) from dataDeal;\n\n\nCREATE TABLE emp_load\n (employee_number CHAR(5),\n employee_dob CHAR(20),\n employee_last_name CHAR(20),\n employee_first_name CHAR(15),\n employee_middle_name CHAR(15),\n employee_hire_date DATE)\n ORGANIZATION EXTERNAL\n (TYPE ORACLE_LOADER\n DEFAULT DIRECTORY EXTERNAL\n ACCESS PARAMETERS\n (RECORDS DELIMITED BY NEWLINE\n FIELDS (employee_number CHAR(2),\n employee_dob CHAR(20),\n employee_last_name CHAR(18),\n employee_first_name CHAR(11),\n employee_middle_name CHAR(11),\n employee_hire_date CHAR(10) date_format DATE mask \"mm\/dd\/yyyy\"\n )\n )\n LOCATION ('info.dat')\n );\n \n drop table emp_load2;\n CREATE TABLE emp_load2\n (employee_number CHAR(5),\n employee_dob CHAR(20),\n employee_last_name CHAR(20),\n employee_first_name CHAR(15),\n employee_middle_name CHAR(15),\n employee_hire_date DATE)\n ORGANIZATION EXTERNAL\n (TYPE ORACLE_LOADER\n DEFAULT DIRECTORY EXTERNAL\n ACCESS PARAMETERS\n (RECORDS DELIMITED BY NEWLINE\n fields terminated by ';'\n (employee_number ,\n employee_dob ,\n employee_last_name ,\n employee_first_name ,\n employee_middle_name ,\n employee_hire_date DATE'mm\/dd\/yyyy'\n )\n )\n LOCATION ('info2.dat')\n );\n \n \n select employee_middle_name,to_char(employee_hire_date,'YYYY-MON-DD') from emp_load2;\n \n \n drop table emp_load3;\n CREATE TABLE emp_load3\n (employee_number CHAR(5),\n employee_dob CHAR(20),\n employee_last_name CHAR(20),\n employee_first_name CHAR(15),\n employee_middle_name CHAR(15),\n employee_hire_date DATE)\n ORGANIZATION EXTERNAL\n (TYPE ORACLE_LOADER\n DEFAULT DIRECTORY EXTERNAL\n ACCESS PARAMETERS\n (RECORDS DELIMITED BY NEWLINE\n fields terminated by '\\t'\n (employee_number ,\n employee_dob ,\n employee_last_name ,\n employee_first_name ,\n employee_middle_name ,\n employee_hire_date DATE'mm\/dd\/yyyy'\n )\n )\n LOCATION ('info3.dat')\n );\n \n \n select employee_middle_name,to_char(employee_hire_date,'YYYY-MON-DD') from emp_load3;\n<\/pre>\n![\"\"](\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/11\/Untitled-12-1-1.png\")