When looking at a table in squirrel, the "Source" tab shows you a create table statement to reproduce that table. However there is a subtlety in Oracle that is missing. When you declare this:
varchar2(100)
that can hold 100 bytes by default, but it can't hold 100 characters, if those characters are UTF-8 or UTF-16 and include extended characters... characters beyond ascii. The solution is to declare it like this...
varchar2(100 char)
How to know if a field is 100 char, or 100 byte? Well I notice in squirrel table view there is a column "CHAR_OCTET_LENGTH" that is 4x as big for a varchar2 char, than a varchar2 byte, so however you are getting that data, I presume the solution would be that when you are generating the CREATE TABLE statement, look at that field, and if it's 4x as big as the COLUMN_SIZE data, then it should be declared as varchar2 char.
Another subtlety is that people can change the global Oracle default from byte to char with this statement:
alter system set nls_length_semantics = 'CHAR' scope = both;
That means you can't really assume byte is the default. That means that in the generated CREATE TABLE tab, you really should have..
varchar2(100 byte)
if it is a byte length field rather than just assuming byte is the default.
As the table source tab is part of the Oracle Plugin I think it would be appropriate to use an Oracle specific way to read table DDLs. I found this:
SELECT dbms_metadata.get_ddl('TABLE','OracleTableSourceTest') from dual
which for example returns this:
Do you think it would be alright to use this instead generating it from the JDBC DatabaseMetaData?
Hmmm.... To me, that looks a little bit too specific and too proprietary. A lot of options there that I have no idea about. Maybe you need more opinions and input before going down this route. Even though the byte vs char thing is a bit different in Oracle, it is at least something that you come across in other databases in one way or another. All the options above... maybe not so much.
I am setting the NLS_LENGTH_SEMANTICS of my session before I create/copy tables, but with "alter session" (I don't have DBA rights in the production databases, but I can create tables in one schema).
So I am happy with the "simple" create table scripts that don't bring forward the whole technical storage details (I stick to the defaults, which might be different in different databases) and do NOT specify the BYTE/CHAR in the create command.
Would any change here also have implications for the table copying?
We have databases that are still codepage based, not UTF-8, so they are mostly BYTE; if the create command would specify the BYTE, the copying of some tables would fail and we would have to prepare the structure first and then copy the tables in a 2nd wave; not a big problem, just an inconvenience. (The data flow is only from the old database to the new UTF-8 database.)
Normally we do not mix semantics in the same table, which is technically possible, but happens here only by mistake. But some might want to bring forward the mixed settings.
So maybe the "Create Table Script" has to come in 2 versions? One as usual and one where the NLS_LENGTH_SEMANTICS from the source table is included? ("Create Table Script NLS"?)
But you cannot rely on dbms_metadata.get_ddl to get the data, because according to https://stackoverflow.com/questions/50941485/any-alternative-way-of-dbms-metadata-get-ddl
"Nonprivileged users can see the metadata of only their own objects."
I just tried in a production database and indeed it throws ORA-31603 for all those tables I don't own.
But I can check with
SELECT COLUMN_NAME,CHAR_USED FROM ALL_TAB_COLS WHERE TABLE_NAME='TEST_TABLE'
the BYTE/CHAR settings for any accessible table, even those I don't own.
I can certainly see the argument for two create table functions... one ANSI sql, and one with database specific SQL. Of course, ANSI sql doesn't have varchar2, nor even varchar, so that might not make people happy either. I'm not sure why you say specifying "byte" would make it fail because your databases are "codepage based", but then I'm not an Oracle expert.
I am not an Oracle expert either :-)
Just an experience: When we copy a table from an "ANSI" database ( where 1 Char=1Byte ) into a UTF-8 database (English characters still take 1 Byte, but local characters take up multiple bytes), the local language characters "blow up", so they might no longer fit, if the table was created with BYTE.
That depends on the data, so if a table has the columns with varchar2(100), but no string is longer than 10 chars, the copying still works. But if there is a string with 60 local chars (which take up 3 bytes in the UTF-8 database), we cannot insert, if the column was created with varchar2(100 BYTE).
Yes, I see what you mean. And this is exactly the reason why I was advocating for this change. If you naively use squirrel's schema generation to create tables for a target database, and it doesn't use char when the original used char, then stuff won't fit and it will "blow up". I think what you're saying is that you use NLS_LENGTH_SEMANTICS to set it globally, and you think this might be a problem if byte fields are explicitly "byte". But as far as I see, this wouldn't be a problem for you, because if you have NLS_LENGTH_SEMANTICS set to char globally, then all your tables will probably be char globally, and thus all the tables generated by squirrel (after the proposed change) would be char explicitly.