Error when generating insert statements

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Error when generating insert statements

Volker Siebelink
Hello,

I have this table:



Note that column ENEX is of type char(2). When generating insert
statements for this table, I get:

Insert into T_GTE
    (EZPT, CREATOR, AEZPT, CHANGER, TAG,
     ETSOEIC_CODE, ENEX, VVON, VBIS, LAST_EXPORT_DATE,
     LAST_EXPORTED_DATE, CONFIDENTIAL, CONFIRMED)
  Values
    (TO_DATE('12/05/2012 14:15:52', 'MM/DD/YYYY HH24:MI:SS'), 'TED',
TO_DATE('12/06/2012 08:08:14', 'MM/DD/YYYY HH24:MI:SS'), 'TED',
'001.TOAST__H018___.LAS.A.D.WM__.RW.H',
     '37Z000000004972G', 'EX ', TO_DATE('04/01/2007 06:00:00',
'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2999 06:00:00', 'MM/DD/YYYY
HH24:MI:SS'), TO_DATE('12/06/2012 10:46:08', 'MM/DD/YYYY HH24:MI:SS'),
     TO_TIMESTAMP_TZ('01.02.2012 07:00:00, -01:00','DD.MM.YYYY
HH24:MI:SS,FF TZH:TZM'), 0, -1);

Here we have two errors:

 1. The value to be inserted into column ENEX is 'EX ', which has 3
    characters.
 2. The expression for the timestamp is wrong. It should be
    "TO_TIMESTAMP_TZ('01.02.2012 07:00:00 -01:00','DD.MM.YYYY HH24:MI:SS
    TZH:TZM')"
 3. when exporting more than 50 row, the ',' in the format string of the
    timestamp changes to '.'.

So, after generating insert statements the output has to be edited each
time. This is quite annoying when the output file is more than 50MB in
size :-(

I use TOAD 11.6.0.43.

Regards,
Volker


--
Volker Siebelink        | phone:  +49 291 9985-20
MEGLA GmbH              | fax:    +49 291 9985-51
Feldstraße 34           | mailto: [hidden email]
D-59872 Meschede        | URL:    http://www.megla.de

Geschäftsführer        Dipl.-Ing. Volker Siebelink
Firmensitz/
Registergericht        Meschede, Amtsgericht Arnsberg, HRB 3288
Umsatzsteuer-
Identifikationsnummer  DE 125917642


iaibgghh.png (19K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: Error when generating insert statements

John Dorlon
Hi Volker,

  What version of Toad are you using?

  Would you be able to export this table and email the export file to me?

-John

From: [hidden email] [mailto:[hidden email]] On Behalf Of Volker Siebelink
Sent: Friday, December 07, 2012 5:35 AM
To: [hidden email]
Subject: [toad] Error when generating insert statements

Hello,

I have this table:

[cid:image001.png@01CDD457.96DBC180]

Note that column ENEX is of type char(2). When generating insert statements for this table, I get:

Insert into T_GTE
   (EZPT, CREATOR, AEZPT, CHANGER, TAG,
    ETSOEIC_CODE, ENEX, VVON, VBIS, LAST_EXPORT_DATE,
    LAST_EXPORTED_DATE, CONFIDENTIAL, CONFIRMED)
 Values
   (TO_DATE('12/05/2012 14:15:52', 'MM/DD/YYYY HH24:MI:SS'), 'TED', TO_DATE('12/06/2012 08:08:14', 'MM/DD/YYYY HH24:MI:SS'), 'TED', '001.TOAST__H018___.LAS.A.D.WM__.RW.H',
    '37Z000000004972G', 'EX ', TO_DATE('04/01/2007 06:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2999 06:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/06/2012 10:46:08', 'MM/DD/YYYY HH24:MI:SS'),
    TO_TIMESTAMP_TZ('01.02.2012 07:00:00, -01:00','DD.MM.YYYY HH24:MI:SS,FF TZH:TZM'), 0, -1);

Here we have two errors:

  1.  The value to be inserted into column ENEX is 'EX ', which has 3 characters.
  2.  The expression for the timestamp is wrong. It should be "TO_TIMESTAMP_TZ('01.02.2012 07:00:00 -01:00','DD.MM.YYYY HH24:MI:SS TZH:TZM')"
  3.  when exporting more than 50 row, the ',' in the format string of the timestamp changes to '.'.

So, after generating insert statements the output has to be edited each time. This is quite annoying when the output file is more than 50MB in size :-(

I use TOAD 11.6.0.43.

Regards,
Volker



--

Volker Siebelink        | phone:  +49 291 9985-20

MEGLA GmbH              | fax:    +49 291 9985-51

Feldstraße 34           | mailto: [hidden email]<mailto:[hidden email]>

D-59872 Meschede        | URL:    http://www.megla.de



Geschäftsführer        Dipl.-Ing. Volker Siebelink

Firmensitz/

Registergericht        Meschede, Amtsgericht Arnsberg, HRB 3288

Umsatzsteuer-

Identifikationsnummer  DE 125917642

image001.png (19K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Error when generating insert statements

Volker Siebelink
Hi John,

I'm on 11.6.0.43. I'll send you the export file.

Regards,
Volker

Am 07.12.2012 15:48, schrieb John Dorlon:

>
> Hi Volker,
>
>   What version of Toad are you using?
>
>   Would you be able to export this table and email the export file to me?
>
> -John
>
> *From:*[hidden email] [mailto:[hidden email]] *On Behalf
> Of *Volker Siebelink
> *Sent:* Friday, December 07, 2012 5:35 AM
> *To:* [hidden email]
> *Subject:* [toad] Error when generating insert statements
>
> Hello,
>
> I have this table:
>
>
>
> Note that column ENEX is of type char(2). When generating insert
> statements for this table, I get:
>
> Insert into T_GTE
>    (EZPT, CREATOR, AEZPT, CHANGER, TAG,
>     ETSOEIC_CODE, ENEX, VVON, VBIS, LAST_EXPORT_DATE,
>     LAST_EXPORTED_DATE, CONFIDENTIAL, CONFIRMED)
>  Values
>    (TO_DATE('12/05/2012 14:15:52', 'MM/DD/YYYY HH24:MI:SS'), 'TED',
> TO_DATE('12/06/2012 08:08:14', 'MM/DD/YYYY HH24:MI:SS'), 'TED',
> '001.TOAST__H018___.LAS.A.D.WM__.RW.H',
>     '37Z000000004972G', 'EX ', TO_DATE('04/01/2007 06:00:00',
> 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2999 06:00:00', 'MM/DD/YYYY
> HH24:MI:SS'), TO_DATE('12/06/2012 10:46:08', 'MM/DD/YYYY HH24:MI:SS'),
>     TO_TIMESTAMP_TZ('01.02.2012 07:00:00, -01:00','DD.MM.YYYY
> HH24:MI:SS,FF TZH:TZM'), 0, -1);
>
> Here we have two errors:
>
>  1. The value to be inserted into column ENEX is 'EX ', which has 3
>     characters.
>  2. The expression for the timestamp is wrong. It should be
>     "TO_TIMESTAMP_TZ('01.02.2012 07:00:00 -01:00','DD.MM.YYYY
>     HH24:MI:SS TZH:TZM')"
>  3. when exporting more than 50 row, the ',' in the format string of
>     the timestamp changes to '.'.
>
> So, after generating insert statements the output has to be edited
> each time. This is quite annoying when the output file is more than
> 50MB in size :-(
>
> I use TOAD 11.6.0.43.
>
> Regards,
> Volker
>
>
>
> --
> Volker Siebelink        | phone:  +49 291 9985-20
> MEGLA GmbH              | fax:    +49 291 9985-51
> Feldstraße 34           | mailto:[hidden email]  <mailto:[hidden email]>
> D-59872 Meschede        | URL:http://www.megla.de
>  
> Geschäftsführer        Dipl.-Ing. Volker Siebelink
> Firmensitz/
> Registergericht        Meschede, Amtsgericht Arnsberg, HRB 3288
> Umsatzsteuer-
> Identifikationsnummer  DE 125917642
>

--
Volker Siebelink        | phone:  +49 291 9985-20
MEGLA GmbH              | fax:    +49 291 9985-51
Feldstraße 34           | mailto: [hidden email]
D-59872 Meschede        | URL:    http://www.megla.de

Geschäftsführer        Dipl.-Ing. Volker Siebelink
Firmensitz/
Registergericht        Meschede, Amtsgericht Arnsberg, HRB 3288
Umsatzsteuer-
Identifikationsnummer  DE 125917642




part1.03030005.06000803@megla.de (19K) Download Attachment