This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 19, 2012 9:54 PM by 929791 RSS

Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab

929791 Newbie
Currently Being Moderated
Hi!
My tutor asked me to store and query the xml files in a "native" way in xml db. First I tried to register the schemas on which those xml files are based. There are about 30 of schemas asscociated with each other, and I have to use "FORCE" to register them , like this:

BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://www.isotc211.org/gmd/metadataEntity.xsd',
bfilename('ISOGMD', 'metadataEntity.xsd'),
TRUE,
TRUE,
FALSE,
TRUE,
TRUE);
END;

Then no tables are created automaticly. And when I wanted to create a XML table based on thses schemas manually, it proped the error: ORA-19046:Out-of-line table cannot be shared by two top-level tab. This has confused me for about a week. Does anyone know what's wrong with the registration? Waiting eagerly on line! Thanks!
  • 1. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    926788 wrote:
    Hi!
    My tutor asked me to store and query the xml files in a "native" way in xml db. First I tried to register the schemas on which those xml files are based. There are about 30 of schemas asscociated with each other, and I have to use "FORCE" to register them , like this:
    That's probably your problem (the "force" part).

    Try first to see if you can achieve registering the XML schema's as demonstrated by ORA-19046 with valid xsd-file (on 11.2.0.2)

    or switch to binary XML storage

    Database version would be ....?
  • 2. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    929791 Newbie
    Currently Being Moderated
    Hi!Marco
    In fact I did try the 7 schemas posted in ORA-19046 with valid xsd-file (on 11.2.0.2) before, and i used your scripts to register those 7 schemas, but it threw ORA-31011 error: XML parsing failed (I've successfully installed the xdb_util u recommended in threadID=2171415 ).
    I'm using the oracle 11.2 express edition, and I'm completely bewilldered by xml db. Except for the Oracle Example "PurchaseOrder.xsd", I never succeeded in registering any schemas (T_T), ORA-19046 constantly props. Since I got 30 more complicated schemas to register, I can hardly specify where the problem lies.
  • 3. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    I can imagine. The XML arena has a high learning curve.
    You might make your life a bit easier and retry using Binary XML storage instead of Object Relational XMLType storage.
    That won't help you, might not help you, with the underlying problem though. As long as you need "force" to register schema's you might be in trouble. Force should ONLY be used if you know that their are cyclic dependencies between the XML schemas.
  • 4. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    929791 Newbie
    Currently Being Moderated
    There are indeed cyclic dependencies between my 30+ schemas, My task is to register them , and create only several tables according to some important and frequently used elements. Now i got thousands of elements and attributes in those schemas, I don't know how to annotare them, so that only a few useful tables are created. Could you give me some advice on that? Manually annotating dozens of complex schemas sounds frustrating. Thanks very much!
  • 5. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    929791 Newbie
    Currently Being Moderated
    Hi Marco!
    Sorry to disturb u again. I've solved the 19046 problems by annotating all the schemas, setting defaulttable="" for complex elements. But i STILL FAILED to register the schemas. Errors like 31096. 00000 - "validation failed for schema" keep occurring. My tutor insist on using structured storage instead of binary storage, so I've been kept on working in this for nearly a month. I wanna know that is it really possible to register complex schemas that with cyclic dependencies? I succeeded in registering some schemas that include 5 files with cycling dependencies, but when it comes to my case(now 72 schemas), I failed again and again. Would u help me with that? Thanks very much!!!
  • 6. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Use the "XDB Utilities" package(s) to automate xdb:annotations and more. See xdb_util.zip on http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html
  • 7. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    You haven't told us yet on which database version you are trying this (all digits please or "select * from v$version")

    If you have an Oracle support contract ask Oracle for help (if possible Senior Product Manager Mark Drake).
    My tutor insist on using structured storage instead of binary storage...
    Is this a school project???
  • 8. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    929791 Newbie
    Currently Being Moderated
    In fact, this is my undergraduation project, and it's also little part of my tutor's project. He is trying structured storage for his large amount of xml files. the version I am using now is Oracle Database 11g Express Edition Release 11.2.0.2.0+ on Windows 32 bits.
    My schemas are all validated by XMLSPY. I've already annotated the schemas by the tool u recomended, only set the defaulttable=" " for all the complex types. No matter I use "force" , or register one by one, it constantly props various sorts of errors, like "ORA-30756:cannot create column or table of type that contains a supertype attribute".
    In theory, i think the schemas could be handle by oracle, so I'm still trying.

    Cheers!
  • 9. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Then you are probably out of luck regarding getting help from Oracle, guessing that your site doesn't pay for support. XMLType OR storage is very rewarding if you get it done, but it also needs a lot of design (logical/physical) via the XML schema's and/or annotations. Most of the time this takes me 66% of the time to setup a complete customer architecture, I noticed. Writing the needed views, PL/SQL code etc afterwards is almost childsplay, if, and only if, that first design phase is done properly (the is in this case the XMLType OR setup including annotations etc)
  • 10. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Paste what you are doing here
  • 11. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    929791 Newbie
    Currently Being Moderated
    First of all, i create a "filename.txt" that contains all my schemas' names. the names are:

    acquisitionInformation.xsd
    applicationSchema.xsd
    basicTypes.xsd
    catalogues.xsd
    citation.xsd
    codelistItem.xsd
    constraints.xsd
    ........

    temporalTopology.xsd
    topology.xsd
    units.xsd
    uomItem.xsd
    valueObjects.xsd
    xlinks.xsd

    there are 72 schemas. Then I register them using "force":

    GRANT EXECUTE ON utl_file to fld;

    create table test (
    fld1 VARCHAR2(50));

    declare
    isto_file utl_file.file_type;
    fp_buffer varchar2(4000);
    begin
    isto_file := utl_file.fopen('MYXMLDIR', 'filename.txt', 'R');
    loop
    begin
    utl_file.get_line (isto_file , fp_buffer );
    insert into test values(fp_buffer);
    Exception
    when no_data_found then
    exit;
    end;
    end loop;
    utl_file.fclose(isto_file);
    end;


    declare
    cursor my_cursor is select fld1 from test;
    v_name varchar2(50);
    begin
    open my_cursor;
    loop
    fetch my_cursor into v_name;
    dbms_xmlschema.registerSchema(
    schemaurl=>v_name,
    schemadoc=>bfilename('MYXMLDIR',v_name),
    local=>TRUE,
    gentables=>true,
    force=>true,
    csid=>nls_charset_id('AL32UTF8')
    );
    exit when my_cursor%NOTFOUND;
    end loop;
    close my_cursor;
    end;


    at last, when I check the types generated during registration, I found that only a small number of types are automatically generated. When I try to create table manually ( based on certain element of one schema), it props errors, sometimes "31079. 00000 - "unable to resolve reference to %s \"%s\"" ", sometimes other errors.

    If I manually register them one by one from the base schema, then the registeration fail at some point. this is the script:

    BEGIN
    DBMS_XMLSCHEMA.registerSchema(
    'geometryAggregates.xsd',
    bfilename('XSD', 'geometryAggregates.xsd'),
    TRUE,
    TRUE,
    FALSE,
    TRUE);
    END;

    ERRORS
    22881. 00000 - "dangling REF"

    Is that something wrong with my schemas? I wonder how can i paste them here. there are 72 files.......
  • 12. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    - purge the recyclebin
    - commit in between every registration step
    - do not register default XML Schema's Oracle already registered for you like among others xlinks.xsd.
  • 13. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    odie_63 Guru
    Currently Being Moderated
    Are you working with this set of schemas?

    http://schemas.opengis.net/gml/

    Which version?
  • 14. Re: Help With Ora-19046:Out-of-line table cannot be shared by two top-level tab
    929791 Newbie
    Currently Being Moderated
    [http://eden.ign.fr/xsd/isotc211/isofull/20090316] Yes!! but not only gml, but also include many other schemas. I downloaded them from

    http://eden.ign.fr/xsd/isotc211/isofull/20090316

    I'm using the full ISO 19139, namely, all the folders on this page. But the gml folder in this page is VERSION 3.2.1 . When I validated those schema included in "FULLIsoschemas-20090316.zip" with xmlspy, it shows some elements related to gml are nowhere to be found. So I replaced the gml 3.2.1 folder, with gml 3.2.0, then they are validated. Then I modified the original schemalocation, for regisration. for example:

    in the /gmd/content.xsd, the original version is:

    +<xs:import namespace="http://www.isotc211.org/2005/gco" schemaLocation="../gco/gco.xsd"/>+
    +     <xs:include schemaLocation="../gmd/citation.xsd"/>+

    I modified the schemalocation, to :

    +<xs:import namespace="http://www.isotc211.org/2005/gco" schemaLocation="gco.xsd"/>+
    +<xs:include schemaLocation="citation.xsd"/>+

    I put all the files into one folder ( not using the "gmd"/"gml"/"gco".....); modified the schemalocation one by one; annotated the defaulttable=" "; I use their file name as registerURI, registered them one by one:

    BEGIN
    DBMS_XMLSCHEMA.registerSchema(
    'content.xsd',
    bfilename('XMLEG', 'content.xsd'),
    TRUE,
    TRUE,
    FALSE,
    true
    );
    END;
    /

    First Xlinks.xsd, then gmlbasictypes.xsd, then gmlbasictypes.xsd......when it comes to geometryAggregates.xsd( sometimes other schemas), it failed. recieve "Dangling REF" errors . I don't know if this because the schemas are logically wrong, or because I had made mistakes in operating xml db.Now I'm trying Marco's suggestion ....
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points