OS & Oracle

	OS & Oracle	Comments Posts

XQuery vs SQL/XML

XQuery 1.0 is the W3C language designed for querying XML data. It is similar to SQL in many ways, but just as SQL is designed for querying structured, relational data, XQuery is designed especially for querying semistructured, XML data from a variety of data sources. You can use XQuery to query XML data wherever it is found, whether it is stored in database tables, available through Web Services, or otherwise created on the fly. In addition to querying XML data, XQuery can be used to construct XML data. In this regard, XQuery can serve as an alternative or a complement to both XSLT and the other SQL/XML publishing functions, such as XMLElement.

Everything in XQuery is an "expression", one most important expression is the FLWOR expression, composed of the following, in order, from which FLWOR takes its name: for, let, where , order by, return.

SQL functions XMLQuery and XMLTable are defined by the SQL/XML standard as a general interface between the SQL and XQuery languages. As is the case for the other SQL/XML functions, XMLQuery and XMLTable let you take advantage of the power and flexibility of both SQL and XML. Using these functions, you can construct XML data using relational data, query relational data as if it were XML, and construct relational data from XML data.

XMLQuery is the logical evolution of Oracle’s extract() operator, and XMLTable is the logical evolution of Oracle’s table(xmlsequence(extract(…)))

Below is a simple comparison between XML/SQL and XQuery.

