Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Feature Requests

Thread: Apply NLS database settings to numeric data types


Permlink 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
   thread.click_reply Reply
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    global.in_response_to.tooltip in response to: makaio
   thread.click_reply Reply
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: 5,066
Registered: 12/17/04
Re: Apply NLS database settings to numeric data types
Posted: Feb 6, 2006 2:39 PM    global.in_response_to.tooltip in response to: makaio
   thread.click_reply Reply
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    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
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,551
Registered: 10/11/06
Re: Apply NLS database settings to numeric data types
Posted: Feb 22, 2008 11:10 PM    global.in_response_to.tooltip in response to: Gilles Philippart
   thread.click_reply Reply
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    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
Thanks for the answer.
What is the ETA for 6.1 ?
Hans Bergsten


Posts: 1,551
Registered: 10/11/06
Re: Apply NLS database settings to numeric data types
Posted: Feb 26, 2008 7:54 PM    global.in_response_to.tooltip in response to: Gilles Philippart
   thread.click_reply Reply
Hi Gilles,

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

Best regards,
Hans

Point your RSS reader here for a feed of the latest messages in all forums