Toad World® Forums

Export Dataset: Creating inserts with ESC-Sequences like CHR(27)CHR(13)


#1

I want to crate insert statements with Schema Browser / Export Datasets where the contents contain a lot of ESC-sequences (thiese are label definitions for label printers).

Doing so creates the statement like this:

Insert into BAUHAUSTRADE.ETIDEF
(ETIDEF_PRINTER, ETIDEF_ETINO, ETIDEF_INIT)
Values
(‘SATO’, ‘5’, ‘AA3V+000H+000CS6#F5A1V01696H0504
PS’, );

but it should be like this:

insert into ETIDEF (etidef_printer, etidef_etino, etidef_init)
values (‘SATO’, ‘5’, ‘’ || chr(2) || ‘’ || chr(27) || ‘A’ || chr(27) || ‘A3V+000H+000’ || chr(27) || ‘CS6’ || chr(27) || ‘#F5’ || chr(27) || ‘A1V01696H0504’ || chr(13) || ‘’ || chr(10) || ‘’ || chr(27) || ‘PS’);

Is there any output option I can activate?

(The product PL/SQL Developer creates the statements in this way (NO, it’s no option to use this one!)

I need this for a reliable import into other schemas.


#2

Unfortunately, I don’t think there is a way to get Toad to produce insert statements like that.

But, there is a way in Toad to get the data moved from one schema to another. Rt-click on the source table in the Schema Browser and choose “Copy Data to Another Schema”. I just tested it with your data and it to work fine.


#3

Hi John,

thanks for your answer. That’s very sad.

The problem with “Copy Data to another schema” is that I’ve 190 Systems in different cuntries and sometimes I have to update the label definitions. But I cannot truncate the table before updating.

Normally we use an update mechanism where we send the new application parts to the systems an run the scripts. So I need the SQL version of my definitions. With other printer types this is no problem because they only use “^” and “~” as control characters. Therefore I can use MERGE-statements…


#4

I just tried exporting it as merge statements to a file and then running it with SQL*Plus and Toad. It looks like the only problem is the CHR(13) || CHR(10) combination.

So how about this as a workaround:

  1. Export to merge statements from this SQL, which replaces CHR(13) || CHR(10) with some text that you can identify later.

select etidef_printer, etidef_etino,

replace(etidef_init, chr(13) || chr(10), ‘’) etidef_init

from etidef;

  1. at the end of your script, before the commit, add this statement to change the text back to CHR(13) || CHR(10).

update etidef

set etidef_init = replace(etidef_init, ‘’, CHR(13) || CHR(10);

I just tried that and it worked for me. I used this select statement to verify that my two tables had the same data:

select ‘1’, dump(etidef_init)

from jdorlon.etidef

union all

select ‘2’, dump(etidef_init)

from jdorlon2.etidef

or you could do this statement to confirm that they are the same, if you don’t want to eyeball it:

select *

from jdorlon.etidef

minus

select *

from jdorlon2.etidef


#5

Oops, I forgot to correct a mistake in the update statement. It should be:

update etidef

set etidef_init = replace(etidef_init, ‘’, CHR(13) || CHR(10));


#6

Hi John,

thanks for all you testing and figuring out a possible solution.

This way seems to be ok for us. I hope to find some time to check this with our full dataset. Possible this will be next week because of a bank holiday on Friday.

I will tell you the results of my testing…


#7

Hi John,

finally I found some time for testing :slight_smile:

It seems to work like you suggested. A little bit mor work but we don’t need this very often.

The labels I printed after loading and converting CRLF again are fine.

Thanks for your help.

Brigitte