Friday, December 18, 2009

APEX Notes

If you want to access a procedure without going through the APEX security, alter wwv_flow_epg_include_mod_local to include your procedure.
I had a simple procedure, that lists all applications, called apex_apps:

CREATE OR REPLACE PROCEDURE apex_030200.apex_apps
AS
BEGIN
FOR i IN (SELECT TRIM(TO_CHAR (display_id)) ID, NAME
FROM apex_030200.wwv_flows
WHERE display_id NOT BETWEEN 4000 AND 5000
ORDER BY NAME)
LOOP
HTP.p ('<li><a href="f?p=' || i.ID || '">' || i.NAME || '</a></li>');
END LOOP;
END;


However, calling this resulted in:
[Fri Dec 18 15:57:12 2009] [error] [client x.x.x.x.] 
mod_plsql: /apex/apex_apps HTTP-403
It is forbidden to call this procedure directly from the browser!

After changing wwv_flow_epg_include_mod_local to

CREATE OR REPLACE function APEX_030200.wwv_flow_epg_include_mod_local(
procedure_name in varchar2)
return boolean
is
begin
-- return false; -- remove this statement when you modify this function
--
-- Administrator note: the procedure_name input parameter may be in the format:
--
-- procedure
-- schema.procedure
-- package.procedure
-- schema.package.procedure
--
-- If the expected input parameter is a procedure name only, the IN list code shown below
-- can be modified to itemize the expected procedure names. Otherwise you must parse the
-- procedure_name parameter and replace the simple code below with code that will evaluate
-- all of the cases listed above.
--
if upper(procedure_name) in (
'APEX_APPS') then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

everything works as designed.

Monday, December 14, 2009

My first program

Entry # 100 - celebration? Anyway, ran into probably my first program ever. Machine code, for an MC6800 (6802, actually). Dates back to 1978 or 1979:

inadd: CLRB ; clear count
CLR,X
PUSH B ; count on stack
CLR 1,X
inadd3: BSR INHEX
BMI inadd1
LDB#4
inadd2: ASL 1,X
ROL,X
DEC B
BNE inadd2
PSHS A
ORA 1,X
STA 1,X
PULS A
INC,B ; count
LDB,S
CMPB#S
BNE inadd3
inadd1: LDB,S+ ; b=count0 + #hex counts
RTS

INHEX = INCHNP

It's a counter, that should be able to count over 256, if I recall correctly. Quite complicated when you only have 8 bits. The code may not even be correct, as pencil tends to fade, and recycled paper becomes yellowish brown. Not much contrast there.
Addition: I could try it of course, as I still own a Motorola MEK6800D2 kit...

Friday, November 13, 2009

Friday, the 13th.

Could someone persuade Oracle to reinstate the good old Metalink, please?!
Yesterday, I got just "An error has occurred. Please try again later" the whole day. And I was not the only one.
Today it worked for about an hour for me (between 09:00 and 10:00 GMT+1), then it started with http-501 errors, and now this:


Update: My favourites are gone! Years of careful reading, searching and safekeeping: gone! According to the Well Known Problems I should NOT have logged on before Nov, 9th, because the updates were pending. If you did, you favourites are regarded as up-to-date, and will not be migrated. According to metaklunk2, I should have logged on. Shouldn't. Should. Sigh.

So it's like my own fault?!? Right. Don't think so. Unhappy customer here, Oracle.

Go, google, go!

Go - a new programming language. Let's hope it will not be the same disaster as Java became. Google should be large enough to withstand Microsoft. Are we waiting for Yet Another Programming Language? Only the future will tell. I see annoying things like 'garbage collection', which leads me to believe it will be just a "lazy" a language as Java.
And the supposedly simple printf is beyond comprehension. Print would be simple. Printf has too much letters.

Tuesday, November 10, 2009

Administration in Run-Time Only APEX

Simple, but you have to know...
connect system
alter session set current_schema = FLOWS_030100;

begin
apex_instance_admin.set_parameter('PRINT_BIB_LICENSED','ADVANCED');
apex_instance_admin.set_parameter('PRINT_SVR_HOST','tobi01.home.local');
apex_instance_admin.set_parameter('PRINT_SVR_PORT','8123');
apex_instance_admin.set_parameter('PRINT_SVR_SCRIPT','/xmlpserver/convert');
apex_instance_admin.set_parameter('PRINT_SVR_PROTOCOL','http');
end;
/

