script.sql

-- The following script export some ORACLE tables into SQL insert statments 
-- It use MIGRATION_COLUMNS_SETTINGS, MIGRATION_TABELS_LIST table to change the columns names

BEGIN 
  DBMS_OUTPUT.PUT_LINE('STARTING');
END; 

/
-- Check if MIGRATION_COLUMNS_SETTINGS table exits or create it
DECLARE
nCount NUMBER;
v_sql LONG;

BEGIN
--DBMS_OUTPUT.PUT_LINE('Check MIGRATION_COLUMNS_SETTINGS table exists?');
SELECT count(*) into nCount FROM dba_tables where table_name = 'MIGRATION_COLUMNS_SETTINGS';
IF(nCount <= 0)
THEN
--  DBMS_OUTPUT.PUT_LINE('No, MIGRATION_COLUMNS_SETTINGS table not exists, Creating new one...');
  v_sql:='
  create table MIGRATION_COLUMNS_SETTINGS
  (
  ID NUMBER(3),
  TABLE_NAME VARCHAR2(30) NOT NULL,
  OLD_COLUMN_NAME VARCHAR2(30) NOT NULL,
  NEW_COLUMN_NAME VARCHAR2(30) NOT NULL
  )';
  execute immediate v_sql;
--ELSE
--DBMS_OUTPUT.PUT_LINE('Yes MIGRATION_COLUMNS_SETTINGS table  exists.');
END IF;

END;



--declare 
--  fHandle  UTL_FILE.FILE_TYPE;
--begin
--  fHandle := UTL_FILE.FOPEN('my_directory', 'test_file', 'w');
--
--  UTL_FILE.PUT(fHandle, 'This is the first line');
--  UTL_FILE.PUT(fHandle, 'This is the second line');
--  UTL_FILE.PUT_LINE(fHandle, 'This is the third line');
--
--  UTL_FILE.FCLOSE(fHandle);
--EXCEPTION
--  WHEN OTHERS THEN
--    DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
--    RAISE;
--end;


/
-- Check if MIGRATION_TABELS_LIST table exits or create it
DECLARE
nCount NUMBER;
v_sql LONG;

BEGIN
--DBMS_OUTPUT.PUT_LINE('Check MIGRATION_TABELS_LIST table exists?');
SELECT count(*) into nCount FROM dba_tables where table_name = 'MIGRATION_TABELS_LIST';
IF(nCount <= 0)
THEN
--  DBMS_OUTPUT.PUT_LINE('No, MIGRATION_TABELS_LIST table not exists, Creating new one...');
  v_sql:='
  create table MIGRATION_TABELS_LIST
  (
  ID NUMBER(3),
  TABLE_NAME VARCHAR2(30) NOT NULL
  )';
  execute immediate v_sql;
--ELSE
--DBMS_OUTPUT.PUT_LINE('Yes MIGRATION_TABELS_LIST table  exists.');
END IF;

END;


/


-- Create method to get new columns names
-- GET_COLUMN_NAME is function to get new column names from MIGRATION_COLUMNS_SETTINGS table
CREATE OR REPLACE FUNCTION  GET_COLUMN_NAME ( table_name  VARCHAR2, column_name  VARCHAR2   )
 RETURN READ_ONLY.MIGRATION_COLUMNS_SETTINGS.NEW_COLUMN_NAME%TYPE
  IS
    NEW_NAME READ_ONLY.MIGRATION_COLUMNS_SETTINGS.NEW_COLUMN_NAME%TYPE;
  BEGIN 
--   DBMS_OUTPUT.PUT_LINE('START SELECTING table_name:' || table_name || ' column_name:' || column_name );
    SELECT NEW_COLUMN_NAME
      INTO NEW_NAME
      FROM READ_ONLY.MIGRATION_COLUMNS_SETTINGS
      WHERE TABLE_NAME = table_name AND OLD_COLUMN_NAME = column_name;
      
      RETURN NEW_NAME;
      
      EXCEPTION
        WHEN OTHERS  THEN
          NEW_NAME := column_name;
