DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Require DBMS_XMLGEN package script(Spec and Body)

  1. #1
    Join Date
    Oct 2008
    Posts
    7

    Exclamation 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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Oct 2008
    Posts
    7
    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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Oct 2008
    Posts
    7
    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
  •  


Click Here to Expand Forum to Full Width