Convert matrix data in Excel /csv or text file into Oracle table in Raw format

We want to convert following matrix data into to raw format .

Copy this data in excel and save it in .csv format named mydata.csv.

Time	Latitude	Temperature Cover (%)Longitude(60-80)
		60	60.1	60.2	60.3	60.4	60.5	60.6	60.7	60.8	60.9	61	61.1	61.2
00Z01AUG2015	23	23.8965	44.0527	48.6152	48.6152	62.5586	91.3066	97.3848	100	100	100	100	100	100
00Z01AUG2015	23.1	20.1816	20.5078	28.5332	28.5332	59.543	69.7168	87.7266	100	100	100	100	100	100
00Z01AUG2015	23.2	0	19.7207	25.5566	25.5566	39.8457	69.7168	91.502	91.502	91.502	99.9395	100	100	100
01Z01AUG2015	23.3	0	19.7207	25.5566	25.5566	39.8457	69.7168	91.502	91.502	91.502	99.9395	100	100	100
00Z01AUG2015	23.4	16.8008	19.7207	20.7754	20.7754	36.1016	53.9766	57.2246	63.9258	63.9258	75.6562	62.7617	80.5586	92.6621
00Z01AUG2015	24.9	9.33203	5.06641	10.8008	10.8008	11.75	12.709	13.0117	13.4883	13.4883	13.5254	20.5293	34.25	44.2461
00Z01AUG2015	25	13.293	6.91602	13.9023	13.9023	13.4043	56.5078	56.0312	14.5918	14.5918	65.084	74.0371	91.3945	76.4277
00Z01AUG2015	25.1	22.9375	14.3984	14.7734	14.7734	16.0293	56.9629	57.4316	57.4316	57.4316	70.4473	85.1523	85.8809	76.4277
00Z01AUG2015	25.2	62.002	57.2988	22.3496	22.3496	14.4746	57.9766	57.8535	35.9023	35.9023	70.4473	73.127	80.502	85.9414
00Z01AUG2015	25.3	62.002	57.2988	22.3496	22.3496	14.4746	57.9766	57.8535	35.9023	35.9023	70.4473	73.127	80.502	85.9414
00Z01AUG2015	25.4	61.2637	59.8574	9.69336	9.69336	14.4746	55.9375	58.998	20.9961	20.9961	30.2812	81.0508	83.0996	73.3652
00Z01AUG2015	25.5	61.2637	65.0254	65.0254	65.0254	82.6895	72.084	59.8789	34.9941	34.9941	39.6641	80.5527	75.7246	69.2969
00Z01AUG2015	25.6	62.041	86.4336	88.4277	88.4277	74.0996	60.6797	60.6797	74.2754	74.2754	94.7402	94.7402	75.2793	86.2031
00Z01AUG2015	25.7	83.4902	92.8145	86.5801	86.5801	82.9453	82.9453	83.2461	72.5547	72.5547	72.5547	88.8457	75.2793	74.9043
00Z01AUG2015	25.8	83.4902	92.8145	86.5801	86.5801	82.9453	82.9453	83.2461	72.5547	72.5547	72.5547	88.8457	75.2793	74.9043
00Z01AUG2015	25.9	59.7734	85.4707	92.1621	92.1621	83.2598	88.8789	83.2461	92.7461	92.7461	86.4648	68.2441	71.6484	92.416
00Z01AUG2015	26	92.0527	92.0527	83.252	83.252	90.3535	89.0508	93.7207	84.6875	84.6875	92.6348	56.6328	87.4727	72.209
00Z01AUG2015	26.1	58.6934	58.7188	66.1367	66.1367	62.8516	16.3477	60.5254	92.6309	92.6309	92.6309	63.4512	82.0742	75.3535
00Z01AUG2015	26.2	17.1348	17.0234	15.1914	15.1914	15.1914	15.0527	16.3008	16.5703	16.5703	13.375	63.4512	9.21094	92.0859
00Z01AUG2015	26.3	17.1348	17.0234	15.1914	15.1914	15.1914	15.0527	16.3008	16.5703	16.5703	13.375	63.4512	9.21094	92.0859

Now We are ready to convert the above data into the raw format using following script.

Execute following script on Oracle sql prompt or any Oracle client tool used to execute Oracle queries.

Following script uses External table to convert .csv files into oracle tables then
uses oracle commands to convert matrix data into Oracle raw table format.

Click here to see how to create and used external table to import data into Oracle.

set serveroutput on ;
declare
cursor tabcolumns is
select column_name, ' '||data_type||' ('||DATA_PRECISION||','||data_scale||')' data_types
from user_tab_columns
where table_name = 'LODADED_DATA'
and COLUMN_NAME  not in ('DATESTIME','LATITUDE')
order by column_id;

TOTAL_COLUMNS NUMBER:=0;
cursor matrix_tabcolumns is
select column_name
from user_tab_columns
where table_name = 'MATRIX_DATA'
and COLUMN_NAME  not in ('DATESTIME','LATITUDE')
AND COLUMN_ID<=TOTAL_COLUMNS
order by column_id;

