-
Require DBMS_XMLGEN package script(Spec and Body)
Hi All,
Can anyone share script of DBMS_XMLGEN package?
I am able to create package spec using DBMSXML.SQL, but can't locate a script to create package body.
I need this in urgency...anyhelp is highly appreciated.
Thanks,
Shivam
-
This is a C compiled package, let me quote Metalink 565166.1...
Recreate the DBMS_XMLGEN package as follows.
From SQL*Plus connected to the database as AS SYSDBA, enter:
SQL> @?/rdbms/admin/dbmsxml.sql
SQL> @?/rdbms/admin/prvtxml.plb
SQL> alter package SYS.DBMS_XMLGEN compile body;
SQL> alter package SYS.DBMS_METADATA compile body;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks!
I ran the two scripts and was able to create the package DBMS_XMLGEN.GETXML successfully..
However, was unable to use the function to generate the XML..here's what I did...
SQL I ran to generate XML:
select dbms_xmlgen.getXml('''select dbms_xmlschema.generateschema('''||'HR'||''','''||'TBL_URS'||''')''') from dual;
Here's the error:
ERROR:
ORA-19202: Error occurred in XML processing
ORA-24333: zero iteration count
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
-
It may be a syntax or other issue, I would test package with a simple statement - like:
HTML Code:
SQL>
SQL> set pagesize 0
SQL> set linesize 150
SQL> set long 9999999
SQL> set head off
SQL>
SQL> select dbms_xmlgen.getxml('select object_name, object_type from sys.dba_objects where rownum < 6') xml
2 from dual;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<OBJECT_NAME>TAB$</OBJECT_NAME>
<OBJECT_TYPE>TABLE</OBJECT_TYPE>
</ROW>
<ROW>
<OBJECT_NAME>I_IND1</OBJECT_NAME>
<OBJECT_TYPE>INDEX</OBJECT_TYPE>
</ROW>
<ROW>
<OBJECT_NAME>I_COBJ#</OBJECT_NAME>
<OBJECT_TYPE>INDEX</OBJECT_TYPE>
</ROW>
<ROW>
<OBJECT_NAME>USER$</OBJECT_NAME>
<OBJECT_TYPE>TABLE</OBJECT_TYPE>
</ROW>
<ROW>
<OBJECT_NAME>I_OBJ1</OBJECT_NAME>
<OBJECT_TYPE>INDEX</OBJECT_TYPE>
</ROW>
</ROWSET>
SQL>
Last edited by PAVB; 06-01-2010 at 11:28 AM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hello Paul,
I tried running your SQL and was successful in generating XML.
1 My Requirement:
Generate XML usign an XSD generated from object type TBL_URS (table type).
2 SQL used to generate XSD
select dbms_xmlschema.generateschema('HR','TBL_URS') from Dual;
spooled the above XSD at local directory
3 Workaround to generate XML
select dbms_xmlgen.getXml('''select dbms_xmlschema.generateschema('''||'HR'||''','''||'TBL_URS'||''')''') from dual;
- Whether XML can be generated only through a SQL statement or can be done using generated XSD as well?
- Please advice whether #3 is correct?
- How to generate XML using XSD?
Please advice...
TIA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|