Solved

XML FILE LOAD INTO ORACLE

Posted on 2004-03-25
3
3,149 Views
Last Modified: 2007-12-19
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_LOB.FILE_READONLY);
  len := DBMS_LOB.GETLENGTH(fil);
  DBMS_LOB.READ(fil,len,1,buffer);
  xmlgen.resetOptions;
  insrow := xmlgen.insertXML('xml_doc',UTL_RAW.CAST_TO_VARCHAR2(buffer));
  DBMS_OUTPUT.PUT_LINE(insrow);
 
  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(SQLERRM(SQLCODE));
  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?
0
Comment
Question by:smena
3 Comments
 
LVL 3

Accepted Solution

by:
concon earned 125 total points
ID: 10685153
Hi smena,

i did not used xmlgen but i have used xmlparser and xmldom packs before.

i have installed XDK for PL/SQL(available in otn). then i've granted execute for these packages from system user to my application roles.

maybe you need to perform these steps.

Regards.
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10686021
Hi,

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/admin/initxml.sql
     
 
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/catxsu.sql
 
 
--------------------------------------------------------------------------
 
-----------------------------------------------------------------
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/xmlload.sql:
   
        --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/initxml.sql
 
 
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/initxml.sql
 
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/somepassword" -s -dbver 817
 
    KSH users:
 
    . ./xdkload -u "somexmlschema/somepassword" -s -dbver 817
 
    DOS users:
    xdkload -u "somexmlschema/somepassword" -s -dbver 817
 
   - 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/somepassword" -s  
 
   KSH users:
 
   . ./xdkload -u "somexmlschema/somepassword" -s
 
   DOS users:
   xdkload -u "somexmlschema/somepassword" -s
 
 
 
-----------------------------------------------------------------------
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.AccessControlException: the
         Permission (oracle.aurora.security.JServerPermission Verifier) has not
         been granted by  dbms_java.grant_permission to SchemaProtection
         Domain(ITL|PolicyTableProxy(ITL))"
         
        Grant following permission to that user:
 
        SQL> call dbms_java.grant_permission('USER','SYS:oracle.aurora.security.JServerPermission', 'Verifier', '' );
         
        (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.gz" is not necessarily the same one available
        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(object_name),1, 30) as
       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(object_name),1, 30) as class, status from
         all_objects where object_type = 'JAVA CLASS' and    
         object_name = dbms_java.shortname('oracle/xml/parser/v2/DOMParser');
 
 
    You should see the following result:
 
    CLASS                               Status
   --------------------------------     ---------
   oracle/xml/parser/v2/DOMParser       Valid
 
 
 
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/admin/rmxml.sql
 
   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 rdbms/jlib/xsu12.jar');
 
   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 xdk/lib/xmlparserv2.jar');
 
   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/rmxml.sql.
 
 
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



0
 

Author Comment

by:smena
ID: 10689170
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....
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now