The doc states you have to have a session as sysdba, but system works for this version, too.

Friday, October 23, 2009

ORA-24062 ( Subscriber table inconsistent with queue table)

Surprisingly little search results in either google, the oracle fora, or metalink for this error. Ran into this today, and could drop an recreate the queues:
SQL> BEGIN
2 SYS.DBMS_AQADM.STOP_QUEUE(QUEUE_NAME =>'TAB_OWNER.TB_BTR_Q_OPDR');
3 SYS.DBMS_AQADM.DROP_QUEUE(QUEUE_NAME =>'TAB_OWNER.TB_BTR_Q_OPDR');
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-24062: Subscriber table TAB_OWNER.AQ$_TB_BTR_Q_TBL_S inconsistent with
queue table TAB_OWNER.TB_BTR_Q_TBL
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4932
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 2

The solution is drastic: for each error, reporting the table, force a drop of that table:
SQL> exec SYS.DBMS_AQADM.DROP_QUEUE_TABLE (
QUEUE_table => 'TAB_OWNER.TB_BTR_Q_TBL',
force=>TRUE);

PL/SQL procedure successfully completed.

After that, recreate the queues and queue tables.

Friday, September 11, 2009

HP-UX: UnsatisfiedLinkError exception loading native library: njni10

Ran into this today whilst applying patch 10.1.4.3 on Identity Management installs. Causes the Directory Intergration Platform (DIP) upgrade to fail. As there are some nice features, quite important. I want that to run, and run correctly!

Three things to check/do:

export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SHLIB_PATH=$ORACLE_HOME/lib32

Then you can rerun the configuration wizards again, assuring you have enough resources - use the setmemwindow:

setmemwindow -i 20 $ORACLE_HOME/cfgtoollogs/configToolCommands


So not just only when running DBCA, or NETCA, or DBUA; there it was an OID install (the infrastucture).
Edit: maybe check the existance of the files first? Search libnjni10.so; should be in lib as well as lib32, but different sizes.

Thursday, September 10, 2009

Upgrade IDM from 10.1.2 to 10.1.4: dba_registry invalid OID

After you upgrade Oracle identity management from 10.1.2(.2) to 10.1.4.0, you will notice that Oracle Internet Directory is listed as Invalid:


idm01@xxxxxxx:/home/idm01> sqlplus / as sysdba
SQL> set lines 132 pages 99
SQL> col version format a10
SQL> col status format a15
SQL> col comp_name for a65
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME VERSION STATUS
------------------------------------------------------- ---------- ---------------
Oracle Application Server Portal 10.1.2.0.2 VALID
Oracle Application Server Single Sign-On 10.1.4.0.1 VALID
.
Oracle Internet Directory 10.1.2.0.2 INVALID



The solution is to:
Check $ORACLE_HOME/ldap/admin/LOGS/ldapupgrade.log in the OID (infrastructure) install for errors.
Check for invalid objects and compile them:

select owner,object_name,object_type from dba_objects
where owner='ODS' and status='INVALID';

returned package ridelplg as invalid.

Finally, run this code:

alter session set current_schema = SYS;

declare
rc integer;
begin
rc := ods.ldapUpgUtls.validateODS();
if rc = 0 then
DBMS_IAS_VERSION.SET_COMPONENT_VALID(COMPONENT_ID=>'OID');
else
DBMS_IAS_VERSION.SET_COMPONENT_INVALID(COMPONENT_ID=>'OID');
end if;
end;
/



According to Metalink Doc ID: 374304.1, you should first call

execute DBMS_IAS_VERSION.SET_COMPONENT_UPGRADED(COMPONENT_ID=>'OID');

This, however, reset your version to 10.1.2.0.2 (whereas it was 10.1.2.2.0 when I started...). It should be 10.1.4.0.1.
Edit: After applying patch 10.1.4.3, it states version as 10.1.4.3.0.

Thursday, September 03, 2009

Actieve DonorRegistratiesysteem

