Tuesday, March 14, 2006

Some gotchas with Oracle XE

Oracle recently announced production/general availability of Oracle Express Edition (XE). Generally a very exciting development: a totally free, almost fully-functioned version of Oracle Database 10g that downloads and installs in minutes (well, assuming you've got broadband!).

I ran into a couple of glitches, however, that I thought I would share:

1. UTL_FILE is not available. Usually, when you install Oracle, the UTL_FILE package (used to read/write files within PL/SQL) is installed, EXECUTE is granted to PUBLIC, and a public synonym is created. With XE, the package is installed and the synonym created, but the GRANT EXECUTE has not been run.

To fix this problem, connect to a SYSDBA account and run the $ORACLE_HOME/RDBMS/Admin/utlfile.sql file, or simply execute this command (from a SYSDBA account):

GRANT EXECUTE on SYS.UTL_FILE TO PUBLIC
/

2. Oracle XE does not include a Java Runtime Environment. I have been using some Java classes, installed in the database, in my unit testing product, Qute. Here is an example of a call to this Java code:

PROCEDURE parse_package (
owner IN VARCHAR2
, package_name IN VARCHAR2
, program_name IN VARCHAR2
)
AS
LANGUAGE JAVA
name 'quPlSqlHdrParser.parsePackage(
java.lang.String, java.lang.String, java.lang.String)';


In pre-production versions of Oracle XE, the code compiled, but then raised runtime errors, sometimes ORA-00600, which are not trap-able with an exception section.

The production version still does not include a JRE and even worse, if you install the Oracle Database 10g Express Edition (Western European; " Oracle Database 10g Express (Western European) Edition - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only.") then the above code will not even compile. Very strange. Oracle seems to actually be looking at the literal string and attempting to validate it at compile time.

Yet if I install the
Oracle Database 10g Express Edition (Universal; Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish), then I do not get a compilation error.







5 comments:

SydOracle said...

My production version of XE (Western European) had the UTL_FILE public synonym, but not the grants. Its safer that way, so I wonder if the next major Oracle release will also omit those grants.
With regards to Java, I'd prefer that it it isn't going to run, it doesn't compile (but a clearer 'Java not supported in this edition' message would be good).

Geno said...

Well done, your copy&paste works well. See http://www.oracleplsqlprogramming.com/oppnews_2006_03.html - Glitches with Oracle Express Edition

Either that, or they copied you, or you both copied from somewhere else.

This is bloating the blogosphere.

Janick said...

About the copy paste, look at the homepage http://www.oracleplsqlprogramming.com/index.html

See "About Steven Feuerstein" ;-)

Lelo said...

Thansk a lot for your post, very useful.

Lelo

Lance said...

Strange that after doing the above steps (grant adjusted for 11gXE Win32bit based on whats on the end of the UTLFILE.sql file) that I still can't get my test user to execute UTL_File. Executing the utlfile fixed it so System user can. Without this permission, many of the utlpSql 2.3.0 procs will not compile and so I couldn't use a non-system user to run my unit tests until I performed the below.

What I ended up doing is (zork is my user when in production):
grant execute on system.utl_file to public;
grant execute on system.utl_file to zork;
grant execute on utl_file to zork;

After doing the above, I could uninstall then install utplsql without compilation errors. I suspect I only needed to grant to my user explicitly. For some reason the Public alone didn't work, or maybe it needed to reference beyond the synonym. I'm unsure as I'm a rookie Oracle user. I'm sure another poster will refine the above process.