create table test (KEY number, XML_INFO xmltype);
insert into test
   (KEY, XML_INFO)
 values
   (1,
    '<?xml version="1.0" encoding="UTF-8"?>
    <lists>
      <list name="String">
                <display-name >test</display-name>
     </list>
   </lists>');

--XQuery version
SELECT test.KEY AS KEY, v.name, v.display
  FROM test, XMLTABLE('
  $xml_info/lists/list'
PASSING test.XML_INFO AS "xml_info"
COLUMNS name VARCHAR2(100) PATH '@name',
display VARCHAR2(100) PATH 'display-name')  v;

--SQL/XML version
select test.key,
extractValue(value(v), '/list/@name'),
extractValue(value(v), '/list/display-name')
from test,
table(XMLSequence(extract(test.xml_info,'/resource-lists/list'))) v;

SQL/XML and XPath Rewrite

SQL/XML provides a series of functions to integrate XML functionality to SQL processing,below is the list of such functions:

APPENDCHILDXML
DELETEXML
DEPTH
EXTRACT (XML)
EXISTSNODE
EXTRACTVALUE
INSERTCHILDXML
INSERTXMLBEFORE
PATH
SYS_DBURIGEN
SYS_XMLAGG
SYS_XMLGEN
UPDATEXML
XMLAGG
XMLCDATA
XMLCOLATTVAL
XMLCOMMENT
XMLCONCAT
XMLFOREST
XMLPARSE
XMLPI
XMLQUERY
XMLROOT
XMLSEQUENCE
XMLSERIALIZE
XMLTABLE
XMLTRANSFORM

Also, XMLType is important in Oracle XML DB, which I have discussed in the last post.

XMLType is a system-defined opaque type for handling XML data. It has predefined member functions on it to extract XML nodes and fragments.

You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically using the SYS_XMLGEN and SYS_XMLAGG SQL functions.

Table 198-1 summarizes functions and procedures of the XMLType.

Table 198-1 XMLTYPE Subprograms

Method	Description
CREATENONSCHEMABASEDXML	Creates a non schema based XML from the input schema based instance.
CREATESCHEMABASEDXML	Creates a schema based `XMLType` instance from the non-schema based instance using the input schema URL.
CREATEXML	Static function for creating and returning an `XMLType` instance.
EXISTSNODE	Takes a `XMLType` instance and a `XPath` and returns 1 or 0 indicating if applying the `XPath` returns a non-empty set of nodes.
EXTRACT	Takes a `XMLType` instance and an `XPath`, applies the XPath expression and returns the results as an `XMLType`.
GETBLOBVAL	Returns the value of the `XMLType` instance as a `BLOB`
GETCLOBVAL	Returns the value of the `XMLType` instance as a `CLOB`.
GETNAMESPACE	Returns the namespace for the top level element in a schema based document.
GETNUMBERVAL	Returns the value of the `XMLType` instance as a `NUMBER`. This is only valid if the input `XMLType` instance contains a simple text node and is convertible to a number.
GETROOTELEMENT	Returns the root element of the input instance. Returns `NULL` if the instance is a fragment
GETSCHEMAURL	Returns the XML schema URL if the input is an XML Schema based.
GETSTRINGVAL	Returns the value of the `XMLType` instance as a string.
ISFRAGMENT	Checks if the input `XMLType` instance is a fragment or not. A fragment is a XML instance, which has more than one root element.
ISSCHEMABASED	Returns 1 or 0 indicating if the input `XMLType` instance is a schema based one or not.
ISSCHEMAVALID	Checks if the input instance is schema valid according to the given schema URL.
ISSCHEMAVALIDATED	Checks if the instance has been validated against the schema.
SCHEMAVALIDATE	Validates the input instance according to the XML Schema. Raises error if the input instance is non-schema based.
SETSCHEMAVALIDATED	Sets the schema valid flag to avoid costly schema validation.
TOOBJECT	Converts the `XMLType` instance to an object type.
TRANSFORM	Takes an `XMLType` instance and an associated stylesheet (which is also an `XMLType` instance), applies the stylesheet and returns the result as XML.
XMLTYPE	Constructs an instance of the `XMLType` datatype. The constructor can take in the XML as a `CLOB`, `VARCHAR2` or take in a object type.

The SQL/XML SQL functions and their corresponding XMLType methods allow XPath expressions to be used to search collections of XML documents and to access a subset of the nodes contained within an XML document.

Oracle XML DB provides two ways of evaluating XPath expressions that operate on XMLType columns and tables, depending on the XML storage method used:

Structured-storage XML data: Oracle XML DB attempts to translate the XPath expression in a SQL/XML function into an equivalent SQL query. The SQL query references the object-relational data structures that underpin a schema-based XMLType. This process is referred to as XPath rewrite. It can occur when performing queries and UPDATE operations.
Unstructured-storage XML data: Oracle XML DB evaluates the XPath expression using functional evaluation. Functional evaluation builds a DOM tree for each XML document, and then resolves the XPath programmatically using the methods provided by the DOM API. If the operation involves updating the DOM tree, the entire XML document has to be written back to disc when the operation is completed.

One example of XPath Rewrite is as following:

SELECT OBJECT_VALUE FROM mypurchaseorders p
WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Company') = 'Oracle';

will become:

SELECT VALUE(p) FROM mypurchaseorders p WHERE p.xmldata.Company = 'Oracle';

XMLType, its storage model and index?

XMLType is an abstract type for programming, whose storage model can be unstructured storage or structure storage.

Oracle XML DB uses XML schema as a mechanism for defining how the contents of an XMLType should be stored inside the database. Currently Oracle XML DB
provides two options:

Unstructured storage. The content of the XMLType is persisted as XML text using a CLOB datatype. This option is available for non-schema-based and schema-based XML content. When the XML is to be stored and retrieved as complete documents, unstructured storage may be the best solution as it offers the fastest rates of throughput when storing and retrieving XML content.
Structured storage. The content of the XMLType is persisted as a set of SQL objects. The structured storage option is only available when the XMLType table or column has been constrained to a global element defined by XML schema. If there is a need to extract or update sections of the document, perform XSL transformation on the document, or work through the DOM API, then structured storage may be the preferred storage type. Structured storage allows all these operations to take place more efficiently but at a greater overhead when storing and retrieving the entire document.

Let’s go to the index topic. This is the range of structured storage. Similar to the regular index, we can create an index when we create the table, or we can create an index using create index statement. Below is an example using create index.

CREATE INDEX ipurchaseorder_rejectedby
ON purchaseorder (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reject/User'));

One limitation is that this require the element to index must occur only once, as extractValue can’t return multiple values.

If the elements occurs multiple times, the index must be create when creating the table using nested table for the index element, one such example is as below:

CREATE TABLE purchaseorder OF XMLType
XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY "XMLDATA"."ACTIONS"."ACTION"
STORE AS TABLE action_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
ORGANIZATION INDEX OVERFLOW)
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM"
STORE AS TABLE lineitem_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
ORGANIZATION INDEX OVERFLOW);

In this way, the indexes for action and lineitem is setup when creating the table.

This CREATE TABLE statement above is equivalent to the CREATE TABLE statement automatically generated by Oracle XML DB when the schema annotation storeVarrayAsTable="true" is included in the root element of the PurchaseOrder XML schema (and genTables="true" is set during schema registration). When this annotation is used, the nested tables generated by the XML schema registration process are given system-generated names, which can be difficult to work with. You can give them more meaningful names using the SQL statement RENAME TABLE.

What elementFormDefault means in XML schema?

The elementFormDefault can be qualified or unqualified, unqualified is the default.
"qualified" means that all the local elements(such as shipTo below) must be qualified, either through specified namespace or default namespace.
Example:
po.xsd as:

<schema xmlns="http://www.w3.org/2001/XMLSchema"
        xmlns:po="http://www.example.com/PO1"
        targetNamespace="http://www.example.com/PO1"
        elementFormDefault="qualified"
        attributeFormDefault="qualified">
  <element name="purchaseOrder" type="po:PurchaseOrderType"/>
  <complexType name="PurchaseOrderType">
    <sequence>
      <element name="shipTo"    type="string"/>
    </sequence>
  </complexType>
