This discussion is archived
14 Replies Latest reply: May 18, 2012 8:37 AM by 580267 RSS

XML DB usage with WCC/UCM

580267 Explorer
Currently Being Moderated
I'm trying to set up a dev platform to play with XML DB in relation to Oracle's WCC/UCM content management. With some taxonomy tools using XML I'd like to be able to use their Schema and XML output as a direct or indirect input to WCC/UCM's metadata selection process. But my SQL/DB skills are a bit weak I must admit. So a few questions:

I want to register a schema from an industry standard source but I'm getting errors that I suspect is because I don't understand the directory reference in bfilename
schemadoc => BFILENAME ('XML_TEST','standardData.xsd')
In this example where should XML_TEST directory be found? Is it a filesystem pathname or something related to the DB and I'm misunderstanding the documentation?

Can Oracle XE DB be used or must I set up a full installation of DB? I'm doing this on a VM and would like to keep the footprint small after installing DB and WCC.

Thanks
  • 1. Re: XML DB usage with WCC/UCM
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    In this example "XML_TEST" is a directory alias, for example it could me defined as follows for Windows or *Nix environments
    SQL> -- Windows
    SQL> create or replace directory XML_TEST as 'c:\temp';
    
    SQL> -- Linux/Unix
    SQL> create or replace directory XML_TEST as '/tmp';
    If you have READ, WRITE access you will be able to read the standardData.xsd file if it exists in the server directories (outside the database) c:\temp or /tmp. Of course you can pick other directories as long as the Oracle software server owner can access those directories on the OS where the database resides.

    Oracle 11g Express should be fine to use.
  • 2. Re: XML DB usage with WCC/UCM
    580267 Explorer
    Currently Being Moderated
    OK that is close to what I was doing so here is the response I get back in SQL Plus

    SQL> DBMS_XMLSchema.registerSchema(schemaurl => 'SampleZTHES.xsd',schemadoc => BFILENAME ('c:\Oracle','SampleZTHES.xsd'));
    SP2-0734: unknown command beginning "DBMS_XMLSc..." - rest of line ignored.

    Can you spot my error?
  • 3. Re: XML DB usage with WCC/UCM
    odie_63 Guru
    Currently Being Moderated
    Can you spot my error?
    You must use exec[ute] or call statement to run stored procedures and functions, e.g. :
    SQL> exec DBMS_XMLSchema.registerSchema(schemaurl => 'SampleZTHES.xsd',schemadoc => BFILENAME ('c:\Oracle','SampleZTHES.xsd'));
  • 4. Re: XML DB usage with WCC/UCM
    580267 Explorer
    Currently Being Moderated
    With the EXECUTE statement we get to the following

    SQL> EXECUTE dbms_xmlschema.registerschema(schemaurl => 'SampleZTHES.xsd', schemadoc => BFILENAME('c:\Oracle','SampleZTHES.xsd'));
    BEGIN dbms_xmlschema.registerschema(schemaurl => 'SampleZTHES.xsd', schemadoc =>
    BFILENAME ('c:\Oracle','SampleZTHES.xsd')); END;

    *
    ERROR at line 1:
    ORA-22285: non-existent directory or file for FILEOPEN operation
    ORA-06512: at "SYS.DBMS_LOB", line 805
    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 131
    ORA-06512: at line 1

    I connected as SYS AS SYSDBA
    The directory and file exist at the location indicated.
    The 3rd & 4th line above (BEGIN to END) was reflected by the system and not entered by me.
  • 5. Re: XML DB usage with WCC/UCM
    Jason_(A_Non) Expert
    Currently Being Moderated
    I connected as SYS AS SYSDBA
    Do not use this account for registering schema as it will register the schema within the SYS user and that is not what you want and can cause issues. Log in as a the user who will be using the schema, ideally, otherwise use some other account.

    The BFILENAME issue is caused because your first parameter should be a directory object as shown by the documentation
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions019.htm#SQLRF00610

    So you will need to create a directory object, grant privs against it (as I recall) and use that directory object name as the parm.
  • 6. Re: XML DB usage with WCC/UCM
    odie_63 Guru
    Currently Being Moderated
    Read again Marco's last reply above.

    You need to create an Oracle directory object (logical alias) that points to your real physical location.
    Then you can use the directory object in the BFILE constructor.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5007.htm#SQLRF01207

    Edit : as Jason said... ;)

    Edited by: odie_63 on 9 mai 2012 21:55
  • 7. Re: XML DB usage with WCC/UCM
    580267 Explorer
    Currently Being Moderated
    Another step in the right direction so here is where it got me:

    SQL> connect
    Enter user-name: dev_ocs as sysdba
    Enter password:
    Connected.
    SQL> EXECUTE DBMS_XMLSCHEMA.REGISTERSCHEMA(schemaurl => 'SampleZTHES.xsd',schema
    doc => BFILENAME ('TEST_XML','SampleZTHES.xsd'));
    BEGIN DBMS_XMLSCHEMA.REGISTERSCHEMA(schemaurl => 'SampleZTHES.xsd',schemadoc =>
    BFILENAME ('TEST_XML','SampleZTHES.xsd')); END;

    *
    ERROR at line 1:
    ORA-19046: Out-of-line table cannot be shared by two top-level tables.
    ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 37
    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 65
    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 136
    ORA-06512: at line 1


    SQL>
  • 8. Re: XML DB usage with WCC/UCM
    Jason_(A_Non) Expert
    Currently Being Moderated
    Enter user-name: dev_ocs as sysdba
    There is no need for "as sysdba" for these commands.

    I'm not a schema registration expert so I'll ask a few introductory questions.
    Did you Google that to see what hits on the forum you get? I'm not saying you'll get the answer, but maybe some insight, such as {thread:id=2171415}

    Can you provide the schema you are trying to register or a link to it? Just guessing but is it http://zthes.z3950.org/schema/index.html

    And what is your version?
    select * from v$version;
  • 9. Re: XML DB usage with WCC/UCM
    580267 Explorer
    Currently Being Moderated
    I've looked at a couple dozen oracle forum and external posts on the subject and haven't seen that one. The others make no reference to some of the details so i will try this next.
    Zthes is the .xsd but its the Synaptica flavor.
    The db I'm using is 11.2 usually XE, but I may have to switch to another edition as I'm begining to suspect not all of XML DB is present. The one time i ran the same code on XE and EE i got different results.
  • 10. Re: XML DB usage with WCC/UCM
    odie_63 Guru
    Currently Being Moderated
    The db I'm using is 11.2 usually XE, but I may have to switch to another edition as I'm begining to suspect not all of XML DB is present.
    XE should be fine for all XML DB features.

    What do you want to do with the schema BTW?

    - Do you only want to validate instance documents?
    - Do you want to store instance documents? as Object-Relational, or binary XML?
    - Do you want to query documents to extract fragments or present the data in a relational form?
  • 11. Re: XML DB usage with WCC/UCM
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    XE should be fine for all XML DB features.
    Thats not true Marc...

    http://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm#BABJBGGA

    which states among others

    XML support in the database: Yes (no JNDI, or Servlet support)
    Database Web services: No

    Reason among others --> Java support in the database: No

    Edited by: Marco Gralike on May 11, 2012 5:10 PM
  • 12. Re: XML DB usage with WCC/UCM
    odie_63 Guru
    Currently Being Moderated
    Database Web services: No
    ?

    Native DWS works fine on my XE instance, at least the C servlet. Maybe the doc refers to something else?

    You're right anyway, thanks for the reminder.

    As far as I have experienced though, I've not encountered any limitation regarding XML DB features such as schema registration, indexation, XMLType tables, XQuery etc. (which was what I had in mind initially)
  • 13. Re: XML DB usage with WCC/UCM
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    odie_63 wrote:
    Database Web services: No
    Native DWS works fine on my XE instance, at least the C servlet. Maybe the doc refers to something else?
    ...at least the C servlet...(aka not Java)
  • 14. Re: XML DB usage with WCC/UCM
    580267 Explorer
    Currently Being Moderated
    I want it to manage taxonomy output as XML from Synaptica. That XML I would like to try an access from Oracle WebCenter Content as the values for metadata elements. So when a particular XML document is checked in my new set of drop down lists(multiple tables referenced typically) or tree list members would come from the new XML.
    Historically the approach used is to output to csv and import to tables. The csv only has the node values (ID & Label) and none of the additional attributes at that node which the xml can carry.

    Thankfully this is not a critical path objective for the project but my idea to explore something new in my free time.

Legend

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