--          DBMS_OUTPUT.PUT_LINE('Error SELECTING ' || 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
--          DBMS_OUTPUT.PUT_LINE('END SELECTING column_name:' ||  column_name || ' NEW_NAME:' || NEW_NAME );
          NEW_NAME := column_name;
          RETURN NEW_NAME;
   
END GET_COLUMN_NAME;
 
/


-- create path for migration

create or replace directory temp_dir as 'C:\temp';
/
grant read, write on directory temp_dir to PUBLIC
/
 
 
 -- Start migration
DECLARE
  cur SYS_REFCURSOR;
  curid NUMBER;
  desctab DBMS_SQL.desc_tab;
  colcnt NUMBER;
  namevar VARCHAR2(4000);
  numvar NUMBER;
  datevar DATE;
  total_row_fetched number:=0;
  file_name varchar(50):='';
  out_columns varchar2(10000);
  out_values varchar2(10000);
  fHandle  UTL_FILE.FILE_TYPE;
  batch_file_number NUMBER := 1;
  
BEGIN

  --  fHandle := UTL_FILE.FOPEN('EXP_CSV', 'out.sql', 'w');
  -- write to file with name 
  --  fHandle := UTL_FILE.FOPEN('EXP_CSV', 'out'|| TO_CHAR(sysdate, 'yyyy-mm-dd hh:mm' )||'-part-'|| batch_file_number ||'.sql', 'w');
  -- get list of tabels to log 
  FOR rec IN (SELECT TABLE_NAME 
                FROM MIGRATION_TABELS_LIST 
               ORDER BY TABLE_NAME)
  LOOP
    OPEN cur FOR 'SELECT * FROM '||rec.TABLE_NAME||'  WHERE rownum <= 5000';
    DBMS_OUTPUT.put_line('Working on table:' ||rec.TABLE_NAME);
    
    
    
    
    
    
    curid := DBMS_SQL.to_cursor_number(cur);
    DBMS_SQL.describe_columns(curid, colcnt, desctab);

    out_columns := 'INSERT INTO '||rec.TABLE_NAME||'(';
    



    
    FOR indx IN 1 .. colcnt LOOP
    
      -- get table name and column name, check if they are exist in the MIGRATION_COLUMNS_SETTINGS
      -- if it exists get the new name, else use current name
      out_columns := out_columns||READ_ONLY.GET_COLUMN_NAME( rec.TABLE_NAME , desctab(indx).col_name )||',';
      IF desctab (indx).col_type = 2 
      THEN
        DBMS_SQL.define_column (curid, indx, numvar); 
      ELSIF desctab (indx).col_type = 12
      THEN
        DBMS_SQL.define_column (curid, indx, datevar); 
      ELSE
        DBMS_SQL.define_column (curid, indx, namevar, 4000); 
      END IF;
    END LOOP;

    out_columns := rtrim(out_columns,',')||') VALUES (';

    WHILE DBMS_SQL.fetch_rows (curid) > 0 
    LOOP
      out_values := '';
      FOR indx IN 1 .. colcnt 
      LOOP
        IF (desctab (indx).col_type = 1) 
        THEN
          DBMS_SQL.COLUMN_VALUE (curid, indx, namevar);
          out_values := out_values||''''||namevar||''',';
        ELSIF (desctab (indx).col_type = 2)
        THEN
          DBMS_SQL.COLUMN_VALUE (curid, indx, numvar);
          out_values := out_values||numvar||',';
        ELSIF (desctab (indx).col_type = 12)
        THEN
          DBMS_SQL.COLUMN_VALUE (curid, indx, datevar);
          out_values := out_values|| ''''||
            to_char(datevar,'YYYY-MM-DD HH24:MI:SS')||
             ''',';
        END IF;
      END LOOP; 
     DBMS_OUTPUT.put_line(out_columns||rtrim(out_values,',')||');');
     -- create new file for each 2M record
    -- write to file with name 
    if mod ( total_row_fetched, 2000 ) = 0 then
      /* close old file, open new */
      -- if file open close it
      if SYS.UTL_FILE.IS_OPEN(fHandle)then
       UTL_FILE.FCLOSE(fHandle);
      end if;
       
       
       file_name := 'out-'|| TO_CHAR(sysdate, 'yyyy-mm-dd-hh:mm' )||'-part-'|| batch_file_number ||'.sql';
       batch_file_number := batch_file_number + 1;
       fHandle := UTL_FILE.FOPEN('EXP_CSV',file_name , 'w');
       DBMS_OUTPUT.put_line('file: ' ||file_name );
       DBMS_OUTPUT.put_line('total_row_fetched: ' || total_row_fetched);
       
    end if;
    total_row_fetched := total_row_fetched + 1;
    -- write to file
    UTL_FILE.PUT_LINE(fHandle, out_columns||rtrim(out_values,',')||');');
 

    END LOOP;

    DBMS_SQL.close_cursor (curid);

  END LOOP;
  UTL_FILE.FCLOSE(fHandle);
  DBMS_OUTPUT.put_line('FINISHED');
  EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
    RAISE;
END;
/