Start a new topic

Apply NLS database settings to numeric data types

[This topic is migrated from our old forums. The original author name has been removed]

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.

[This reply is migrated from our old forums. The original author name has been removed]

Re: Apply NLS database settings to numeric data types
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!
[This reply is migrated from our old forums.]

Re: Apply NLS database settings to numeric data types
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: Apply NLS database settings to numeric data types
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
[This reply is migrated from our old forums.]

Re: Apply NLS database settings to numeric data types
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: Apply NLS database settings to numeric data types
Thanks for the answer.
What is the ETA for 6.1 ?
[This reply is migrated from our old forums.]

Re: Apply NLS database settings to numeric data types
Hi Gilles, It is scheduled for late Q1 or early Q2, but as always, plans may change. Best regards, Hans