Syncronization issue with quotes in character string

Toad Data Point
1

Hello. I am evaluating Toad Data Point 4.3.0.718 (64 bit). I experimented with creating a script that would synchronize some CLOB data across servers.

The resulting script looks like this:

`INSERT INTO “SOME_SCHEMA”.“SOME_TABLE” (“KEY”, “CLOB_FIELD”)
VALUES (‘key_value’, empty_clob());

DECLARE
lobcol CLOB ;
line varchar2(4000);
BEGIN
UPDATE “SOME_SCHEMA”.“SOME_TABLE” SET “CLOB_FIELD” = empty_clob()
WHERE “KEY” = ‘key_value’ returning CLOB_FIELD into lobcol;
dbms_lob.open (lobcol, dbms_lob.lob_readwrite);
line := ‘This clob data contains the user’s text.’;
dbms_lob.write (lobcol, length(line), 1, line);
dbms_lob.close (lobcol);
END;`

Notice the line := statement includes a quote in the value (user’s). It should be double-quoted or else it won’t work when I run the script on the target server.

Instead, I get this error: “ORA-01756: quoted string not properly terminated”

I’ve looked through the forum and the options but couldn’t find any way to make this work.

Is there a way to get the synchronization wizard to prepare the script with double-quotes?

Thank you .

2

I try use q’[…]’ and it is works.

INSERT INTO “SOME_SCHEMA”.“SOME_TABLE” (“COLUMN_1”)
VALUES (q’[This clob data contains the user’s text.]’);

3

Thanks Petr.

It worked for me to change the line := statement to

line := q'[This clob data contains the user's text.]';

So is it possible to get the synchronization wizard to do this automatically?

Or do I need to massage the script after it’s generated?

4

It is impossible add to synchronization wizard for now.

I created task (QAT-13164) for change in synchronization wizard.

5

Just wanted to say that after 2 years, we are using this now and the added feature of using q'[...]' for synchronizing CLOBS appears to be working.

Current version is Toad Data Point 5.0.7.106 (64 bit).

6

Further to my last comment, I have some more 'findings'. We are running the script on some complex data ... CLOBS that include quotes, question marks, ampersands, and French accents.

  1. Running the script directly in Phire (our promotion tool) doesn't work. It will not translate the q'[ ... ]' properly when it contains quotes.
  2. Running the script directly from Toad Data Point doesn't work when there are both quotes (') and question marks (?) inside the q'[ ... ]' structure.
  3. The script also should put a slash (/) after each END; from the procedures. I had to add that in manually using UltraEdit search/replace.
  4. Finally got the script to run from SQL Developer, but first had to a) set define off; so ampersands (&) do not prompt for replacement variables, and b) have encoding set to UTF-8 so accents do not get garbled.
    Hope this helps someone!