0

I have exported all rows from all table in a given schema using SQL developer . A sql file is generated which has insert statements . Can I use this to import data in target database EDB(postgres). The target Db has same tables as in Source DB . Will it work ?

I have Dbeaver also .If I export data from oracle DB in Dbeaver and create insert script . Will this be better than using sql script generated from sql developer . Please suggest some working option . Thank You .

Prashant Singh
  • 23
  • 6

2 Answers2

0

That depends on how complete the export is.

A backup of a dbms in general writes out a series of SQL statements. These are (amongst others) INSERT statements to put data in tables.

The most import part that you might be missing is the TABLE CREATE command. If you do not have that there is no table to insert into (or a table with a non matching column set).

I would advice that you take a look at a backup of a postgres db created by the pg_dump. That should clarify things.

de2Zotjes
  • 101
  • 3
0

Well, the first thing is that SQL Developer will export the rows as insert statements using ANSI SQL, so normally it should work. However, you must take in consideration the data type conversion, because the export as insert is intended to use in another Oracle database.

Here you can see the possible conversions on data types between Oracle and PostgreSQL.

Oracle type                  Possible PostgreSQL types
CHAR                         char, varchar, text
NCHAR                        char, varchar, text
VARCHAR                      char, varchar, text
VARCHAR2                     char, varchar, text, json
NVARCHAR2                    char, varchar, text
CLOB                         char, varchar, text, json
LONG                         char, varchar, text
RAW                          uuid, bytea
BLOB                         bytea
BFILE                        bytea (read-only)
LONG RAW                     bytea
NUMBER                       numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0        numeric, float4, float8, int2, int4, int8,boolean, char, varchar, text
FLOAT                        numeric, float4, float8, char, varchar, text
BINARY_FLOAT                 numeric, float4, float8, char, varchar, text
BINARY_DOUBLE                numeric, float4, float8, char, varchar, text
DATE                         date, timestamp, timestamptz, char, varchar, text
TIMESTAMP                    date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE     date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH               date, timestamp, timestamptz, char, varchar, text
LOCAL TIME ZONE 
INTERVAL YEAR TO MONTH       interval, char, varchar, text
INTERVAL DAY TO SECOND       interval, char, varchar, text

so the answer always relies in how complex is your data model.

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • RE: output of SQL Dev vs. that of DBeaver ... I'd seriously doubt there is any difference at all. It would be simple enough to compare the two yourself . . . – EdStevens Jul 06 '20 at 11:19