</schema>

Corresponding xml can be:

<?xml version="1.0"?>
<prx:purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po2.xsd"
xmlns:prx="http://www.example.com/PO1">
   <prx:shipTo >
  </prx:shipTo>
</prx:purchaseOrder>

<?xml version="1.0"?>
<purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po2.xsd"
xmlns="http://www.example.com/PO1">
   <shipTo >
  </shipTo>
</purchaseOrder>

"unqualified" means that all the local elements mustn’t be qualified, or to say, must have no namespace.

po.xsd as:

<schema xmlns="http://www.w3.org/2001/XMLSchema"
        xmlns:po="http://www.example.com/PO1"
        targetNamespace="http://www.example.com/PO1"
        elementFormDefault="unqualified"
        attributeFormDefault="unqualified">
  <element name="purchaseOrder" type="po:PurchaseOrderType"/>
  <complexType name="PurchaseOrderType">
    <sequence>
      <element name="shipTo"    type="string"/>
    </sequence>
  </complexType>
</schema>

Corresponding xml can be:

<?xml version="1.0"?>
<purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po.xsd"
xmlns="http://www.example.com/PO1">
   <shipTo xmlns="">
  </shipTo>
</purchaseOrder>

<?xml version="1.0"?>
<prx:purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po.xsd"
xmlns:prx="http://www.example.com/PO1">
   <shipTo>
  </shipTo>
</prx:purchaseOrder>

You can test the above examples in the XML-editor, such as JDeveloper, validating the grammar automatically.

Also, the references below can be useful if you want the further understanding.

And Recently, My colleague Corey clarify this concept too in another blog, whose URL is elementFormDefault attribute, attributeFormDefault attribute and form attribute in XML Schema , also referencing to the above documents:)

Common Causes and Solutions for ORA-1157

SQL> startup

ORACLE instance started.

Total System Global Area 202868968 bytes

Fixed Size 731368 bytes

Variable Size 167772160 bytes

Database Buffers 33554432 bytes

Redo Buffers 811008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: ‘/opt/oracle/product/9.2/dbs/datafile.dbf’

1. The datafile does exist, but Oracle cannot find it.

2. The datafile does not exist or unsuable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore.

a. Recreate the tablespace that the datafile belongs to.

1. If the database is down, mount it. SVRMGR> STARTUP MOUNT PFILE=’<location_of_pfile>’;
2. Offline drop the datafile. SVRMGR> ALTER DATABASE DATAFILE ‘<full_path_file_name>’ OFFLINE DROP;
3. If the database is at mount point, open it. SVRMGR> ALTER DATABASE OPEN;
4. Drop the user tablespace. SVRMGR> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
Note: The users can stop with this step if they do not want the tablespace anymore in the database.
5. Recreate the tablespace. SVRMGR> CREATE TABLESPACE <tablespace_name> DATAFILE ‘<datafile_full_path_name’> SIZE <required_size>;
6. Recreate all the previously existing objects in the tablespace.

b. Recover the datafile using normal recovery procedures.

     1. Restore the lost file from a backup.
     2. If the database is down, mount it.
      SQL>STARTUP MOUNT PFILE=<location_of_pfile>;
     3. Issue the following query:
        SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#,
                FIRST_CHANGE#
                FROM V$LOG V1, V$LOGFILE V2
                WHERE V1.GROUP# = V2.GROUP# ;
        This will list all your online redolog files and their respective sequence and first change numbers.
    4. issue the query:
          SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
    This query will list the files needing to recover.
   5. use rman to restore and recover the datafile.

3. Operating Systems (OS) Tempfiles missing:

      When using TEMPORARY tablespaces with tempfiles, the absence of the tempfile at the OS level can cause ORA-1157.
      Since Oracle does not checkpoint tempfiles, the database can be opened even with missing tempfiles.
      The solution in this case would be to drop the logical tempfile and add a new one.

For example:
     SQL> select * from dba_objects order by object_name;
           select * from dba_objects order by object_name;
               *
     ERROR at line 1:
     ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
     ORA-01110: data file 1026: ‘/Oracle/oradata/V901/temp2_01.tmp’

     SQL> alter database tempfile ‘/Oracle/oradata/V901/temp2_01.tmp’ drop;
     SQL> select tablespace_name, file_name from dba_temp_files;
     SQL> alter tablespace temp2
          add tempfile ‘/Oracle/oradata/V901/temp2_01.tmp’ size 5m;

The article is mainly from metalink Note:184327.1

table data block corruption simulation

1. corrupt the data file corresponding to the table
For this purpose, a dd command is generate from the Oracle views.