Ik heb zojuist mijn steun gegeven aan de campagne 2 Miljoen Handtekeningen. Het doel van deze campagne is de invoer van het Actieve DonorRegistratiesysteem (ADR).

Bij deze zou ik jou willen vragen om de petitie ook te ondertekenen op de site www.2miljoenhandtekeningen.nl
Mocht je eerst meer willen weten over de campagne of het ADR dan kun je al die informatie ook op de site vinden.

Daarnaast het verzoek mail te sturen naar al jouw vrienden om de campagne bij een groot publiek onder de aandacht te brengen.
Namens mij en de Stichting 2 Miljoen Handtekeningen, alvast hartelijk bedankt!

Tuesday, July 14, 2009

Oracle 10, Apex 3, PDF printing and ORA-20001

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

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.

GUI

Using the Graphical User Interface, or GUI, it would have been easy:
  1. On the Workspace home page, click the Application Builder icon.
  2. Select an application.
  3. On the Application home page, click Shared Components.
  4. Under Application, select Definition.
  5. 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?

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:
  • 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
as well as linking errors in ins_rdbms.mk.

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
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?!?
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 €!

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.

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...

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...
[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.

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'.
And that is exactly what is stored, and can be retrieved, and displayed as euro symbol. But only because the program I use (gnome-terminal) supports UTF8. Any other program, not understanding UTF8 will display something else!

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

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

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

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

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 €: &euro;)

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:


Sunday, April 19, 2009

"Special" Characters - part II

One of the prerequisites to do what I demonstrated in the previous entry is that you must use tools that allow the display of special characters.
As logical as it seems, you can only display diacritics or a Euro sign when the tool actually knows how to display those. If in the end, your tooling cannot, you will see some strange things.




This screenshot is not as strange as it looks. The terminal session actually allows for various character encodings; it is UTF-8 by default. That is why the characters are actually displayed in the first select statement. The second one uses Western Windows 1252, the third Western ISO-8859-1.
I will now add the € symbol, using ISO-8859-15:

The fact, that is does not display in the second select statement, is that the terminal session switched to UTF8.
Now, lets have a look how Oracle treats all this:
SQL> select dump(a) from blah;

DUMP(A)
--------------------------------------------------------------------------------
Typ=1 Len=1: 113
Typ=1 Len=2: 195,167
Typ=1 Len=1: 231
Typ=1 Len=11: 195,134,32,195,166,32,197,146,32,197,147
Typ=1 Len=14: 195,133,32,195,152,32,195,159,32,195,145,32,195,177
Typ=1 Len=1: 164

First line, standard lower case "q": value is 0x071, or decimal 113. Now how in the world does the value one-hundred-and-thirteen get translated into some flagpole with a circle attached, many people will recognize as the character "q"?

Code Points and Character Sets


That is all about "code points". There's a standing agreement that in computer environments, decimal 113 gets represented on screen as the letter q. There's even a standard, called ASCII, which is short for American Standard Code for Information Interchange.
Of course, not all the world uses this standard, so it is possible, that the value 113 gets represented by another sign in another environment. Which is true for new characters, like the euro sign. After all, that did not exist 30 years ago.
But even in the "good old days" it was possible to represent the symbol "q" with something else, when you used the Extended Binary Coded Decimal Interchange Code, or EBCDIC (pronounced as 'epsedick'), for example. In EBCDIC, "q" would be 0x098, or 152 decimal.
The second line looks odd: 195,167 (0x0C3, 0x0A7) for a c-cedilla. Let's not forget the terminal session uses UTF8 character encoding by default. And that is what I used to insert all these characters in the first place.
UTF8 is yet another standard by which signs on screens are connected to (computer) code. If you want to know more about ASCII (particular US7ASCII), ISO and UTF character encoding, internet search machines are your friend. Basically, the ASCII coding schema only used 7 bits, allowing only for 2^7 code points, or 128 characters. The 8th bit was originally used for parity checking. As the first 32 codes were used as communication control characters, that did not allow for much more than the alphabet in upper and lower case (2*26 = 52 code points), numbers (0-9 = 10 additional code points) and some other characters, found on every typewriter (yet another 32 code points). In total, 126 code points; add a space, and all your bits are used.
The PC industry skipped the parity check, and extended the character set with another 127 characters. This character set became actually known by that name: the Extended ASCII character set (or US8ASCII, as opposed to the original US7ASCII). As every manufacturer had it's own likings, about every PC manufacturer had it's own coding points; some used it for primitive graphics, others for locale specific adaptations.
ISO standards defines 2^8 character points, and sub-standards exist for about every locale: ISO 8859-1 is known as ISO Latin1, or Western European, whereas ISO 8859-5 is known as the Cyrillic character set, ISO 8859-8 the Celtic, etc, etc..

