Control Files for Migration

his article discuss the procedure to generate the auto control files for given schema and store the control files in text format.

This process saves lot of time and effort. I assume the oracle directory is created before running this process.

This procedure reads the schema tables and generate generic control file based on data types. For each table process generates one control file and stores in default oracle directory path.

Create Or Replace Procedure CreateControlFiles(PDateFormat VarChar2)
Is

Cursor C1 Is
Select 'LOAD DATA'|| chr (10) ||
'INFILE ''' || lower (table_name) || '.dat''' || chr (10) ||
'INTO TABLE '|| table_name || chr (10)||
'FIELDS TERMINATED BY ''|'''||chr (10)||
'TRAILING NULLCOLS' || chr (10) || '(' CtrText,
Table_Name
From User_Tables;


Cursor C2(CvTableName VarChar2) Is
Select Decode(Column_Id, 1, '', ' , ')||
Rpad(Column_Name, 33, ' ') ||
Decode (Data_Type,
'VARCHAR2', 'CHAR NULLIF ('||Column_Name||'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('||Column_Name||'=BLANKS)',
'NUMBER', Decode (Data_Precision, 0,
'INTEGER EXTERNAL NULLIF ('||Column_Name||
'=BLANKS)', Decode (Data_Scale, 0,
'INTEGER EXTERNAL NULLIF ('||
Column_Name||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('||
Column_Name||'=BLANKS)')),
'DATE', 'DATE "'||PDateFormat||'" NULLIF ('||Column_Name||'=BLANKS)', Null)
ColText
From User_Tab_Columns
Where Table_Name = CvTableName
Order By Column_Id;


LvFile Utl_File.File_Type;

Begin

For C1Rec In C1
Loop
LvFile := Utl_File.FOpen('EXCEL_DATA',C1Rec.Table_Name||'.Txt','W');

Utl_File.Put_Line(LvFile,C1Rec.CtrText);

For C2Rec In C2(C1Rec.Table_Name)
Loop

Utl_File.Put_Line(LvFile,C2Rec.ColText);

End Loop;

Utl_File.Put_Line(LvFile,')');
Utl_File.FClose(LvFile);
End Loop;
End;
/