set heading off
set lines 113
SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
       to_number(S.HEADER_BLOCK + 1) || ' << EOF',
       'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
       'EOF'
  FROM DBA_SEGMENTS S, dba_data_files f, dba_tables t
 WHERE f.tablespace_name = t.tablespace_name
   and S.SEGMENT_NAME = t.table_name
   and t.table_name = 'BTEST'
   and S.OWNER = t.owner
   and t.owner = 'SYS';

After executing this dd comand, the storage space of table sys.btest will be corrupted.

2. flush the buffer cache, so the next querying statement will read data from storage.

alter system flush buffer_cache;

3. select should be error.

sys@ORCL> select * from btest;
select * from btest
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 16, block # 12)
ORA-01110: data file 16: '/opt/oracle/oradata/orcl/ORCL/datafile/o1_mf_broken_2vjk5vss_.dbf'

4. After shutdown and startup, the database functions normal, but the btest will be unavailable.

OEM Database Control Exceptions

There are quite a few errors which you perphase meet when you begin to use EM in Windows. Such as:
"Agent process exited abnormally during initialization" in system event log,
"java.lang.Exception: Exception in sending Request :: null" in OEM Database Control Database Instance home page
"status pending" in OEM Database Control Database Instance home page
"Io exception: Unknown host specified" in OEM Database Control Database Login

One root cause was an incorrect time zone autoconfigured by the Database Control service.

Below info from http://forums.oracle.com/forums/thread.jspa?messageID=1374397

On Windows 2000 / XP, for the OEM single instance control, (with no grid agent) the service OracleDBConsole<SID> runs
both a control service and an agent. After creation of the database using the Database Configuration Assistant 10.2.0.2,
the <ORACLE_HOME/><hostname_SID>/sysman/config/emd.properties file was created with no agentTZRegion parameter.
At the first startup of the agent, it recognised this and added a line "agentTZRegion=GMT" (the correct time zone
would have been Australia/Melbourne). This action is reported in <ORACLE_HOME>/<hostname _SID>/emdb.nohup:
----- Fri Apr 28 17:57:51 2006::Console Launched with PID 3348 at time Fri Apr 28 17:57:51 2006 -----
----- Fri Apr 28 17:59:26 2006::Property 'agentTZRegion' is missing from C:\oracle\product\10.2.0\db_1\
    AMAZON.RUBICON.COM.AU_DAP1/sysman/config/emd.properties. This is normal when the agent is started for
    the very first time.Updating it... -----
----- Fri Apr 28 17:59:26 2006::C:\oracle\product\10.2.0\db_1\AMAZON.RUBICON.COM.AU_DAP1/sysman/config/emd.properties
    copied to C:\oracle\product\10.2.0\db_1\AMAZON.RUBICON.COM.AU_DAP1/sysman/config/emd.properties.2006-04-28-17-59-26
    while updating the property 'agentTZRegion' -----
----- Fri Apr 28 17:59:35 2006::An agentTZregion of 'GMT' is installed in
    C:\oracle\product\10.2.0\db_1\AMAZON.RUBICON.COM.AU_DAP1/sysman/config/emd.properties. -----
----- Fri Apr 28 17:59:36 2006::The agentTZRegion value in C:\oracle\product\10.2.0\db_1\AMAZON.RUBICON.COM.AU_DAP1/sysman/config/emd.properties
     is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting
     has not changed since the last start of the agent.If you modified the timezone setting in the environment, please stop
     the agent and exectute 'emctl resetTZ agent' and also execute the script 'mgmt_target.set_agent_tzrgn' to get the value propagated to repository -----
As you can see, the agent adds the agentTZRegion parameter and immediately recognises that it has set the wrong value.
(Possibly this could be avoided by setting a system TZ environment variable before creating the database.) The agent fails,
and the service ends up with a java process running, but the service not properly started, so you can't restart it via the services console.

"emctl status dbconsole" says it is not running.
Once this has happened,future startups of the OracleDBConsole<SID> report the last of the errors (the one starting "::The agentTZRegion value in")
and again the agent fails.

The solution is
1. Kill the java process which is the console portion of OracleDBConsole<SID>
2. Edit the emd.properties file to have the correct timezone
3. Reboot or restart the OracleDBConsole<SID>
Note that deleting and recreating the service via "emca -deconfig dbcontrol db -repos drop" and "emca -config dbcontrol db -repos create"
does not cure the problem - it again creates an emd.properties file with no agentTZRegion value.

The timezone supported can be found at: %ORACLE_HOME%\sysman\admin\supportedtmz.lst

Another note when installing Oracle in Linux is that /etc/hosts must be checked carefully, or else the emca will be failed, left the problem confused. It should be like below.

   1: 127.0.0.1 localhost.localdomain localhost    2: <IP> <hostname>

and then the emca will recognize the hostname as <hostname>, not localhost.localdomain etc, which cause the OEM can’t display correctly.

Categories