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')
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.
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.