Dynamic Import using sqlldr

Here i provide a form that can be used to import dynamically data from a specified file to selected table and columns

- this version run in the environment that the database and application server is on the same server , other environment needs extra code adding to the provided code.

- you can add new feature or functionality to the code provided , or enhance the existing code according to your requirements

-- You choose the table that you want to upload data into from the provided list of the tables in your schema.

-- you will choose the columns form the table column list populated

-- write the file name with extension that you want to upload data from

-- choose if truncte the table or append the data to the existing data

------ the explaination of the mechanism

-- the main idea is to dynamically create the control file , and the batch file that will change the running oracle home and run the Sql loader command using the control file created

an audit piece of code is issued at the end of the program unit to audit the importing process.

-- the user will only choose the table and column and write the file name with extenstion .

--- Code Sample -----

PROCEDURE Import_process IS
v_load_directory varchar2(30);
V_FILE_TYPE UTL_FILE.FILE_TYPE;
v_directory_path varchar2(200) ;
v_file_name varchar2(50);
v_ext varchar2(3);
V_COL_LIST VARCHAR2(500);
V_SEP_POS NUMBER(3);
V_ORA_HOME VARCHAR2(500);
v_bat_file_name varchar2(500);
v_control_file varchar2(50);

BEGIN

if :table_name is null then
message('You Must Choose Table For Importing Process'); message('You Must Choose Table For Importing Process');
raise form_trigger_failure ;
end if ;

if :file_name is null then
message('You Must Specify File Name You Want to Upload'); message('You Must Specify File Name You Want to Upload');
raise form_trigger_failure ;
end if ;

if Get_List_Element_Count('SELECTED_COLS')=0 then
message('No Column Selected For Importing Process');message('No Column Selected For Importing Process');
raise form_trigger_failure ;
end if ;

--- start collecting information and parameters for the process
v_load_directory:=sm_check_pkg.check_application_setting('LOAD_DATA_DIR');
v_directory_path :=sm_get_pkg.get_directory_path(v_load_directory);
v_file_name:=substr(:file_name,1,instr(:file_name,'.')-1) ;
v_ext:=substr (:file_name,-3) ;
V_ORA_HOME:=SM_CHECK_PKG.CHECK_APPLICATION_SETTING('DB_ORA_HOME');


V_COL_LIST:='';
For i IN 1 .. Get_List_Element_Count('SELECTED_COLS') Loop
V_COL_LIST:=V_COL_LIST','Get_List_Element_Value( 'SELECTED_COLS', i );
End loop ;
---V_SEP_POS:=INSTR(V_COL_LIST,',',-1);

V_COL_LIST:=SUBSTR(V_COL_LIST,2);

-- CREATE TEH CONTROL FILE
v_control_file:=dbms_random.STRING('b',8)'.''ctl';
v_file_type:=utl_file.fopen(v_load_directory,v_control_file,'W');
UTL_FILE.putf(V_FILE_TYPE,'LOAD DATA'); -- first line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'INFILE ''''' v_directory_path'\':file_name''''); -- second line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'BADFILE ''''' v_directory_path'\'v_file_name'.''bad'''''); -- third line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'DISCARDFILE ''''' v_directory_path'\'v_file_name'.''dsc'''''); -- fourth line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,:PREDATA_02);
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'INTO TABLE ''"TMS"''.''"':TABLE_NAME'"');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'FIELDS TERMINATED BY '''''';''''');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'('V_COL_LIST')');
UTL_FILE.fclose(V_FILE_TYPE);
synchronize;

---- CREATE THE BATCH FILE
v_bat_file_name:=dbms_random.STRING('b',8)'.''bat';
v_file_type:=utl_file.fopen(v_load_directory,v_bat_file_name,'W');
UTL_FILE.putf(V_FILE_TYPE,'@echo on ');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'set oracle_home='v_ora_home);
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'cd\');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,v_ora_home'\bin\sqlldr.exe ' 'userid=tms/tms@hdb control='v_directory_path'\'v_control_file);
UTL_FILE.fclose(V_FILE_TYPE);
synchronize;

host('start 'v_directory_path'\'v_bat_file_name);

if Form_success then
paragma_pkg.p_import_audit(info_pkg.get_current_user_id,v_control_file,v_bat_file_name);
end if ;
END;