columms varchar2(20000):='';
column_val varchar2(1000):='';
create_table varchar2(30000):='';
val_count number:=0;
TABLE_MISSING EXCEPTION;
PRAGMA EXCEPTION_INIT(TABLE_MISSING,-00942);
begin
    begin
        execute immediate 'ALTER TABLE EXT_TABLE LOCATION(''mydata.csv'')';
        execute immediate 'DROP TABLE LODADED_DATA';
        execute immediate 'CREATE TABLE LODADED_DATA  AS SELECT * FROM EXT_TABLE';
        exception WHEN TABLE_MISSING then
       execute immediate 'CREATE TABLE LODADED_DATA  AS SELECT * FROM EXT_TABLE';
    end;

  for rec_cols in tabcolumns  loop
  execute immediate 'select count('||rec_cols.column_name||') from LODADED_DATA ' into val_count;
    if val_count <> 0 then
       columms:=columms||','||rec_cols.column_name;
       execute immediate 'select '||'replace(''A_''||'||rec_cols.column_name||',''.'',''_'') '||' from LODADED_DATA where rownum=1' into column_val;
       create_table:=create_table||','||column_val||rec_cols.data_types;
       TOTAL_COLUMNS:=TOTAL_COLUMNS+1;
       -- dbms_output.put_line (rec_cols.column_name);
    end if;
  end loop;
  begin
    dbms_output.put_line ('Table created to load matrix data created');
    execute immediate 'drop table MATRIX_DATA';
    execute immediate 'create table MATRIX_DATA (DATESTIME varchar2(100),LATITUDE number(10,5)'||create_table|| ' )';
    exception WHEN TABLE_MISSING then
    execute immediate 'create table MATRIX_DATA (DATESTIME varchar2(100),LATITUDE number(10,5)'||create_table|| ' )';
  end;
  -- dbms_output.put_line (columms);
   execute immediate 'insert into MATRIX_DATA select DATESTIME,LATITUDE'||columms||' from LODADED_DATA where rowid <> (select rowid from LODADED_DATA where rownum=1)';
  commit;
 columms:='';
  for matrix_rec in matrix_tabcolumns loop
    columms:=columms||','||matrix_rec.column_name;
  end loop;
  execute immediate 'drop  table raw_data';
  execute immediate 'create table RAW_DATA as select DATESTIME,LATITUDE,REPLACE(REPLACE(LONGITUDE,''A_'',''''),''_'',''.'') LONGITUDE ,quantity from MATRIX_DATA  UNPIVOT (quantity FOR  LONGITUDE IN ('||substr(columms,2)||'))';
end;


-- select * from RAW_DATA;
-- drop table RAW_DATA;
--where longitude=60;

Restore Cold backup on different host.

In this article we are required to duplicate database using cold backup.

We have two servers named oracle1 and oracle2;

Source database path: D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB
Destination Database Path: C:\oracle\product\10.2.0\oradata\PDADB

We want to duplicate /clone the database  PDADB on oracle1 machine to Oracle2 machine.

For this first Install Oracle on the Oracle2 machine and create a database with same name ie PDADB.

Now  one Oracle2 machine stop oracle services and rename folder that contains database files;

In my case it is C:\oracle\product\10.2.0\oradata\PDADB.

So I renamed PDADB to PDADB_FRESH_BACKUP.

Now copy database folder from folder D:\ORACLE\PRODUCT\10.2.0\ORADATA on your Oracle1 machine to Oracle2 oradata  C:\oracle\product\10.2.0\oradata\ folder.

Start database in mount state.

sqlplus / as sysdba

startup mount;

Execute following command to create contolfile backup in text format, This will help us to verify files and their path on the Oracle1 server that needs to be changed on oracle2 server.


--- backup controlfile to trace;

alter database backup controlfile to trace as 'c:\PDAdb_controlfile.txt';


If your Oracle1 server database path and Oraocle2 server database path are different then execute following command to change path accordingly.

--------rename datafile location --------

alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\REDO01.LOG' to 'C:\oracle\product\10.2.0\oradata\PDADB\REDO01.LOG';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\REDO02.LOG' to 'C:\oracle\product\10.2.0\oradata\PDADB\REDO02.LOG';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\REDO03.LOG' to 'C:\oracle\product\10.2.0\oradata\PDADB\REDO03.LOG';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\SYSTEM01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\SYSTEM01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\UNDOTBS01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\UNDOTBS01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\SYSAUX01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\SYSAUX01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\USERS01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\USERS01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\EXAMPLE01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\EXAMPLE01.DBF';
alter database rename file'E:\ORACLEDATA\PDADB01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\e_oradata\PDADB01.DBF';
alter database rename file'E:\ORACLEDATA\PDAAOSDB01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\e_oradata\PDAAOSDB01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\PDADB.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\PDADB.DBF';


Execute following command to view the changed path
--- backup controlfile to trace;

alter database backup controlfile to trace as 'c:\PDAdb_controlfile.txt';


If all paths are ok now we can start database using following command;

Alter database open;

Duplicate process is complete.