Control Files Migration.

Control Files for Migration this 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_NameFrom 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)ColTextFrom User_Tab_ColumnsWhere Table_Name = CvTableNameOrder By Column_Id;