CODEWITHSUNDEEP
sqldatabaseoracle-sqldeveloper
Adley Slb
Adley Slb

Reputation: 47

SQL Developer : Export of the query result don't keep the data_type

I am working on a project where I can't connect directly to the database. I wrote a query that get all the data that I need so I can export them. The result of this SQL query have the great data_type but When I export the result, I choose the insert format and at this point all the data_type are switching to string.

Example of the exported data where the number are in the string format wherease in the original database and in the respons of the query, the type is respected :

Insert into MY_TABLE (POSTCODE, NUMBER, ADRESS, CODE, CITY, VALUE) values ('90000', '303', 'HultonLane', '2845', 'BOLTON', '3');

Do you know if there is any way to export the data with the same data_type from the original DB?

Thank for your help, if you need more information I will provide them.

Upvotes: 0

Views: 599

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

That behavior (or bug?) exists in e.g. SQL Developer version 19.4, but is fixed in 20.2 so - if you're on a lower version, I suggest you upgrade.

Here's an example, based on Scott's DEPT table where DEPTNO column's datatype is NUMBER. Result - when this:

desc dept;
select /*insert*/ * from dept;

is ran as a script - is:

Name   Null?    Type         
------ -------- ------------ 
DEPTNO NOT NULL NUMBER       
DNAME           VARCHAR2(20) 
LOC             VARCHAR2(20) 
REM INSERTING into DEPT
SET DEFINE OFF;
Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

As you can see, all DEPTNO values are numbers. In 19.4, you'd have e.g.

Insert into DEPT (DEPTNO,DNAME,LOC) values ('10','ACCOUNTING','NEW YORK');

('10', a string).

You don't have to do anything about it; the result is the same regardless you run such a select statement, or right-click table name and "Export" data as insert statements.

BTW, our versions match: Mine is 20.2.0.175, build 175.1842.

Upvotes: 1

Related Questions