What actually is stored...


are code points. Not characters!
Now, how does the c-cedilla get translated to 0x0C3, 0x0A7? Simple: I entered a c-cedilla in a terminal session, that supported UTF-8. In UTF-8, the c-cedilla has a code point "c3 a7", or U+00E7. You can check the rest of the output with a conversion table, an example is here. Another fine example with the full 1-byte UTF8 code table is here.
Now, the SQL*Plus session uses US7ASCII (I specified that by setting NLS_LANG). The database uses US7ASCII. Conclusion: Oracle needs to convert nothing! It gets US7ASCII from the session, and it stores US7ASCII - done!
Now - the last entry, the euro sign (164, or 0x0A4), was done whilst using ISO 8859-15 as terminal setting. It comes back in UTF8 as a question mark. Even though U+00A4 is defined as currency symbol, UTF8 does not understand the single byte.

Where Oracle comes in.

OK - I now changed the environment variable NLS_LANG to American_America.UTF8. The terminal supports UTF8, too. Now take a look at this:

SQL> truncate table blah;
Table truncated.

SQL> insert into blah values('q');
1 row created.

SQL> insert into blah values('ç');
1 row created.

SQL> select * from blah;
A
------------------------------------------------------------
q
c

SQL> select dump(a) from blah;
DUMP(A)
--------------------------------------------------------------------------------
Typ=1 Len=1: 113
Typ=1 Len=1: 99

Watch that last entry! It reads the character 'c', not 'ç' ! The whole point here is that now the network layer actually performs a conversion - it gets UTF8, but has to map that into US7ASCII. US7ASCCI does not know the code point "ç", and maps it to "c". Similar for the euro symbol:
SQL> insert into blah values('€');
1 row created.

SQL> select a,dump(a) from blah;
A DUMP(A)
---------- ------------------------------
q Typ=1 Len=1: 113
c Typ=1 Len=1: 99
? Typ=1 Len=1: 63

Ah - there it is! The infamous question mark! And the euro symbol has become a decimal 63 (0x03F).

Mind you - now you have actually lost data! There is no way of knowing that 0x063 was originally the character 'c' or not. Same for the euro symbol!

Conclusion

  • Be careful with "special" characters - you may actually loose data!
  • You do not store characters in the database - you store code points. The actual character displayed depends on the character set in use.
  • Make sure all end user components are capable of displaying "special" characters. If one does show the correct character, and the other doesn't, the latter uses a wrong character set. Choose a different one, that matches the code point to the correct character (just as the other tool does).

Wednesday, April 15, 2009

"Special" Characters - part I

Times and times again, people complain about "not being able to store special characters". Whatever special means - in this case diacriticals, like à é or ç, or currency symbols like the Yen or Euro sign: ¥ and €.
Educated guess... Sort of.
I even had an Oracle Master explaining that "the database had the wrong character set" because a web based application did not display the euro sign correctly. My 'polite' comment was "Humbug! A database stores whatever you throw at it". Sorry about that.
The database in question was 10.2.0.4, using WE8ISO8859P1 (not P15!)

So, what have we got:
- One workstation, running Linux (CentOS) 2.6.18-92.1.18.el5PAE #1 SMP
- One Oracle 10G Release 2 for Linux, pathed to 10.2.0.3 (p5337014).
- Some scripts, generated by dbca
- NLS_LANG set to American_America.US7ASCII, ORACLE_BASE and ORACLE_HOME set

This is the crux:

