Encoding for script created by Toad

Toad for Oracle
1

Hello, there,

I am having issue with the encoding of script created by Toad.

I had set UTF-8 as encoding in the following 2 places:

File–>General–>default encoding

Scripts–>spool file encoding

I have some functions in the database, some functions have some special character inside, for example, French accent letter ‘é’, when I create function script in Toad(one file per object), the script files of those functions with the special character are in ANSI encoding, for those functions do not have the letter, the script file was generated with UTF-8 encoding, by the way, i verified the encoding by opening the file with Notepad++, which can tell me the encoding.

I have issue with ANSI encoding script file.

Is this a bug or anywhere I didn’t configure? For the functions with special character are supposed to be generated as UTF-8 encoding, other than ANSI. I tested package scripts, got same results. The scripts generated using SQL Developer works fine.

My toad version is Toad for Oracle xpert 64-bit, 12.8.0.49

Thanks in advance for your comments and help.

Rex

2

I tried simple test:

set serveroutput on size unlimited;
spool ut8_test.lst
select ‘é’ from dual;
spool off;

Response in Toad:

Time Start: 12/04/2018 09:48:21

‘É’

é
Elapsed: 00:00:00.02
Time End: 12/04/2018 09:48:22
Elapsed Time for Script Execution: 830 msecs

And spoole file has correct values.
So any way to see your example?
P.S.

Spool works from SQL*Plus (direct in oracle)?
If no then look in your client side settings

3

Thanks Damir, it seems your script works, when i ran it and created the file, opened with notepad++, the encoding is UTF-8. I am pasting a script to create 2 test function here, the difference of the two functions is, one assigning ‘e’ to the returning variable, the other assigns ‘é’ to the returning variable. After the 2 functions created, right click the 2 functions in toad, and create script files, choose 1 file per object, thus you will create 2 files, open them with notepad++, check the encoding in notepad++, the one with ‘é’ is ANSI, the other one with ‘e’ is UTF-8, which will be my problem, i need the script file created consistent with UTF-8 encoding. Here is the script:

CREATE OR REPLACE FUNCTION TEST_UTF8 RETURN VARCHAR2
IS
var_any VARCHAR2(100);
BEGIN
var_any := ‘é’;
RETURN var_any;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

CREATE OR REPLACE FUNCTION TEST_UTF8_2 RETURN VARCHAR2
IS
var_any VARCHAR2(100);
BEGIN
var_any := ‘e’;
RETURN var_any;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/

4

I am wondering whether anybody can look into this issue.

Thanks,

Rex

5

Hey Rex,

Did you set the encoding drop-down when you right-clicked on the object and selected “Create -> Script”? That Encoding drop-down may be set to ANSI, which would export the scripts using that encoding. You may need to set that to UTF-8 as well.

Let us know,

-John

6

Thanks John for your response. I still have the same issue now. When right click a object to create scripts, there is no drop-down to let me select encoding. There are only 2 places I found in option setting(mentioned in this post), where I already set to utf-8.
When I select more than 1 objects to export, some are exported as utf-8, some are exported as ansi. The one exported as utf-8 with special character cannot be read properly in notepad++.

7

The dropdown, shown below, was added in Toad version 12.12

However, if I remember correctly, you can work around it in 12.8 like this:

  1. in the Export DDL Dialog, choose the "Single File" option.
  2. click the '...' button after the file name edit box,
  3. choose encoding in the "save as" dialog that appears.

image
image.jpg769×726 180 KB

8

Oh, and I forgot to mention, this trick works even if you want to save to “one file per object”. So, to do that, the steps would be:

  1. in the Export DDL Dialog, choose the “Single File” option.
  2. click the ‘…’ button after the file name edit box,
  3. choose encoding in the “save as” dialog that appears and click OK
  4. change to “One file per object”
  5. The “filename” edit box changes to “directory”, so put your directory in there.
  6. hit OK to run it.
9

Thanks a lot, jdorlon, my toad is 12.8, the trick works. Once i did the trick, it seems toad remember it, and the scripts generated has consistent encoding now.

10

Great!

                                       88  
  ,d                                   88  
  88                                   88  
MM88MMM ,adPPYba,  ,adPPYYba,  ,adPPYb,88  
  88   a8"     "8a ""     `Y8 a8"    `Y88  
  88   8b       d8 ,adPPPPP88 8b       88  
  88,  "8a,   ,a8" 88,    ,88 "8a,   ,d88  
  "Y888 `"YbbdP"'  `"8bbdP"Y8  `"8bbdP"Y8
1 Like