Copy All the data from a DataBase using Apex

Sometimes we don't have access to the Data Base it self and we can't make an export of the Data Base, in my case, I can only access through Apex, so I had to find a way to copy all the data to other Data Base, and probably, as it happened to me, all what you have found is the long way, download Table by table and then upload it, well, that could be enough if you have less than 10 tables, but if you have more, it would take many time, many click.. and bajh, We're to lazy to do it :P and I don't if you've heard this were you live, but "The Laziness is the mother of all inventions" hehehe..

Unfortunately (And forgive me by lying with 'all' in the tittle) this method will not work with tables that have columns of type Clob, Blob or Long (and maybe others). Maybe this will not be a problem if your data base has not many tables with data of that type, then you can use it for the others and upload one by one the tables with columns of that type. If you have many tables with columns of that type and you don't mind the data in those columns, I'll show you an alternative method that will work only if the column can be null, the link will be at the end of this post, below the Video (which I Hope you watch it) :)

Now that you've read what this will do, you can decide if this is useful for you, if it is, let's begin :D

---

Well, this method consist in run a procedure that will print a procedure that will print all the insert statements... :o 何　(what?) hehehe I know this might sound confusing, but our target is to print the insert statements of all the Data by selecting the data for each table, as I want to make it general for all the Data Bases, first we have to check the name and the columns of all the tables, so here is the code that does it.

declare
insert_columns_value varchar2(32000);
select_columns varchar2(32000);

begin
htp.p('CREATE OR REPLACE PROCEDURE GET_DATA IS');
htp.p('begin');
for tab in (select table_name 
   from user_tables 
   where TABLE_NAME NOT IN (select table_name
         from user_tab_columns
         where DATA_TYPE in ('CLOB','BLOB','LONG')
         GROUP BY table_name)
   order by 1)LOOP
 insert_columns_value :='';
 select_columns :='';
 for col in (select (CASE WHEN COLUMN_ID > 1 THEN '||'',''|| ' end )||'R.'||COLUMN_NAME "INSERT_COLUMN_VALUE",
      (CASE WHEN COLUMN_ID > 1 THEN ', ' end )||COLUMN_NAME "INSERT_COLUMN_REFERENCE",
      
      (CASE WHEN COLUMN_ID > 1 THEN ', ' end )||
      (CASE WHEN DATA_TYPE IN 'NUMBER' THEN 'nvl2('||COLUMN_NAME||',replace('||COLUMN_NAME||','','',''.''),'||chr(39)||'null'||chr(39)||')  "'||COLUMN_NAME||'"'
        WHEN DATA_TYPE = 'DATE'  THEN 'nvl2('||COLUMN_NAME||',chr(39)||to_char('||COLUMN_NAME||',''MM/DD/YY'')||chr(39),'||chr(39)||'null'||chr(39)||')  "'||COLUMN_NAME||'"'
        ELSE 'nvl2('||COLUMN_NAME||',CHR(39)||replace('||COLUMN_NAME||',chr(39),chr(39)||chr(39))||CHR(39),'||chr(39)||'null'||chr(39)||')  "'||COLUMN_NAME||'"' END) "SELECT_COLUMN"
    FROM USER_TAB_COLUMNS
    WHERE TABLE_NAME = tab.table_name
    order by COLUMN_ID) LOOP
    
  insert_columns_value := insert_columns_value||col.INSERT_COLUMN_VALUE;
  select_columns := select_columns||col.SELECT_COLUMN;
 END LOOP;
 
 htp.p('htp.p(''INSERT ALL'');');
 htp.p('for r in( select '||select_columns||' from '||tab.table_name||') LOOP');
 htp.p(' htp.p('' INTO '||tab.table_name||' VALUES(''||'||insert_columns_value||'||'')'');');
 htp.p('END LOOP;');
 htp.p('htp.p(''SELECT * FROM DUAL;''||chr(10)||chr(10));'||chr(10)||chr(10));
 
END LOOP;
htp.p('END;');
END;

