Did You Know? -- November 2001

By Hervé Deschamps, Oracle Corporation.



There are tools out there to convert XML files to flat files and conversely. One of them is available from www.unidex.com. These are useful if you want to avoid a big bang approach to migrating system interfaces. You can have a system outputing XML without having all downstream applications' interfaces modified. You "protect" the downstream application by using XSL to transfer the XML file into the good old flat file structure expected by the downstream application.

When you use XSU (XML SQL Utility) to load XML data you may encounter this kind of error message:

ORA-29532: Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: Exception ' The XML element tag 'RecordType' does not match the name of any of the columns/attributes of the target database object.' encountered during processing ROW element 0 All prior XML row changes were rolled back. in the XML document.
ORA-06512: at "SYS.DBMS_XMLSAVE", line 91

This could be caused by case mismatch. By default XSU is case sensitive and if you XML file has mixed case tags, as the column names are uppercase in the database, XSU is not happy. So to make it case insensitive here is the API call you need to make (undocumented)

      insCtx := DBMS_XMLSave.newContext('SCOTT.EMP');
      rows := DBMS_XMLSave.insertXML(insCtx,Doc);

XSU also does not like this tag:
<?xml-version='1.0'?>. The only way I found to load my data was to remove this tag altogether.

Oracle Packages

Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (INIT.ORA).

On UNIX systems, a file created by the FOPEN function has as its owner the owner of the shadow process running the instance. In the normal case, this owner is oracle. Files created using FOPEN are always writable and readable using the UTL_FILE subprograms, but non-privileged users who need to read these files outside of PL/SQL might need their system administrator to give them access.

Procedure dbms_utility.compile_schema(schema varchar2, compile_all boolean default TRUE);
  --  Compile all procedures, functions, packages and triggers in the specified
  --  schema.  After calling this procedure you should select from view
  --  ALL_OBJECTS for items with status of 'INVALID' to see if all objects
  --  were successfully compiled.  You may use the command "SHOW ERRORS
  --  <type> <schema>.<name>" to see the errors associated with 'INVALID'
  --  objects.

PROCEDURE comma_to_table( list   IN  VARCHAR2,
                         tablen OUT BINARY_INTEGER,
                         tab    OUT uncl_array);
--  Convert a comma-separated list of names into a PL/SQL table of names
--  This uses name_tokenize to figure out what are names and what are commas

PROCEDURE table_to_comma( tab    IN  uncl_array,
                         tablen OUT BINARY_INTEGER,
                         list   OUT VARCHAR2);
--  Convert a PL/SQL table of names into a comma-separated list of names

Apps 11i Concurrent Manager

When you do not supply default values for the program parameters, the concurrent manager does not use the ones defined on the called pl/sql program unit. Instead it passes a null.

If the concurrent manager does not produce a log file it may be because you have not defined the directories log and out in the directory structure corresponding to the application that owns the program that you are running.

Java IO permissions on 8.1.7.

In order to read or write in the file system of the machine that runs your oracle database, using java, you need to grant the permission to the user:


You do not need to include the directories in init.ora as we do for utl_file.

If you forget to grant the java permission you will get a message like this:

ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException:
the Permission (java.io.FilePermission C:\Temp\BACKUP read) has not been granted by
dbms_java.grant_permission to SchemaProtectionDomain(SYSTEM|PolicyTableProxy(SYSTEM))

Checking a Java Installation

If you want to know if Java was installed on a Oracle Database correctly, try this:

     SELECT count(*)
     FROM dba_objects
     WHERE object_type LIKE '%JAVA%';

If the above query returns a number < 4000, the installation was not successful.

The query below checks for any invalid Java classes using a Java stored procedure:

     SELECT dbms_java.longname(name)
     FROM sys.obj$
     WHERE type# = 29 and status != 1;

If the above query fails or returns any rows, then something is wrong with the JVM installation.

PL/SQL Tables

How can you test if a PL/SQL table is null? (Just initialized)

   arr util.vc_tab;
   if arr.last is null then
   end if;

APPS Reports

Find the user exits in Apps' reports quickly:
   Do a search for srw.user_exit  (program --> Find and Replace in PL/SQL)

Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others.  He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at herve.deschamps@.oracle.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.