Editing data in a grid using an update script
I tried to use Toad 12.1 DBA and SQLDeveloper to achieve my goal, but I got the same results.
When I update the NVARCHAR2 cell in the grid , I can set and fix the '€' sign as value. But when I execute an update script to do the same, it prints '?' symbol as data.
Here is the output when I edit and freeze the data in the grid:
Here is the problem when I use the update script to do the same:
I tried using different NLS_LANG options but they didn't work either. AMERICAN_AMERICA.WE8ISO8859P9 AMERICAN_AMERICA.AL16UTF16
The NLS database options are located here:
SELECT * FROM NLS_DATABASE_PARAMETERS where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------ ------------
NLS_CHARACTERSET WE8ISO8859P9
NLS_NCHAR_CHARACTERSET AL16UTF16
Create a script for a simple table:
CREATE TABLE AAA
(
NVARCHAR2COL NVARCHAR2(50)
);
I also tried using sqlplus to perform the update script, but it also printed the question mark as data.
Edit: One more thing, using an update script with the unistr function, but I need to update the data with readable text:
UPDATE AAA SET NVARCHAR2COL = unistr('\20AC');
COMMIT;
source to share
The default Text Literal data type is CHAR. CHAR strings can only contain ASCII characters and there is no character in the ASCII table '€'
, so it is '€'
converted to '?'
at compile-time statement.
To use the national character set, the string value must be prefixed N
. The function N
converts data at compile time to NCHAR.
UPDATE AAA SET NVARCHAR2COL = N'€';
must work.
Alternatively, you can use UPDATE AAA SET NVARCHAR2COL = to_nchar('€');
.
When the value in the grid is updated, the authoring tool makes it transparent to the user.
source to share