CREATE DATABASE "us7ascii"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oracle/data/us7ascii/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/data/us7ascii/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/data/us7ascii/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle/data/us7ascii/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oracle/data/us7ascii/redo01.log') SIZE 51200K,
GROUP 2 ('/oracle/data/us7ascii/redo02.log') SIZE 51200K,
GROUP 3 ('/oracle/data/us7ascii/redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";


Ok - database was made, and it cannot store any special characters by default, as the standard character set is 7-bits. Or can it?!?


SQL> create user scott identified by tiger default tablespace users;
User created.

SQL> grant connect, resource to scott;
Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> create table blah (a varchar2(20));
Table created.

SQL> insert into blah values ('q');
1 row created.

SQL> insert into blah values('ç');
1 row created.

SQL> select * from blah;

A
--------------------
q
ç

Oops... Can it get any worse? It can, actually:

SQL> select * from blah;

A
--------------------
q
ç

Æ æ Œ œ
Å Ø ß Ñ ñ


I guess I just demonstrated you actually can store "special" characters in a US7ASCII based Oracle database. So can we now stop the discussions?

A database (well, at least an Oracle database) stores whatever you throw at it. That is what databases are supposed to do.
Edit: of course, this is linked to the TOP-25...
more specifically, point #1 I made: "Improper encoding"

Friday, February 13, 2009

APEX V3.1.2 Runtime Adminitration

Currently working on a third party developed application, that does user management from within the application. Nothing special, you'd say.
Nope, it is not. But for the part, where a new user needs to get privileges to add other users... The query used to check whether a user can administer application users is a join over the view FLOWS_030100.APEX_WORKSPACE_APEX_USERS, specifically the column IS_ADMIN. Now, promoting an APEX application user to administrator is simple in the developer environment. In a runtime only environment, it is a little bit harder.

In fact, I can install new versions of the application by logging on to the database with SQL*Plus as workspace owner, and run the script. One of the first things this script does is:

begin wwv_flow_api.set_security_group_id(
p_security_group_id => APEX_UTIL.FIND_SECURITY_GROUP_ID('NAME'));
end;
/

After that, it continues with the creation of the flow (after the flow was dropped):

wwv_flow_api.create_flow(
p_id => 100,
p_display_id=> 100,
p_owner => 'OWNER',
p_name => 'NAME',

... etc...

However, owner corresponds with an Oracle database account, and there's a policy to have different application accounts from the database accounts. So in the workspace import (wwv_flow_fnd_user_api.create_company), there's a difference between Workspace schema (p_company_schemas) and provisioning schema (p_first_schema_provisioned) on one side, and the Workspace Administrator (wwv_flow_fnd_user_api.create_fnd_user) on the other.

As stated: I can log on to the database as owner (Oracle account) and install the application. However, what I cannot do is:

begin wwv_flow_api.set_security_group_id(
p_security_group_id => APEX_UTIL.FIND_SECURITY_GROUP_ID('NAME'));
end;
/
begin
apex_util.edit_user(
p_user_id => apex_util.get_user_id('APP_ADMIN'),
p_user_name => 'APP_ADMIN',
p_developer_privs => 'ADMIN:');
end;
/

It simply results in:

ERROR at line 1:
ORA-20001: User requires ADMIN privilege to perform this operation.
ORA-06512: at "FLOWS_030100.WWV_FLOW_FND_USER_API", line 76
ORA-06512: at "FLOWS_030100.WWV_FLOW_FND_USER_API", line 1192
ORA-06512: at "FLOWS_030100.HTMLDB_UTIL", line 756
ORA-06512: at line 2

The workaround is to execute this code as SYS (SYSTEM should work, too).

I suspect is is to do with the fact, the Workspace Administrator name differs from the owner of the application. Or, in technical terms, FLOWS_030100.APEX_WORKSPACE_APEX_USERS.USER_NAME does not correspond with the pseudo column USER.

Thursday, January 15, 2009

Top-25

A little late, this top-25, but noteworthy, non the less. The top-25 of programming errors. What about Improper Encoding or Escaping of Output, or SQL Injection (the CWE organization calls it 'Failure to Preserve SQL Query Structure')

Others, I like:
Improper Initialization
Client-Side Enforcement of Server-Side Security
Hard-Coded Password
and, one I blogged about years ago: Use of a Broken or Risky Cryptographic Algorithm

Must read: the SQL Injection cheat sheet