REM=========================================================================== REM Copywright 2002, John Love and Chet West REM============================================================================ REM DESCRIPTION - Generate loader scripts (control files) from data REM dictionary defintions for a user. REM REM NOTE - This is currently only designed to load data files in a delimited "|" format. REM REM============================================================================ REM MODIFICATION HISTORY REM Person Date Comments REM --------- ---------- ------------------------------------------- REM John Love 01/01/1999 Initial Creation; REM Chet West 05/01/1999 REM============================================================================ REM Enter the 3 file names; Loader script name, data file name, output file name REM and the date of generation, application name and the application version for the REM loader script to be generated. ACCEPT v_loadscript_name PROMPT 'ENTER THE NAME OF THE LOADER SCRIPT TO BE GENERATED (EXAMPLE MYSQLLDR.CTL) ' ACCEPT v_inputfile_name PROMPT 'ENTER THE FLAT FILE SOURCE FILE NAME (EXAMPLE MYDATA.DAT) ' ACCEPT v_loading_table_name PROMPT 'ENTER THE TABLE TO BE LOADED (WHERE DATA IS TO BE LOADED) NAME (EXAMPLE EMPLOYEE) ' ACCEPT v_schema_name PROMPT 'ENTER THE SCHEMA THAT OWNS THE TABLE (EXAMPLE SCOTT) ' ACCEPT v_current_date PROMPT 'ENTER THE DATE OF GENERATION TO APPEAR IN CODE (FORMAT DD-MON-YY) ' ACCEPT v_application_name PROMPT 'ENTER THE APPLICATION NAME (EXAMPLE MYAPP) ' ACCEPT v_application_version PROMPT 'ENTER THE APPLICATION VERSION (EXAMPLE 1) ' REM ACCEPT v_output_directory_path PROMPT 'ENTER THE OUTPUT DIRECTORY PATH (CaSe SeNsItIvE) ' SET LINESIZE 80; SET HEADING OFF; SET WRAP ON; SET FEEDBACK OFF; REM SET FEEDBACK ON; SET ECHO OFF; REM SET ECHO ON; SET VERIFY OFF; REM SET VERIFY ON; SET UND OFF; SET SERVEROUTPUT ON SIZE 40000; REM SET TERMOUT ON; REM SET PAUSE ON; REM /* Spool to the user defined .CTL file */ SPOOL &&v_loadscript_name; DECLARE v_total_columns NUMBER(3) := 0; -- Set default to 0 for total columns v_Loop_count NUMBER(3) := 0; -- Times through loop logic v_continue_processing VARCHAR2(1); -- Yes/No variable max_cols NUMBER(3); -- Total columns -- CURSOR column_count is SELECT count(col.column_name) v_total_columns FROM all_tab_columns col WHERE UPPER(col.table_name) = UPPER('&&v_loading_table_name') AND col.owner = UPPER('&&v_schema_name'); -- CURSOR columns_to_generate_csr is SELECT RPAD(substr(col.column_name,1,30),30,' ') col_name, DECODE(SUBSTR(col.data_type,1,8),'CHAR', 'CHAR ','NUMBER', 'CHAR ','DATE','DATE ', 'VARCHAR2','CHAR ') field_type FROM all_tab_columns col WHERE UPPER(col.table_name) = UPPER('&&v_loading_table_name') AND col.owner = UPPER('&&v_schema_name'); -- BEGIN FOR all_columns_in_table_count IN column_count LOOP -- 1 row of total column count IF all_columns_in_table_count.v_total_columns > 0 THEN v_continue_processing := 'Y'; -- Continue processing columns ELSE all_columns_in_table_count.v_total_columns := 0; DBMS_OUTPUT.PUT_LINE('Column count returned zero columns for table '||'&&v_loading_table_name'); v_continue_processing := 'N'; -- Stop processing, no columns counted for specific table END IF; -- End IF v_total_columns > 0 max_cols := v_total_columns + 21; -- 21 is the total number of header and trailer records IF v_continue_processing = 'Y' THEN DBMS_OUTPUT.PUT_LINE ('rem '||'&&v_loadscript_name'); -- Name of generated CTL script DBMS_OUTPUT.PUT_LINE ('rem ****************************************************************** '); DBMS_OUTPUT.PUT_LINE ('rem Author-> Loader Script Generator, (CTL_GEN.SQL)'); DBMS_OUTPUT.PUT_LINE ('rem Date-> '||TO_CHAR(TO_DATE('&&v_current_date'), 'dd-mon-yy')); -- Date script generated DBMS_OUTPUT.PUT_LINE (' '); -- space for appearance DBMS_OUTPUT.PUT_LINE ('rem PURPOSE-> '); DBMS_OUTPUT.PUT_LINE ('rem Incoming file name-> '||'&&v_inputfile_name'); -- Flat file name DBMS_OUTPUT.PUT_LINE ('rem Output file name-> '||'&&v_loading_table_name'); -- Output file to data will be loaded DBMS_OUTPUT.PUT_LINE (' '); -- blank line for appearance DBMS_OUTPUT.PUT_LINE ('rem ****************************************************************** '); -- for appearance DBMS_OUTPUT.PUT_LINE (' '); -- blank line for apperance DBMS_OUTPUT.PUT_LINE (' '||'OPTIONS (SKIP=1) '); -- Number of lines to skip in dat (input flatfile) file DBMS_OUTPUT.PUT_LINE (' '||' LOAD DATA '); DBMS_OUTPUT.PUT_LINE (' '||' INFILE $DATA_PATH/'||'&&v_inputfile_name'); DBMS_OUTPUT.PUT_LINE (' '||' INSERT ' ); -- Inserts into file does not append to existing DBMS_OUTPUT.PUT_LINE (' '||' INTO TABLE '||'&&v_loading_table_name'); -- DBMS_OUTPUT.PUT_LINE (' '||' FIELDS TERMINATED BY "|" OPTIONALLY TERMINTATED BY " " '); DBMS_OUTPUT.PUT_LINE (' ('); -- Begin of data values BEGIN -- Looping through the columns to generate output FOR columns_to_generate_recs IN columns_to_generate_csr LOOP -- All columns in table to generate v_Loop_count := v_loop_count + 1; -- Count times through loop IF v_loop_count < all_columns_in_table_count.v_total_columns THEN DBMS_OUTPUT.PUT_LINE (' '||columns_to_generate_recs.col_name||' '||RTRIM(columns_to_generate_recs.field_type)||','); -- all lines ELSE DBMS_OUTPUT.PUT_LINE (' '||columns_to_generate_recs.col_name||' '||RTRIM(columns_to_generate_recs.field_type)); -- Last line END IF; END LOOP; -- generate output END; -- Looping through columns of specified table DBMS_OUTPUT.PUT_LINE(' )' ); -- End of values closing paranthesis END IF; END LOOP; -- Column count -- EXCEPTION -- WHEN OTHERS THEN -- DBMS_OUTPUT.PUT_LINE ('ERROR WHILE PROCESSING. DON’T KNOW WHAT BUT A SNAFU HAS OCCURED!!!'); END; / REM set termout on; REM /* Remove all values from variables when complete. This will prevent carryover to next execution*/ undefine v_loadscript_name; undefine v_stagetable_name; undefine v_inputfile_name; undefine v_current_date; undefine v_max_col_id; undefine v_total_columns; undefine v_application_name; undefine v_application_version; REM undefine v_ouput_directory_path; CLEAR BUFFER; SPOOL OFF;