After I came back from Vijayawada meeting my friends, yesterday, I started surfing aimlessly - truly aimless. But it was good time that I got to know about some interesting feature in Oracle. I never thought of this feature. Of course there was a reason for not thiniking about this feature, as Oracle already had a similar* feature. Last week my client had sent a request tp delete few records based on columnA, columnB combination. It must be very simple right?- just a simple delete statement. But, they sent around 200 such combination values. How do you do?. Do you execute Delete statement for 200 times?. I think it is time wasting process. Definitely it would have been naive implementation had I used Delete statement for 200 times :). As a simple method, I created a temporary table and loaded that table with the data that my client had provided. That's all, now both Main data and the Reference data are in database. Now write a single Delete stmt to delete the records from the Main table using the data in the Reference table. But, only question is how to load the external data into a Database table? - Answer is Oracle's sql loader tool. For loading the data from flat files into a table, we need to setup some environment variables(oracle_home, tns_admin...), need to create Control file. Control File(demo.ctl): LOAD DATA INFILE * INTO TABLE FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY (, , ) SQL Loader call... sqlldr userid=uwclass/uwclass control=c:\demo.ctl log=c:\demo.log I think we can all agree that this sql loader approach is better than deleting the records manually for each combination, delete from del_frm_table where cola, colb in (select cola, colb from del_using_table); All this was 1 week ago. Today I have come to know about another similar approach which is even better than SQL Loader approach. This is using External Tables. Sql loader loads the data from flat files into database tables. Also, it has relatively complex implementation (uses control files...). But, in External table method, data from flat files would not be loaded into Database, rather directly used from files. In effect, you could treat a text file as if it were a table, and thus join its contents with that of other tables, both ‘real’ and external. As such, they were intended to help those database users who routinely perform extraction, transformation and loading (ETL) operations: data cleansing exercises, in short. External Tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. This external table definition can be thought of as a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database.You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and indexes cannot be created on external tables.(in 10g even DML operation is also possible) 1.Create a Directory Object where the flat files will reside SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES';Directory created. 2.Create metadata for the external table SQL> CREATE TABLE emp_ext ( empcode NUMBER(4), empname VARCHAR2(25), deptname VARCHAR2(25), hiredate date ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tables ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION ('emp_ext1.dat','emp_ext2.dat') ) REJECT LIMIT UNLIMITED; Table created. "The ORACLE_LOADER is an access driver for loading data from the external files into the tables." Another driver is ORACLE_DATAPUMP. ORACLE_LOADER is mainly for loading the data from flat files. Using ORACLE_DATAPUMP driver, table data can be dumped into a flat file(in driver proprietary binary format) and the same file can be used to load the data using same datapump driver. SQL> create table ext_e 2 organization external 3 (type oracle_datapump 4 default directory ext_dir 5 location ('tbl_e.etl')) 6 as 7 select * from e; Table created. whenever "as select..." clause is used with oracle_datapump, it will create binary data file(unload) from the contents of "select clause". Without "as select..." it will load the data from the binary file.

Finally, one interesting feature in SQL Loader... Use CONCATENATE when you want SQL*Loader to always combine the same number of physical records to form one logical record. In the following example, integer specifies the number of physical records to combine. CONCATENATE integer

Use CONTINUEIF if the number of physical records to be continued varies. The parameter CONTINUEIF is followed by a condition that is evaluated for each physical record, as it is read. CONTINUEIF THIS NEXT LAST condition Assume that you have physical records 14 bytes long and that a period represents a space: %%aaaaaaaa.... %%bbbbbbbb...... cccccccc.... %%dddddddddd.. %%eeeeeeeeee.... ffffffffff.. CONTINUEIF THIS (1:2) = '%%' Therefore, the logical records are assembled as follows: aaaaaaaa....bbbbbbbb....cccccccc.... dddddddddd..eeeeeeeeee..ffffffffff..