SupportSupport

Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Feature Requests

Thread: Apply NLS database settings to numeric data types

Welcome, Guest: Login / Register; Guest Settings

Reply to this Thread

Search Forum

Back to Thread List

	Replies: 6 - Pages: 1 - Last Post: Feb 26, 2008 7:54 PM Last Post By: Hans Bergsten

makaio

Posts: 7
Registered: 02/03/06

Apply NLS database settings to numeric data types
Posted: Feb 4, 2006 12:07 AM

I am using Oracle 10g v10.1.0.3 with dbvis v4.3.6.

I have found that when I export data to sql, I can specify the data formats, but when I execute the resulting sql, I will get errorrs unless the data formats match my database NLS settings.

For example, I can only explicitly insert a date field if it is of the form 'DD-MMM-YY'. In order to insert using a different format, I have to either use 'alter session set NLS_DATE_FORMAT' or use the to_date function. For example:

alter session set NLS_DATE_FORMAT='YYYY-DDD"T"HH24:MI:SS'
insert into foo values('2002-063T23:02:02')

or

insert into foo values(to_date('2002-063T23:02:02','YYYY-DDD"T"HH24:MI:SS'))

So, it would be nice if the export data to sql feature could output sql using the correct to_TYPE function with the format specified in the settings.

makaio

Posts: 7
Registered: 02/03/06

Re: Apply NLS database settings to numeric data types
Posted: Feb 4, 2006 12:50 AM

in response to: makaio

I did find out how to do what I want with the to_TYPE functions, but it is still a bit cumbersome because there is no way to save the functional formatting.

I found by reading the instructions that you can reformat the values on the export grid data page:

http://www.minq.se/products/dbvis/doc/main/doc/ug/exportPrint/exportPrint.html#mozTocId339765

So from my previous example, I could do:

to_date('$$value$$','YYYY-DDD"T"HH24:MI:SS')

I still think it would be great if there were a feature added to do this automatically. I do realize that the type formatting used for dbvis does not directly correspond to what can be used with Oracle (i.e. Oracle is not case sensitive, hence minutes in dbvis are 'mm' and 'MI' in Oracle). So translations would have to be performed differently for different databases.

At the very least it would be great to be able to save this as a setting, i.e. a default value string for a given type.

By the way, I think this is a great program!

Roger Bjarevall

Posts: 4,566
Registered: 12/17/04

Re: Apply NLS database settings to numeric data types
Posted: Feb 6, 2006 2:39 PM

in response to: makaio

Hi,

Great you found the workaround.

We have on the list to make this more transparent without needing to manually add conversation functions, etc.

Thanks!

Regards

Roger

Gilles Philippart

Posts: 13
Registered: 07/03/06

Re: Apply NLS database settings to numeric data types
Posted: Feb 11, 2008 4:47 PM

in response to: Roger Bjarevall

Hello Roger,

Is there any update about this topic ?
I have the same formatting issues here, i export some table to SQL, and the generated SQL can't be executed properly due to Timestamp and Date formatting issues.

Gilles

Hans Bergsten

Posts: 1,256
Registered: 10/11/06

Re: Apply NLS database settings to numeric data types
Posted: Feb 22, 2008 11:10 PM

in response to: Gilles Philippart

Hi Gilles,

Sorry about the late response. We are looking for improvements in this area for the next feature release (i.e., 6.1). For now, please use the work-around described earlier in this thread.

Best regards,
Hans

Gilles Philippart

Posts: 13
Registered: 07/03/06

Re: Apply NLS database settings to numeric data types
Posted: Feb 26, 2008 1:57 PM

in response to: Hans Bergsten

Thanks for the answer.
What is the ETA for 6.1 ?

Hans Bergsten

Posts: 1,256
Registered: 10/11/06

Re: Apply NLS database settings to numeric data types
Posted: Feb 26, 2008 7:54 PM

in response to: Gilles Philippart

Hi Gilles,

It is scheduled for late Q1 or early Q2, but as always, plans may change.

Best regards,
Hans

Pages: 1

Back to Thread List