I have been struggling to get Apex 3.2 to print PDF, using BI Publisher.
Time and time again, the developer reported he got the -by now- dreaded error:
"ORA-20001: The printing engine could not be reached because either the URL specified is incorrect or a proxy URL needs to be specified."
Because I spent quite a lot of time researching this, and the solution was so simple, this entry.
Please be warned: this error is to be expected when using 11G and the database listener.
This error also seems to manifest when you use the defaults, which do not seem to get accepted; instead: fill out the fields (with Print server host address 'localhost' and port '8888')
In my case, however, it turned out to be a space, following the port number.
Hope this helps somebody to save some time...
Edit: see this entry for setting this up in a run-time only environment.
Edit: see this entry if you run into an ORA-20001 with Oracle 11G
Tuesday, July 14, 2009
Friday, May 15, 2009
Apex runtime maintenance: wwv_flow_api and #IMAGE_PREFIX#
For some reason, the most important API within Apex is not well documented. I was asked to change the image prefix in an Apex environment.
However... this was a run-time only install. And much to my astonishment, there is no APEX API to change the image prefix for an application.
So, basically, you want this:
It does no more than update the wwv_flows table, it seems:
By the way: changing the prefix overall is done by running the reset_image_prefix.sql script, which can be found in the utilities subdirectory of your apex source code.
GUI
Using the Graphical User Interface, or GUI, it would have been easy:- On the Workspace home page, click the Application Builder icon.
- Select an application.
- On the Application home page, click Shared Components.
- Under Application, select Definition.
- When the Edit Application Definition page appears, locate the Image Prefix field.
However... this was a run-time only install. And much to my astonishment, there is no APEX API to change the image prefix for an application.
wwv_flow_api
After inspection of an Apex application import file, I found the wwv_flow_api procedure is used to create the flow, and define the image prefix (hint: you can change it in the import file, before import). And sure enough, there is a procedure set_image_prefix:procedure set_image_prefix (
p_flow_id in number default null,
p_image_prefix in varchar2 default null)
;
So, basically, you want this:
begin
wwv_flow_api.set_security_group_id(p_security_group_id=>
APEX_UTIL.FIND_SECURITY_GROUP_ID('APP1'));
wwv_flow_api.set_image_prefix(p_image_prefix=>'/i32', p_flow_id=>100);
end;
/
It does no more than update the wwv_flows table, it seems:
SQL> col FLOW_IMAGE_PREFIX for a10
SQL> select id, flow_image_prefix from FLOWS_030100.wwv_flows;
ID FLOW_IMAGE
---------- ----------
100 /i/
4411 /i/
4155 /i/
SQL> begin
2 wwv_flow_api.set_security_group_id(p_security_group_id=>
3 APEX_UTIL.FIND_SECURITY_GROUP_ID('APP1'));
4 wwv_flow_api.set_image_prefix(p_image_prefix=>'/i32/', p_flow_id=>100);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select id, flow_image_prefix from FLOWS_030100.wwv_flows;
ID FLOW_IMAGE
---------- ----------
100 /i32/
4411 /i/
4155 /i/
SQL> rollback;
By the way: changing the prefix overall is done by running the reset_image_prefix.sql script, which can be found in the utilities subdirectory of your apex source code.
Thursday, May 14, 2009
Project Raw Iron, pardon me, Virtual Iron
Crisis? What crisis?
Never heard of the company (Virtual Iron, that is), and wonder if this would be an "if you can't beat them..." action. What would the impact be on Oracle's Linux fork?
Never heard of the company (Virtual Iron, that is), and wonder if this would be an "if you can't beat them..." action. What would the impact be on Oracle's Linux fork?
Saturday, May 02, 2009
"Special" characters - part IV
By now, special characters should not be special. The whole point is that these characters have code points that have different shapes in different character sets. Once you realize that, it's simple. Make sure you use the same character set all the way.
If you can't do that, make sure you understand what to use, and how it's done. Reread previous parts.
Is there a silver bullet? A cure against all weird characters? No, there's not. No off-the-shelf menu to be found here. I do not know your system; you do. I can only depict a pretty standard situation I know about, and I can reproduce.
So, what about a pretty standard situation? How's this:
For the client install, I'll use a clean laptop; clean meaning: no previous Oracle installs. In this case, also meaning Vista wiped off, XP on again. So, all in all, pretty clean.
Of course, the (standard) install will use WE8MSWIN1252 as coding set.
The database instance will be on Linux (CentOS to be precise). I'll use a 10G Release 2, as it is predominant in Europe. The (standard) install will use WE8ISO8859P1, which I changed to P15
The web can be deleted here. The web interface on the database will encode the contents as UTF-8, as usual. Whether this is correct remains to be seen. There's an entry on displaying special characters on the web, too.
Install CentOS Server V4.7, and update.
Install missing Oracle stuff:
yum install gcc gcc-c++ openmotif21 sysstat gnome-libs libstdc++-devel compat-libstdc++-33 compat-db-4 libaio
If you don't, you will get this:
Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2009-09-30_10-39-42AM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory as well as linking errors in ins_rdbms.mk.
The ORCL instance was created using the following code:
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /oracle/db10/admin/orcl/scripts/CreateDB.log
startup nomount pfile="/oracle/db10/admin/orcl/scripts/init.ora";
CREATE DATABASE "orcl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oracle/data/orcl/orcl/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/data/orcl/orcl/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/data/orcl/orcl/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle/data/orcl/orcl/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8ISO8859P15
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oracle/data/orcl/orcl/redo01.log') SIZE 51200K,
GROUP 2 ('/oracle/data/orcl/orcl/redo02.log') SIZE 51200K,
GROUP 3 ('/oracle/data/orcl/orcl/redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off Mind the difference with the default, the DBCA would choose: the characterset is WE8ISO8859P15 in order to support the €-symbol.
The environment is set whenever I login on the machine, by .bash_profile being executed. It has the following settings:
export ORACLE_BASE=/oracle/db10
export ORACLE_HOME=$ORACLE_BASE/10GRel2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
export DISPLAY=192.168.1.100:0.0
echo "Oracle settings done"Here, too, the €-symbol supporting characterset is used. Of course, you will only actually see it when your terminal program supports it. I hope that is clear now, else see part I.
Let's do the basic test, scott is created again:
SQL> create table blah (A varchar2(10) );
Table created.
SQL> insert into blah values ('€');
1 row created.
SQL> insert into blah values('Æ');
1 row created.
SQL> insert into blah values('ß');
1 row created.
SQL> col dump(a,16) format a20
SQL> select a, dump(a,16) from blah;
A DUMP(A,16)
---------- --------------------
€ Typ=1 Len=1: 80
Æ Typ=1 Len=1: c6
ß Typ=1 Len=1: df
That works. Kind of. First thing to notice, is that the code point for the € is now 0x80, where it used to be 0xa4 (or 164 decimal). Now for the MS Windows client... It should not present a problem, as MSWIN1252 is a superset of ISO8859P15. A superset is where all code points are mapped to the same characters, plus extra. In other words: all characters, seen with ISO8859P15 can still be seen with WIN1252, and then some (that ISO8859P15 does not have).
SQL> select * from blah;
A
----------
┐
╞
▀OK, that looks weird, as the client maps the characters, dictated by the code points to other characters then originally. Let's change the client, so that it can map the codepoints.
C:\>chcp 1252
Active code page: 1252
C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 30 16:11:22 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> select * from blah;
A
----------
¿
Æ
ßOK - a lot better, but still no €. And I can get this CLI session to display a € sign by using ALT-0128 on the numeric keypad. Heck, I can even insert the € correctly, why can't this stupid DBA?!?
OK - what went wrong?!? The DBA inserted the € correctly. It could be retrieved, and -from a MicroSoft point of view- has the correct code point: 0x80, or 128 decimal (remember you can display the € on a MS Windows machine by holding the ALT button, and typing 0128 on a numeric keypad? Coincidence?)
The client cannot see the € correctly - instead it has the dreaded upside-down question mark. But it actually can insert a € sign, and retrieve it...
What is going on here? The client uses a default of AMERICAN_AMERICA.WE8MSWIN1252 for NLS_LANG (set in the registry during the client install). The DBA used the database character set for NLS_LANG: WE8ISO8859P15. Also, he copied the € symbol off a web page. His workstation? A MicroSoft Windows machine...
So: DBA has € in MS Win code page 437. That would be 0128, or 0x80. Copies that into a SQL*Plus session with NLS_LANG equal to DB Characterset. Because of that, not code point re-mapping takes place. 0x80 is stored as the €. Upon retrieval: same route other way around: € displays correctly.
Client however, retrieves 0x80 in WIN1252. The database delivers this in ISO8859P15, so the network substrate tries to remap 0x80. However, there is no mapping from 0x80 in ISO8859P15 to MSWIN1252 - hence the inverted question mark.
As the client inserts the €, there is a code point conversion from 0x80 to the corresponding currency sign in 8859P15: the € as 0xA4. Client can retrieve that, as the same logic applies as with the DBA session: same route other way around, and the character is displayed correctly.
Now, homework: explain what the DBA sees when the customer starts complaining, and why.
To be cont'd...
If you can't do that, make sure you understand what to use, and how it's done. Reread previous parts.
Is there a silver bullet? A cure against all weird characters? No, there's not. No off-the-shelf menu to be found here. I do not know your system; you do. I can only depict a pretty standard situation I know about, and I can reproduce.
So, what about a pretty standard situation? How's this:
- MS Windows client
- Unix (well, Linux) based database, that has
- a web interface, which is browsed from
- a MS Windows client.
The Client
For the client install, I'll use a clean laptop; clean meaning: no previous Oracle installs. In this case, also meaning Vista wiped off, XP on again. So, all in all, pretty clean.
Of course, the (standard) install will use WE8MSWIN1252 as coding set.
The Database.
The database instance will be on Linux (CentOS to be precise). I'll use a 10G Release 2, as it is predominant in Europe. The (standard) install will use WE8ISO8859P1, which I changed to P15
The Web.
The web can be deleted here. The web interface on the database will encode the contents as UTF-8, as usual. Whether this is correct remains to be seen. There's an entry on displaying special characters on the web, too.
Preparations:
Install CentOS Server V4.7, and update.
Install missing Oracle stuff:
yum install gcc gcc-c++ openmotif21 sysstat gnome-libs libstdc++-devel compat-libstdc++-33 compat-db-4 libaio
If you don't, you will get this:
Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2009-09-30_10-39-42AM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
Database
The ORCL instance was created using the following code:
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /oracle/db10/admin/orcl/scripts/CreateDB.log
startup nomount pfile="/oracle/db10/admin/orcl/scripts/init.ora";
CREATE DATABASE "orcl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oracle/data/orcl/orcl/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/data/orcl/orcl/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/data/orcl/orcl/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle/data/orcl/orcl/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8ISO8859P15
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oracle/data/orcl/orcl/redo01.log') SIZE 51200K,
GROUP 2 ('/oracle/data/orcl/orcl/redo02.log') SIZE 51200K,
GROUP 3 ('/oracle/data/orcl/orcl/redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off
The environment is set whenever I login on the machine, by .bash_profile being executed. It has the following settings:
export ORACLE_BASE=/oracle/db10
export ORACLE_HOME=$ORACLE_BASE/10GRel2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
export DISPLAY=192.168.1.100:0.0
echo "Oracle settings done"
Let's do the basic test, scott is created again:
SQL> create table blah (A varchar2(10) );
Table created.
SQL> insert into blah values ('€');
1 row created.
SQL> insert into blah values('Æ');
1 row created.
SQL> insert into blah values('ß');
1 row created.
SQL> col dump(a,16) format a20
SQL> select a, dump(a,16) from blah;
A DUMP(A,16)
---------- --------------------
€ Typ=1 Len=1: 80
Æ Typ=1 Len=1: c6
ß Typ=1 Len=1: df
That works. Kind of. First thing to notice, is that the code point for the € is now 0x80, where it used to be 0xa4 (or 164 decimal). Now for the MS Windows client... It should not present a problem, as MSWIN1252 is a superset of ISO8859P15. A superset is where all code points are mapped to the same characters, plus extra. In other words: all characters, seen with ISO8859P15 can still be seen with WIN1252, and then some (that ISO8859P15 does not have).
SQL> select * from blah;
A
----------
┐
╞
▀
C:\>chcp 1252
Active code page: 1252
C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 30 16:11:22 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> select * from blah;
A
----------
¿
Æ
ß
SQL> insert into blah values ('€');
1 row created.
SQL> select A, dump(A,16) from blah;
A DUMP(A,16)
---------- ---------------
€ Typ=1 Len=1: a4
¿ Typ=1 Len=1: 80
Æ Typ=1 Len=1: c6
ß Typ=1 Len=1: df
SQL> commit;
See? First entry: the €!1 row created.
SQL> select A, dump(A,16) from blah;
A DUMP(A,16)
---------- ---------------
€ Typ=1 Len=1: a4
¿ Typ=1 Len=1: 80
Æ Typ=1 Len=1: c6
ß Typ=1 Len=1: df
SQL> commit;
Enter the War of the Worlds.
OK - what went wrong?!? The DBA inserted the € correctly. It could be retrieved, and -from a MicroSoft point of view- has the correct code point: 0x80, or 128 decimal (remember you can display the € on a MS Windows machine by holding the ALT button, and typing 0128 on a numeric keypad? Coincidence?)
The client cannot see the € correctly - instead it has the dreaded upside-down question mark. But it actually can insert a € sign, and retrieve it...
What is going on here? The client uses a default of AMERICAN_AMERICA.WE8MSWIN1252 for NLS_LANG (set in the registry during the client install). The DBA used the database character set for NLS_LANG: WE8ISO8859P15. Also, he copied the € symbol off a web page. His workstation? A MicroSoft Windows machine...
So: DBA has € in MS Win code page 437. That would be 0128, or 0x80. Copies that into a SQL*Plus session with NLS_LANG equal to DB Characterset. Because of that, not code point re-mapping takes place. 0x80 is stored as the €. Upon retrieval: same route other way around: € displays correctly.
Client however, retrieves 0x80 in WIN1252. The database delivers this in ISO8859P15, so the network substrate tries to remap 0x80. However, there is no mapping from 0x80 in ISO8859P15 to MSWIN1252 - hence the inverted question mark.
As the client inserts the €, there is a code point conversion from 0x80 to the corresponding currency sign in 8859P15: the € as 0xA4. Client can retrieve that, as the same logic applies as with the DBA session: same route other way around, and the character is displayed correctly.
Now, homework: explain what the DBA sees when the customer starts complaining, and why.
To be cont'd...
Friday, April 24, 2009
Whoo!
Little Darling, it seems like years since it's been here.
Here comes the sun.
Just click the header for the press release.
Here comes the sun.
Just click the header for the press release.
Thursday, April 23, 2009
Database agnostic and Hibernate
You know, there once was a time I genuinely loved Java. I thought it would be a great language with even better security. Then came V0.8, and we had to rewrite everything we'd done so far during the class...
Since then, I changed. Java stepped out of the sandbox, was even tangled with by Microsoft, and I've become a bitter old man. I've seen too many Java "programmers" that just select from table a, and from table b, sort the a results, sort the b results, join a and b and discard 99% of the result set , and then complain about the performance of the database.
This entry made my life happier; maybe it is not "just me" - read the April, 22nd 2009 entries.
Grin.
By the way: java programmers: read up on ref_cursors...
Since then, I changed. Java stepped out of the sandbox, was even tangled with by Microsoft, and I've become a bitter old man. I've seen too many Java "programmers" that just select from table a, and from table b, sort the a results, sort the b results, join a and b and discard 99% of the result set , and then complain about the performance of the database.
This entry made my life happier; maybe it is not "just me" - read the April, 22nd 2009 entries.
Grin.
By the way: java programmers: read up on ref_cursors...
Wednesday, April 22, 2009
"Special" Characters - part III
Now that you know how Oracle databases store characters, as seen in part I and part II, it may be time to get realistic. After all, no one wants to loose information in a dramatic way as seen in part II.
One thing I forgot earlier: the euro symbol...
Now, ignore the first five entries - these are from the previous demonstrations. The last one is what it is all about, that is the Euro symbol! But how did it get to be three byte anyway?
Same way the US7ASCII database was built, now a WE8ISO8859P1 database is created. I have chosen WE8ISO8859P1 as it is the default for DBCA on West-European Unix and Linux.
For one of the clients, I will use the corresponding MS Windows code settings; WE8MSWIN1252. This will simulate the average workstation in this part of the world - predominantly Microsoft Windows.
The other client will simulate a DBA, logging in to the machine itself, and using the environment settings which come with the installation: NLS_LANG=American_America.WE8ISO8859P1; export NLS_LANG. Start seeing dark clouds yet? I do...
I know why that works - do you?
HEY! What's that?
A dark cloud... Well, I cheated - something you cannot see from this output, is the fact I changed gnome-terminal to use ISO-8859-1, not UTF8. The last two inserts were done with ISO-8859-1. Now, that is a single byte character set, so the double bytes used by UTF8 (the first two entries) are no longer understood. Hence the "corrupt" characters when retrieving them. The ISO-8859-1 characters can be found here, and are correct from that standpoint. Remember, 0x0c6=198, 0x0df=223.
Let me insert a € symbol. This cannot be done while gnome-terminal is in ISO-8859-1 mode: ISO-8859-1 does not support the Euro symbol! So, switch to ISO-8859-15:
Switching back to ISO-8859-1, this is the result:
Nice, huh? When you lookup decimal 164 in the ISO-8859-1 reference, you will find it is defined as Currency sign. Pretty neat, I'd say to define the Euro symbol in the -15 extension on the code point for the currency sign.
But I also show things can go wrong. And it will get worse, when NLS settings come in!
Next and last part will be about NLS settings, as indicated above.
Not sure if and how your browser will display all these characters, here are two images:
One thing I forgot earlier: the euro symbol...
[frank@cs-frank03 ~]$ su - ora10gr2
Password:
[ora10gr2@cs-frank03 ~]$ . oraenv
ORACLE_SID = [ora10gr2] ? us7ascii
[ora10gr2@cs-frank03 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 22 10:04:56 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1261396 bytes
Variable Size 109052076 bytes
Database Buffers 176160768 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> conn scott
Enter password:
Connected.
SQL> insert into blah values('€');
1 row created.
SQL> select dump(a) from blah;
DUMP(A)
--------------------------------------------------------------------------------
Typ=1 Len=1: 113
Typ=1 Len=1: 99
Typ=1 Len=1: 63
Typ=1 Len=1: 63
Typ=1 Len=1: 63
Typ=1 Len=3: 226,130,172
6 rows selected.
Password:
[ora10gr2@cs-frank03 ~]$ . oraenv
ORACLE_SID = [ora10gr2] ? us7ascii
[ora10gr2@cs-frank03 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 22 10:04:56 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1261396 bytes
Variable Size 109052076 bytes
Database Buffers 176160768 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> conn scott
Enter password:
Connected.
SQL> insert into blah values('€');
1 row created.
SQL> select dump(a) from blah;
DUMP(A)
--------------------------------------------------------------------------------
Typ=1 Len=1: 113
Typ=1 Len=1: 99
Typ=1 Len=1: 63
Typ=1 Len=1: 63
Typ=1 Len=1: 63
Typ=1 Len=3: 226,130,172
6 rows selected.
Now, ignore the first five entries - these are from the previous demonstrations. The last one is what it is all about, that is the Euro symbol! But how did it get to be three byte anyway?
The mystery of a single character stored as three bytes in US7ASCII
It is not. A mystery, that is. It is no rocket science at all. Here's the explanation:- The database and session are using the same NLS settings. Because of that, no conversion is done whatsoever. This is an important item to remember.
- The terminal session supports UTF8
- The decimal UTF8 representation of the Euro symbol is '226.130.172' - take a look here, search for 'euro'.
Reality check
Enough playing around with US7ASCII. I do not think anyone in their right mind would build a US7ASCII database. Which makes you wonder why Oracle Corporation still uses it as the default? But let's move on.Same way the US7ASCII database was built, now a WE8ISO8859P1 database is created. I have chosen WE8ISO8859P1 as it is the default for DBCA on West-European Unix and Linux.
For one of the clients, I will use the corresponding MS Windows code settings; WE8MSWIN1252. This will simulate the average workstation in this part of the world - predominantly Microsoft Windows.
The other client will simulate a DBA, logging in to the machine itself, and using the environment settings which come with the installation: NLS_LANG=American_America.WE8ISO8859P1; export NLS_LANG. Start seeing dark clouds yet? I do...
SQL> insert into blah values ('Æ');
1 row created.
SQL> insert into blah values('ß');
1 row created.
SQL> select a, dump(a,16) from blah;
A DUMP(A,16)
-------------------- --------------------
Æ Typ=1 Len=2: c3,86
ß Typ=1 Len=2: c3,9f
1 row created.
SQL> insert into blah values('ß');
1 row created.
SQL> select a, dump(a,16) from blah;
A DUMP(A,16)
-------------------- --------------------
Æ Typ=1 Len=2: c3,86
ß Typ=1 Len=2: c3,9f
I know why that works - do you?
SQL> insert into blah values('Æ');
1 row created.
SQL> insert into blah values('ß');
1 row created.
SQL> select a, dump(a,16) from blah;
A DUMP(A,16)
-------------------- --------------------
Æ Typ=1 Len=2: c3,86
ß Typ=1 Len=2: c3,9f
Æ Typ=1 Len=1: c6
ß Typ=1 Len=1: df
1 row created.
SQL> insert into blah values('ß');
1 row created.
SQL> select a, dump(a,16) from blah;
A DUMP(A,16)
-------------------- --------------------
Æ Typ=1 Len=2: c3,86
ß Typ=1 Len=2: c3,9f
Æ Typ=1 Len=1: c6
ß Typ=1 Len=1: df
HEY! What's that?
A dark cloud... Well, I cheated - something you cannot see from this output, is the fact I changed gnome-terminal to use ISO-8859-1, not UTF8. The last two inserts were done with ISO-8859-1. Now, that is a single byte character set, so the double bytes used by UTF8 (the first two entries) are no longer understood. Hence the "corrupt" characters when retrieving them. The ISO-8859-1 characters can be found here, and are correct from that standpoint. Remember, 0x0c6=198, 0x0df=223.
The case of the invalid Euro
I have had comments, that I was not storing the correct characters. My response to that was "define correct" (which is of course similar to one of the standard answers for any Oracle question: "it depends" ;) ). If I can store and retrieve characters, it must be correct, isn't it?Let me insert a € symbol. This cannot be done while gnome-terminal is in ISO-8859-1 mode: ISO-8859-1 does not support the Euro symbol! So, switch to ISO-8859-15:
SQL> insert into blah values('€');
1 row created.
SQL> select a, dump(a) from blah;
A DUMP(A)
-------------------- --------------------
Æ Typ=1 Len=2: 195,134
ß Typ=1 Len=2: 195,159
Æ Typ=1 Len=1: 198
ß Typ=1 Len=1: 223
€ Typ=1 Len=1: 164
1 row created.
SQL> select a, dump(a) from blah;
A DUMP(A)
-------------------- --------------------
Æ Typ=1 Len=2: 195,134
ß Typ=1 Len=2: 195,159
Æ Typ=1 Len=1: 198
ß Typ=1 Len=1: 223
€ Typ=1 Len=1: 164
Switching back to ISO-8859-1, this is the result:
A DUMP(A)
-------------------- --------------------
Æ Typ=1 Len=2: 195,134
ß Typ=1 Len=2: 195,159
Æ Typ=1 Len=1: 198
ß Typ=1 Len=1: 223
¤ Typ=1 Len=1: 164
-------------------- --------------------
Æ Typ=1 Len=2: 195,134
ß Typ=1 Len=2: 195,159
Æ Typ=1 Len=1: 198
ß Typ=1 Len=1: 223
¤ Typ=1 Len=1: 164
Nice, huh? When you lookup decimal 164 in the ISO-8859-1 reference, you will find it is defined as Currency sign. Pretty neat, I'd say to define the Euro symbol in the -15 extension on the code point for the currency sign.
But I also show things can go wrong. And it will get worse, when NLS settings come in!
Wrap up
So far, I have not even begun to fiddle around with NLS settings. All I did was change the capabilities of the front end. And I did not even touch web interfaces by far (which should of course use the correct HTML escaped code for a €: €)Next and last part will be about NLS settings, as indicated above.
Not sure if and how your browser will display all these characters, here are two images:
Subscribe to:
Posts (Atom)
