#54 Generate DDL with CHAR or BYTE keyword

open
nobody
None
5
2009-01-06
2009-01-06
Anonymous
No

Today I'm working on transfering data from a gigant audit table to several tables, one per month, in another server. I used SQLTools++ to generate the DDL and started using it to create the destination tables. After a while I noted by accident that the definition of the new tables were different from the original and after checking I understood why. Both oracle databases are configured so that a char or varchar2 definition is given in characters (NAME VARCHAR2(20) = NAME VARCHAR2(20 CHAR)) and both use a set of unicode as character sets. When SQLTools++ got the DDL it generated the definition in BYTES so I got (NAME VARCHAR2(120)). When runing that script on the new server it creates a column of 120 characters (480 Bytes). It wasn't a big problem since I resolved it with a simple alter table, however It would be nice if SQLTools++ could add in the definition the keyword CHAR or BYTE so that the size of the colums stays as defined on the original independently of where you run the script.

Thank you

Discussion

  • randolf_geist
    randolf_geist
    2009-01-06

    Not entirely sure about your problem description, since you should use the appropriate NLS_LENGTH_SEMANTICS setting (BYTE or CHAR).

    You might want to check what is going to be generated if you use the "Use DBMS_METADATA" option to generate the DDL, I think it already includes an explicit semantics identifier.

     
  • My problem was that NLS_LENGTH_SEMANTICS was set to CHAR and SQLTools generated the DDL for the varchar2 columns with the length in bytes so when I ran that script on an oracle with NLS_LENGTH_SEMANTICS in CHAR it would create the columns with a wrong length. For example:

    Script to create the source table: CREATE TABLE Test (mycolumn varchar2(5)) /* My column will have 5 characters = 20 Bytes */

    SQLTools++ generate from created table: CREATE TABLE Test (mycolumn varchar2(20)) /* Returns the number of bytes and when I ran this without on the destination it generated a varchar column of 20 characters length (80 Bytes) */

    However the DBMS_METADATA option answers my request, it generates the DDL script exactly as I wanted it.

    Thank you!

     
  • randolf_geist
    randolf_geist
    2009-01-07

    OK, now I got the point, this is more a bug than a feature, since you get the wrong size in your scripts (based on bytes rather than chars). Can you please download the latest version of SQLTools++ (currently V1.21RC3) if you haven't done so yet, resp. let me know which version you've used to test this. There was a bug (1939328: Length of multibyte character columns incorrectly reported) that was fixed, so I would like to know if you still get the same behaviour when using the latest version (not using the DBMS_METADATA option).

     
  • The version I'm currently using is "SQLTools_pp 1.21 RC3 (for trouble-shooting)". I downloaded it from Sourceforge about a couple weeks ago.

     
  • randolf_geist
    randolf_geist
    2009-01-07

    I can reproduce your issue using the latest release. The mentioned bug fixed only the handling of NVARCHAR and NCHAR columns but multi-byte VARCHAR and CHAR columns are still causing what you've described. I'll open another bug for this, but will leave this feature request open as well, because the initial bug fix will be to have the "correct" size specified, although it won't use the "BYTE" or "CHAR" keyword (and therefore is an incomplete solution if you have a mixture of BYTE and CHAR definitions in your database), so this feature request still is reasonable.

    If you drop me a quick email on info( a t )sqltools-plusplus( dot )org I can send you a version of the executable containing the initial fix, so that you can test it, if you like.