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

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.

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.

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…

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

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));

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…

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