smena
asked on
XML FILE LOAD INTO ORACLE
I'm using the following example for xml file load into the ORacle table:
----code starts
CREATE OR REPLACE PROCEDURE loadxml AS
fil BFILE;
buffer RAW(32767);
len INTEGER;
insrow INTEGER;
BEGIN
SELECT f_lob INTO fil FROM xml_temp WHERE key = 1;
DBMS_LOB.FILEOPEN(fil,DBMS
len := DBMS_LOB.GETLENGTH(fil);
DBMS_LOB.READ(fil,len,1,bu
xmlgen.resetOptions;
insrow := xmlgen.insertXML('xml_doc'
DBMS_OUTPUT.PUT_LINE(insro
IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('In Exception');
DBMS_OUTPUT.PUT_LINE(SQLER
IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);
END IF;
end;
--- script ends
When I try to compile error
PLS-00201: identifier 'XMLGEN.RESETOPTIONS' must be declared appeared for
line xmlgen.resetOptions;
Any advise?
----code starts
CREATE OR REPLACE PROCEDURE loadxml AS
fil BFILE;
buffer RAW(32767);
len INTEGER;
insrow INTEGER;
BEGIN
SELECT f_lob INTO fil FROM xml_temp WHERE key = 1;
DBMS_LOB.FILEOPEN(fil,DBMS
len := DBMS_LOB.GETLENGTH(fil);
DBMS_LOB.READ(fil,len,1,bu
xmlgen.resetOptions;
insrow := xmlgen.insertXML('xml_doc'
DBMS_OUTPUT.PUT_LINE(insro
IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('In Exception');
DBMS_OUTPUT.PUT_LINE(SQLER
IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);
END IF;
end;
--- script ends
When I try to compile error
PLS-00201: identifier 'XMLGEN.RESETOPTIONS' must be declared appeared for
line xmlgen.resetOptions;
Any advise?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Are there any way to use "core" PL_SQL functional without using XDK?
I have a very simple XML file format that just needs to be loaded into one oracle table....
I have a very simple XML file format that just needs to be loaded into one oracle table....
XMLGEN is a package and you have to ensure the XDK has been installed for JAVA & PL/SQL correctly. This shows how to install in different Oracle version:
DETAILED STEPS
---------------
--------------------------
How to Install the Original 8.1.7 XDK on any 8.1.7 Database Version
--------------------------
These steps apply if the initxml.sql script has never been run:
1. Log into SQL*Plus as SYS and run the following script:
For both UNIX and Windows:
SQL> [ORACLE_HOME]/oracore/admi
2. Run the following commands to create synonyms for PL/SQL packages that were
not created by the above script:
--drop public synonym xmldom;
create public synonym xmldom for xmldom;
--drop public synonym xmlparser;
create public synonym xmlparser for xmlparser;
--drop public synonym xslprocessor;
create public synonym xslprocessor for xslprocessor;
3. Execute the following script:
For both UNIX and Windows:
SQL> [ORACLE_HOME]/rdbms/admin/
--------------------------
--------------------------
How to Install the Original 9.0.1 XDK on a 9.0.1 Database Version
--------------------------
This procedure only applies if the initxml.sql script has never been run.
1. Before running initxml.sql, change following line in
$ORACLE_HOME/xdk/admin/xml
--drop public synonym xmlprocessor;
create public synonym xmlprocessor for xmlprocessor;
to
--drop public synonym xslprocessor;
create public synonym xslprocessor for xslprocessor;
Please refer to [BUG:2060389] for further details.
2. Logon into SQL*Plus as SYS and run the following script:
For both UNIX and Windows:
SQL> [ORACLE_HOME]/rdbms/admin/
Notes:
If you encounter the error "ORA-00972: identifier is too long"
while running the XML installation script, please refer to [BUG:2125960].
In Oracle 9.0.1, the initxml.sql script will install XML SQL Utility (XSU).
--------------------------
--------------------------
How to Install the Original 9.2 XML parser and XSU on any 9.2 Database
--------------------------
This procedure only applies if the initxml.sql script has never been run.
Logon into SQL*Plus as SYS and run the following script:
For both Unix and Windows:
SQL> [ORACLE_HOME]/rdbms/admin/
Notes:
In Oracle 9iR2, the initxml.sql script will install XML SQL Utility (XSU).
In 9iR2, the xdk for pl/sql is depricated since there are 3 new packages called
dbms_xmldom, dbms_xmlparser, and dbms_xslprocessor. Therefore, after following
the above steps, you will have 2 ways to do XML from PL/SQL. We recommend you
use the dbms_* packages. If you have existing code that uses the orginal java
based packages (xmlparser, xmldom, and xslprocessor), you have 3 options (in
order of preference):
1) To use the new C based packages with minor code changes, add "dbms_" to all
references to xmlparser, xmldom, and xslprocessor.
2) To use the new C based packages without any code changes, drop the public
synonyms for these three packages and recreate them pointing to the dbms_*
packages.
drop public synonym xmldom;
drop public synonym xmlparser;
drop public synonym xslprocessor;
create public synonym xmldom for dbms_xmldom;
create public synonym xmlparser for dbms_xmlparser;
create public synonym xslprocessor for dbms_xslprocessor;
grant execute on xmldom to public;
grant execute on xmlparser to public;
grant execute on xslprocessor to public;
3) To use the depricated java based api, do nothing as public synonyms were
created for the java stored procedures xmldom, xmlparser, and xslprocessor.
--------------------------
--------------------------
How to Install the Latest 9.2 XDK for PL/SQL on any 8.1.6 and 8.1.7 Database
--------------------------
1. Please review the pre-installation notes below.
2. Uninstall the current XML Objects (see specific section later in this note).
Note: The 9.2.0.x XDK can't coexist in 2 different schemas. Therefore, it
is imperative that you remove any existing versions of the XML Parser and
XSU before loading the XDK downloaded from Oracle Technet.
3. Download the latest PL/SQL XDK from Oracle Technet. Because of the difference
in text files on UNIX and DOS/Windows (CRLF vs. NEWLINE), there are 2
downloads available from technet. Both contain the same Java classes though.
You should download the file that matches the platform from which you will
do the install; not the platform of the server where the XDK will be loaded.
Please review any installation notes to make sure this version will work
with your Database release.
4. Unzip the file.
- On Windows use Windows Unzip Utility
- On Unix use the following two steps:
$> gunzip -dv xdk_file_you_downloaded
$> tar -xvf resulting_file_from_gunzip
Here is an example,
$> gunzip -dv xdk_plsql_9_2_0_6_0.tar.gz
$> tar -xvf xdk_plsql_9_2_0_6_0.tar
The following files and directories/folders will be created:
bin - xdk executables and utilities
lib - directory for libraries
xdk - top xdk directory
xdk/demo - directory for demo files
xdk/doc - directory for documentation
xdk/admin - direcory for dband config files
xdk/*html - doc navigation files
xdk/license.html - copy of license agreement
5. Change directory to the newly created xdk/admin directory or folder.
Modify the file xsusyn.sql and remove the "or replace" syntax, if found.
e.g. create or replace public synonym
As of this writing, xsusyn.sql uses new syntax for synonym creations and
this will cause a problem on 9.0.1.0 and lower releases..
6. Change directory to the newly created bin directory or folder.
7. Run xdkload using the following options:
- For 8.1.7:
CSH/TCSH users:
./xdkload -u "somexmlschema/somepasswor
KSH users:
. ./xdkload -u "somexmlschema/somepasswor
DOS users:
xdkload -u "somexmlschema/somepasswor
- For 8.1.6:
Review steps above and replace '817' above with '816' as the argument to
-dbver.
8.1.7 server specific Note: If you discover that you have invalid Java objects
in SYS after following these instructions, you likely have a corrupt JVM and
should follow [NOTE:219862.1] which does a full JVM reinstall along with the
9.2.0.x XDK for PLSQL.
--------------------------
--------------------------
How to Install the Latest 9.2 XDK for PL/SQL on any 9.0.1 Database Version
--------------------------
1. Please review the pre-installation notes below.
2. Uninstall the current XDK (see specific section in this note).
Note: The 9.2.0.x XDK can't coexist in 2 different schemas. Therefore, it
is imperative that you remove any existing versions of the XML Parser and
XSU before loading the XDK downloaded from Oracle technet.
3. Download the latest PL/SQL XDK from Oracle Technet. Because of the difference
in text files on UNIX and DOS/Windows (CRLF vs. NEWLINE), there are 2
downloads available from technet. Both contain the same Java classes though.
You should download the file that matches the platform from which you will
do the install, not the platform of the server where the XDK will be loaded.
Please review any installation notes to make sure this version will work
with your Database release.
4. Unzip the file.
- On Windows use Windows Unzip Utility
- On Unix use the following two steps:
$> gunzip -dv xdk_file_you_downloaded
$> tar -xvf resulting_file_from_gunzip
Here is an example,
$> gunzip -dv xdk_plsql_9_2_0_6_0.tar.gz
$> tar -xvf xdk_plsql_9_2_0_6_0.tar
The following files and directories/folders will be created:
bin - xdk executables and utilities
lib - directory for libraries
xdk - top xdk directory
xdk/demo - directory for demo files
xdk/doc - directory for documentation
xdk/admin - direcory for dband config files
xdk/*html - doc navigation files
xdk/license.html - copy of license agreement
5. If installing the XDK on database version 9.0.1.0, change directory to the
newly created xdk/admin directory or folder.
Modify the file xsusyn.sql and remove the "or replace" syntax if found.
e.g. create or replace public synonym
As of this writing, xsusyn.sql uses new syntax for synonym creations and
this will cause a problem on 9.0.1.0 and lower releases.
6. If installing a 9.2.0.2 XDK on a 9.0.1.x release, change directory to the
newly created lib directory or folder.
copy the xdb.jar file to xdb.jar.orig
copy the xdb_9012.jar to xdb.jar
7. Change directory to the newly created bin directory or folder.
CSH/TCSH users:
./xdkload -u "somexmlschema/somepasswor
KSH users:
. ./xdkload -u "somexmlschema/somepasswor
DOS users:
xdkload -u "somexmlschema/somepasswor
--------------------------
How to Install the Latest 9.2 XDK for PL/SQL on any 9.2 Database Version
--------------------------
At the time of this writing, the latest XDK is 9.2.0.6 and it is already
included in the 9.2.0.4 server patchset. Please install the latest patchset
and the current XML in the SYS schema will automatically be upgraded.
Note: If you have previously replaced the original XML functionality in SYS
by creating a schema for an updated version of the XDK for PL/SQL, then you
will need to remove this schema and install the original XML functionality
(run initxml.sql as SYS) before applying the latest server patchset.
--------------------------
--------------------------
Pre-Installation Notes:
(1) Although you can run xdkload script as any database user, do not
install it in the SYS or SYSTEM schema. We recommend you create a
separate DBA privileged schema as this schema will only be used for
XDK maintenance. Along with dba privs, this schema must have
JAVAUSERPRIV and JAVASYSPRIV privileges. No development should be
done in this schema as future upgrades will just involve dropping
this schema and recreating it.
(2) In order to avoid the following security error at load time:
"Error while turning off verifier ORA-29532: Java call terminated by
uncaught Java exception: java.security.AccessContro
Permission (oracle.aurora.security.JS
been granted by dbms_java.grant_permission
Domain(ITL|PolicyTableProx
Grant following permission to that user:
SQL> call dbms_java.grant_permission
(Replace 'USER' in above command with the actual name of this XDB USER / schema.)
(3) When installing the XDK on the 9i release, you need to make sure that you
do not try to load java into a schema whose tablespace has SEGMENT SPACE MANAGEMENT
set to AUTO. (See [NOTE:158567.1])
(4) We recommend that you install the latest available server patchset before
installing the latest XDK.
(5) When installing the latest XDK on an 8i release, it is necessary to
specify the -dbver option so that your script can load the specific
xsu12_*.jar file compatible with your database version.
(6) At the time of this writing, version 9.2.0.6 is the latest version of
the XDK. You will see references to multiple platform specific XDK
downloads on the OTN website, however, there are only 2 different
downloads: one for all "UNIX" platforms and another for Windows platforms.
Although you may have downloaded this file previously, you should
re-download it now to make sure you do not hit any known 'kit issues'
that may have been fixed since your last download. Kit issues
(corrupt tar archive, missing files, incorrect file permissions, etc.)
do not cause a version increment so the previous
"xdk_plsql_9_2_0_6_0_tar.g
today.
Item 7 only applies to UNIX:
(7)UNIX Specific Installation Notes that only apply to downloads from OTN:
a) It may be necessary to set the execute permission on the xdkload
script.
b) To use the Xdkload script without modifications, it is required that
tsch is installed in the /bin directory
If you do not have tcsh installed or it is in another location,
you will need to modify the xdkload script to either use csh or
specify the actual tcsh directory location:
EG. change the first line from xdkload from
#!/bin/tcsh
to
#!/bin/csh
How to Verify Installation of the XDK for PL/SQL
--------------------------
Log into SQL*PLUS as the schema owner of the XDK.
1. Verify there are no invalid Java classes that start with name "oracle/xml"
by querying the ALL_OBJECTS view.
SQL> Select substr(dbms_java.longname(
class, status from all_objects where object_type = 'JAVA CLASS' and
status = 'INVALID';
2. Verify installation of the Oracle XML Parser for JAVA (i.e xmlparserv2.jar)
SQL> select substr(dbms_java.longname(
all_objects where object_type = 'JAVA CLASS' and
object_name = dbms_java.shortname('oracl
You should see the following result:
CLASS Status
--------------------------
oracle/xml/parser/v2/DOMPa
3. Verify the PL/SQL wrapper packages are created for the Oracle XML Parser for PL/SQL
DESC XMLPARSER;
or
DESC XMLDOM;
4. Verify the PL/SQL wrapper packages are created for the Oracle XML SQL Utility
DESC DBMS_XMLQUERY;
or
DESC DBMS_XMLSAVE;
--------------------------
--------------------------
How to Uninstall the 8.1.7 XSU and XML Parser from any 8.1.7 Database Version
--------------------------
1. Logon to SQL*Plus as the database user who owns the XML objects
2. Run [ORACLE_HOME]/oracore/admi
This above script does not drop packages and .jar files related to Oracle XML Utility.
3. Run the following commands to drop Oracle XML Utility classes and packages:
a. Call sys.dbms_java.dropjava('-v
b. Ensure, that the old xmlparser classes will be deleted by doing the
following, stated in [NOTE:209690.1] before new xmlparser is installed:
Call sys.dbms_java.dropjava('-v
c. Drop XSU packages and synonyms:
drop package dbms_xmlquery;
drop package dbms_xmlsave;
drop package xmlgen;
drop public synonym dbms_xmlquery;
drop public synonym dbms_xmlsave;
drop public synonym xmlgen;
Note:
The datbase user running the above commands should have 'drop public synonym'
privilege.
--------------------------
--------------------------
How to Uninstall the 9i XML functionality from any 9i Database Version
--------------------------
1. Logon to SQL*PLUS as the database user who owns the XML related objects
2. If the XDK is version 9.0.1.x, change the following line in rmxml.sql
before running:
drop public synonym xmlprocessor;
to
drop public synonym xslprocessor;
Please refer to [BUG:2060389] for details
3. Run [ORACLE_HOME]/xdk/admin/rm
Note:
Database user running the above commands should have 'drop public synonym'
privilege.
--------------------------
--------------------------
How to Uninstall the 9i XDK for PL/SQL from any 8.1.6 or 8.1.7 Database
--------------------------
If you installed the 9i XDK for PL/SQL in its own schema, then just drop the
schema.
If you installed the 9i XDK for PL/SQL in SYS or SYSTEM:
There are no Oracle provided scripts to uninstall the XDK, so you need to run
the necessary commands manually to remove all of the .jar classes and packages
related to the XDK. :
1. Log into SQL*PLUS as the database user who owns the XDK for PL/SQL.
2. If the XDK is version 9.0.1.x, change the following line in xmldrop.sql
from:
drop public synonym xmlprocessor;
to:
drop public synonym xslprocessor;
Please refer to [BUG:2060389] for details.
3. Drop PL/SQL XML Parser packages from the directory where you unzipped the 9i
XDK for PL/SQL file previously downloaded from the Oracle Technet site. This
step is not applicable to XDK for Java.
SQL> @xmldrop.sql
4. Drop XSU packages and synonyms.
drop package dbms_xmlquery;
drop package dbms_xmlsave;
drop package xmlgen;
drop public synonym dbms_xmlquery;
drop public synonym dbms_xmlsave;
drop public synonym xmlgen;
5. From the operating system command line and from within the directory where
you unzipped the 9i XDK file downloaded from the Oracle Technet site, issue
a dropjava command for each of the following java archives (if they exist):
./xdk/lib/xschema.jar
./xdk/lib/xsu12_81*.jar (81*= 816 or 817)
./xdk/lib/classgen.jar
./xdk/lib/xmlparserv2.jar
./xdk/lib/xmlplsql.jar
eg. dropjava -v -u user/password ./xdk/lib/xschema.jar
Note: Database user running the above commands should have 'drop public synonym'
privilege.
--------------------------
Hope this helps