This will take each table that has not columns of type Blob, Clob or Long.
Why those types? well, as I've said, our target is print the insert statements and for do it, we need to print the data.. and I couldn't find how to print the data of those types... ok I admit it I didn't search it much, The data Base I'll copy doesn't have columns of that type, but I tested this with one that has them and it got me troubles, so I didn't include it :P

Well, for each table after we search the name of the columns and store them in the variables insert_columns_value in the format R.column, this will be used for the insert statement. Those values stored in the select_columns variable will include a format depending the type of data like this

--number
nvl2(NUMBER_COLUMN,replace(NUMBER_COLUMN,',','.'),'null')
-- Date
nvl2(FECHA_LIMITE_ACCION,chr(39)||to_char(FECHA_LIMITE_ACCION,'MM/DD/YY')||chr(39),'null')
--Varchar and char and others
nvl2(CHAR_COLUMN,CHR(39)||replace(CHAR_COLUMN,chr(39),chr(39)||chr(39))||CHR(39),'null')

All the types has a nvl2 which will print the column in the format or null if the column value is null.
Number: will replace the colon by a dot, if we don't do it the numbers with decimal will be printed like 1,5 and the colon will separate the number in two data and we'll get a disagreeable error of too many values.
Date: This will format the data in the format you put there, this must be the same that the one in the target DataBase.
Varchar: This will only put the data between quotes.

The procedure will print something like this

CREATE OR REPLACE PROCEDURE GET_DATA IS
begin
htp.p('INSERT ALL');
for r in( select nvl2(ID_ARTIST,replace(ID_ARTIST,',','.'),'null')  "ID_ARTIST", nvl2(ID_COUNTRY,replace(ID_COUNTRY,',','.'),'null')  "ID_COUNTRY", nvl2(NAME_ARTIST,CHR(39)||replace(NAME_ARTIST,chr(39),chr(39)||chr(39))||CHR(39),'null')  "NAME_ARTIST", nvl2(WEBSITE,CHR(39)||replace(WEBSITE,chr(39),chr(39)||chr(39))||CHR(39),'null')  "WEBSITE" from ARTIST) LOOP
 htp.p(' INTO ARTIST VALUES('||R.ID_ARTIST||','|| R.ID_COUNTRY||','|| R.NAME_ARTIST||','|| R.WEBSITE||')');
END LOOP;
htp.p('SELECT * FROM DUAL;'||chr(10)||chr(10));


htp.p('INSERT ALL');
for r in( select nvl2(ID_COUNTRY,replace(ID_COUNTRY,',','.'),'null')  "ID_COUNTRY", nvl2(NAME_COUNTRY,CHR(39)||replace(NAME_COUNTRY,chr(39),chr(39)||chr(39))||CHR(39),'null')  "NAME_COUNTRY" from COUNTRY) LOOP
 htp.p(' INTO COUNTRY VALUES('||R.ID_COUNTRY||','|| R.NAME_COUNTRY||')');
END LOOP;
htp.p('SELECT * FROM DUAL;'||chr(10)||chr(10));

--Like this for all the tables...

END;

As you can see, it is just a pl/sql Block that will select all the data in the table in the respective format and print the insert statement.
The line 1 is due to a size limit of the Apex console, you can't run a script bigger than 32k or something like that, then with this you create a process that will do it. Yeah, this will create that process but how to create it if it still exceeds the limit? Well, we can run it as a Script file, if you don't know how to do it, watch the next post.

