14 Replies Latest reply: May 18, 2012 10:37 AM by 580267 RSS

    XML DB usage with WCC/UCM

    580267
      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
          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
            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
              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
                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)
                  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
                    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
                      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)
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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.