README

This readme describes how to create Java a stored procedure that uses the Intenz display class "SpecialCharacters". The readme is part of a .zip file which includes sources/jars/classes mentioned below.

Author: markr@ebi.ac.uk pmatos@ebi.ac.uk

HOW TO set it up

• Have the DBAs create an Oracle account in a database 10G database to own the Java objects. The oracle account needs development and loadjava privs.

  Note: as this is in a 10G database, make sure you set your EBI Oracle working environment to 10G. (on the cluster thats using oracle home /sw/arch/dbtools/oracle/product/10.1.0.2)

  NB: Currently the load java utility and dropjava only works on 32 bit so you can't use 64 bit. You need to log into dodo and setup your script as follows:

  setenv ORACLE_BASE /sw/arch/dbtools/oracle

  setenv ORACLE_HOME /sw/arch/dbtools/oracle/product/10.1.0.2

  setenv CLASSPATH $ORACLE_HOME/JRE/lib:$ORACLE_HOME/jlib:$ORACLE_HOME/jdbc/lib/classes111.zip

  setenv ORACLE_TERM vt100

  setenv TNS_ADMIN $ORACLE_HOME/network/admin

  setenv ORACLE_PATH $ORACLE_HOME/bin

  setenv ORA_NLS32 $ORACLE_HOME/ocommon/nls/admin/data

  setenv NLS_LANG american_america.us7ascii

  setenv NLS_DATE_FORMAT "DD-MON-RRRR HH24:MI:SS"

  setenv LD_LIBRARY_PATH $ORACLE_HOME/lib

  set path = ( $ORACLE_PATH $path )

• Create an xchars.jar file, but do not use Java 1.5 to compile the classes. Use instead for example usr/java/j2sdk1.4.1_02/bin/java. Also do not include packages in xchars.jar that you dont need, such as dependencies on log4j. Also do not use java.util.logging as this causes a static initialisation error and causes the SpecialCharacters class not to load.

  The xchars.jar used is included in the zip file.

• Load xchars.jar into the schema of the Oracle account

  For example: loadjava -resolve -user chebi_dev10/XXXXX@dev10 xchars.jar

  To drop a class then: dropjava -resolve -user chebi_dev10/XXXXX@dev10 uk/ac/ebi/xchars/domain/CharacterClass.class

• Compile and load the class that will be wrapped as a stored procedure into the database. See zip file for Java source.

  For example: loadjava -resolve -user chebi_dev10/XXXXX@dev10 xchars-database-1.0.jar

• Log in to the database and check that the loaded Java classes are valid ;

  For example:

  >> select object_type, status, count(*) from user_objects group by object_type, status

  OBJECT_TYPE STATUS COUNT(*)

  LOB VALID 1

  INDEX VALID 1

  TABLE VALID 1

  JAVA CLASS VALID 29

  JAVA RESOURCE VALID 1

• Create the wrapper function in the database. See zip file for PL/SQL source.
• Have the DBA grant read permissions on the XML and DTD files on the network to your Oracle account

  Example:

  begin

  dbms_java.grant_permission( 'CHEBI_DEV10', 'SYS:java.io.FilePermission', '/ebi/sp/pro1/intenz/special_characters' , 'read' );

  dbms_java.grant_permission( 'CHEBI_DEV10', 'SYS:java.io.FilePermission', '/ebi/sp/pro1/intenz/special_characters/specialCharacters.dtd' , 'read' );

  dbms_java.grant_permission( 'CHEBI_DEV10', 'SYS:java.io.FilePermission', '/ebi/sp/pro1/intenz/special_characters/specialCharactersMapping.dtd' , 'read' );

  dbms_java.grant_permission( 'CHEBI_DEV10', 'SYS:java.io.FilePermission', '/ebi/sp/pro1/intenz/special_characters/specialCharacters.xml' , 'read' );

  commit;

  end;

• Make sure that user 'oracle' (the one who runs the database) can see this directory (above /ebi/sp/pro1/intenz/special_characters) from the db server. The DBAs can verify this. Systems need to mount it if necessary.
• Test the function, in SQL*PLUS:

  SET SERVEROUTPUT ON

  CALL dbms_java.set_output(2000);

  commit;

  >> select xml_display_sp_code ('/ebi/sp/pro1/intenz/special_characters/', 'specialCharacters.xml', 'greekalpha/greek-ribsome') from dual;

  XML_DISPLAY('/EBI/SP/PRO1/INTENZ/SPECIAL_CHARACTERS/','SPECIALCHARACTERS.XML','GREEKALPHA/GREEK-RIBSOME') ----------------------------------------------------------------------------------------------------------------------------------- alpha-ribsome

• Optionally, create a view over a database link to use this function This can be created by the DBA's you can then query the function via a method call: select xml_display_chebi_code@DEV_DEV10.EBI.AC.UK('/ebi/sp/pro1/intenz/special_characters/', 'specialCharacters.xml', 'greek>alpha/greek-ribsome') from dual;

  Where DEV_DEV10.EBI.AC.UK is the name of the database link.

  To view all database links on a schema:

  select * from all_db_links;