(not available yet! :'()

But this also has a size limit, so if you exceed it, you have to run it in different files.
after you run it, it will create a process called "GET_DATA" now just run it with the next 3 lines

begin
get_data();
end;

And now you have the insert Script similar to this one :D

INSERT ALL
SELECT * FROM DUAL;

INSERT ALL
 INTO ARTIST VALUES(1,16,'Perfume','https://www.perfume-web.jp')
 INTO ARTIST VALUES(2,5,'Nelly Furtado','http://www.nellyfurtado.com')
 INTO ARTIST VALUES(3,2,'The Veronicas','http://www.theveronicas.com')
 INTO ARTIST VALUES(4,28,'Breaking Benjamin','http://www.shallowbay.com/')
 INTO ARTIST VALUES(5,28,'Demi Lovato','http://demilovato.com/')
SELECT * FROM DUAL;

INSERT ALL
 INTO SONG VALUES(1,1,'パーフェクトスター・パーフェクトスタイル',1)
 INTO SONG VALUES(2,1,'リニアモーターガール',2)
 INTO SONG VALUES(3,1,'コンピューターシティ',3)
 INTO SONG VALUES(4,1,'エレクトロ・ワールド(Album Version)',4)
 INTO SONG VALUES(5,1,'引力',5)
 INTO SONG VALUES(6,1,'モノクロームエフェクト',6)
 INTO SONG VALUES(7,1,'ビタミンドロップ',7)
 INTO SONG VALUES(8,1,'スウィートドーナッツ',8)
 INTO SONG VALUES(9,1,'ファンデーション',9)
 INTO SONG VALUES(10,1,'コンピュータードライビング',10)
SELECT * FROM DUAL;

The first INSERT All will be the same for all the tables that doesn't have Data then you have to remove them one by one..but again, we're lazy to do it when there are many of then, well, you can use your favorite text editor to replace the two lines by nothing or a space. And now Celebrate as A~chan does because now you have All (or almost all) the data in a single script.

Although there is something I forget, humm... oh yeah, this!! :P. With this, we're done with the source Data Base, but now we have to upload the data in the other Data Base, I hope you have access directly to it, if you don't, you have to upload the data in the same way you created the procedure "Get_Data".
Before do it, you must be sure that the Data Base doesn't have data in the tables you will upload data, this is to avoid problems with primary Keys. if you have test data for any reason, you can delete it easy with the next post and DO NOT ENABLE CONSTRAINTS YET!.

Delete the data from All tables in Oracle

If you didn't had to delete the date, now you have to disable all the constraints, I explain in this post how to do it.

Disable and Enable All table constraints in Oracle

Now that you have all the constraints disabled you will not have any problem uploading the data, then we can create the upload Script, you could just run the code that threw the procedure Get_Data but with this you will be sure that all the data was uploaded right. just put the insert script in the next block of code

Spool on
Spool C:\logDataLoad\All.txt
variable n number
exec :n := dbms_utility.get_time
ALTER SESSION set NLS_DATE_FORMAT = 'MM/DD/YY';

-- Put the code Here

exec :n := (dbms_utility.get_time - :n)/100
exec dbms_output.put_line('execution time = '||:n||' Seconds'||chr(10)||chr(10)||chr(10))

PROMP Check the Log Files in C:\logDataLoad, if there is any error execute COMMIT
spool off;

The lines 3,4,9 and 10 are just by fun :P and check how many time it takes.
The Lines 1,2, and 13 are to create a log file in the directory of line 2, that way, you can check that there is no one error before commit the changes.
And the line 5 is to alter the Date format to make it math with the one you exported the data.

Now save this file and run it... How? well, suppose that you save the file named data.sql in C:\ then open a console (win + r > cmd > enter) and go where you have your file connect and run

cd C:\
sqlplus user
[pasword]
@data.sql

And now you can see how all the data is loaded, Finally enable the constraints again (before do it upload the data from tables with clob, blob or Long columns) and if it doesn't present any problem, you are Done, now you can celebrate like Kashiyuka!!! :D now Please, if this was really helpful to you, I'd thank you if you coment, or share this and watch the below video and share it... :) I hope you like it.





oh yeah, I said that I'd show you a way to upload tables with Clob, Blob or Long columns, well, it is too late and I'll go to sleep soon, but if you really need this now, I'll explain this to you, I hope you get it :)

You have to modify only the first Pl/sql, in the first for change the NOT IN for a IN. create a new variable called "reference_columns" and store in it the exact name of the column, now in the second for include in the where the condition DATA_TYPE not in ('CLOB','BLOB','LONG'). now edit the line 34 to print between () the value of the variable you created after the name of the table, the idea is that at the end you get something like

INSERT ALL
  INTO table (column1,column3) VALUES(value1,value3)
  --...other inserts
SELECT * FROM DUAL;

The column 2 would be the column with the value we couldn't print. all the rest is the same.
I hope you can do it :) さようなら
And don't forget to watch and share the video and this post ;)