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');
DBMS_XMLSave.SetIgnoreCase(insCtx,1);
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.
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
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.
begin
dbms_java.grant_permission('SYSTEM',
'java.io.FilePermission',
'C:\Temp\BACKUP',
'read,write');
commit;
end;
/
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))
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.
declare
arr util.vc_tab;
begin
if arr.last is null then
dbms_output.put_line('Nul!');
else
dbms_output.put_line(arr.last);
end if;
end;
/