Download Oracle VM

You can download OracleVM from http://edelivery.oracle.com/oraclevm

Load XML into Database

Following is the extract from Oracle SQL & PLSQL Forum (Courtesy Billy Verreynne)

You can store XML as CLOB or XMLType objects.

You can easily load XML files into the database as CLOBs using the DBMS_LOB PL/SQL package - it has a load-from-file procedural call that can be made. Very easy to use.


drop table xml_clob;
drop table my_objects;
-- create a table to store a large XML doc in raw format
create table xml_clob ( doc CLOB )
/
-- create such a raw XML document, using the ALL_OBJECTS -- table
DECLARE
CURSOR curxml IS
SELECT xmlelement("ORA_OBJECT", xmlforest(object_id AS "ID", owner AS "OWNER", object_type AS "TYPE", object_name AS "NAME")) AS "XML_DATA"
FROM all_objects
WHERE rownum < 100001;

xml XMLTYPE;
c CLOB;
BEGIN
dbms_lob.createtemporary(c, TRUE);

OPEN curxml;

LOOP
FETCH curxml
INTO xml;

EXIT WHEN curxml%NOTFOUND;
DBMS_LOB.Append( c, xml.GetCLOBVal() );
-- dbms_lob.writeappend(c, LENGTH(xml.getclobval()), xml.getclobval());
END LOOP;

CLOSE curxml;

INSERT INTO xml_clob
VALUES ('' || c || ''
);

COMMIT;
END;

/
CREATE TABLE my_objects (obj_id NUMBER, obj_owner VARCHAR2(30), obj_type VARCHAR2(19), obj_name VARCHAR2(30) )

/
-- call proc with a raw XML clob and have it insert the contents
-- into a relational table
CREATE OR REPLACE PROCEDURE insertobjects(
xmldoc IN OUT NOCOPY CLOB
) IS
BEGIN
INSERT INTO my_objects
SELECT extractvalue(VALUE(xml), 'ORA_OBJECT/ID')
, extractvalue(VALUE(xml), 'ORA_OBJECT/OWNER')
, extractvalue(VALUE(xml), 'ORA_OBJECT/TYPE')
, extractvalue(VALUE(xml), 'ORA_OBJECT/NAME')
FROM TABLE(SELECT xmlsequence(EXTRACT(XMLTYPE(xmldoc), 'DATASET/*'))
FROM dual) xml;

COMMIT;
END;
/
-- the test (processing 10,000 XML rows):
set timing on
declare c CLOB;
BEGIN
SELECT doc
INTO c
FROM xml_clob;

insertobjects(c);
END;
/

set timing offselect count(*) from my_objects;
select * from my_objects where rownum < 4;
--eof


Extracting from XML Datatype



WITH XMLDATA AS
(SELECT xmltype(
'<?xml version="1.0" encoding="utf-8"?>
<transaction type="IN">
<customer id="Cust001"/>
<product>
<product_details>
<orderno>ORD000001</orderno>
<orderlineno>000010</orderlineno>
<product_code>PRD</product_code>
<product_content>
<URL/>
<product_one>
<textType>1</textType>
<text/>
<text1>Surrounding Areas</text1>
<iconCreditCards boolean="false"/>
<messagingEmailAddress>myemailAddress@email.com</messagingEmailAddress>
</product_one>
</product_content>
<live_date>2007-02-21</live_date>
<term_date>2008-03-05</term_date>
<proposed_start_date>2007-03-07</proposed_start_date>
</product_details>
<classification code="00001">
<branch bf_id="0001">
<suppress_address boolean="false"/>
<branch_override_details>
<telephone_number prefix="Tel">01234 567890</telephone_number>
</branch_override_details>
<organisation_name>SIGNAL</organisation_name>
<address_line1>Address Line 1, Some Road</address_line1>
<address_line2/>
<address_line3/>
<locality>London</locality>
<post_town>PostTown</post_town>
<postcode>PO1 BO1</postcode>
<telephone_number>02314 555000</telephone_number>
<telephone_number_2 prefix="Mobile">07834 123123</telephone_number_2>
</branch>
<branch bf_id="0002">
<suppress_address boolean="true"/>
<branch_override_details>
<telephone_number prefix="Tel">01234 567891</telephone_number>
</branch_override_details>
<organisation_name>SIGNAL</organisation_name>
<address_line1>Adress Line 1, Street Road</address_line1>
<address_line2/>
<address_line3/>
<locality>Best Locality</locality>
<post_town>Wonderful Town</post_town>
<postcode>PO1 CD2</postcode>
<telephone_number>01189 118118</telephone_number>
<telephone_number_2 prefix="Mobile">07834 123456</telephone_number_2>
</branch>
</classification>
</product>
</transaction>'
') XML FROM DUAL)
SELECT
extractValue(xml,'/transaction/customer@id') as CustomerID,
extractValue(xml,'/transaction/product/product_details/live_date') as startDate,
extractValue(xml,'/transaction/product/product_details/orderno') as orderno,
extractValue(xml,'/transaction/product/product_details/product_content/product_one/messagingEmailAddress') as Email,
extractValue(value(d),'/branch@bf_id') branch,
extractValue(value(d),'/branch/suppress_address@boolean') AddressSuppress,
extractValue(value(d),'/branch/branch_override_details/telephone_number@prefix') TelPrefix,
extractValue(value(d),'/branch/branch_override_details/telephone_number') TelNo
FROM XMLDATA x,
table(xmlsequence(extract(x.xml,'/transaction/product//classification/branch'))) d
/

---------- ---------- ---------- -------------------------- ------ --------------- ----------- -------------
CUSTOMERID STARTDATE ORDERNO EMAIL BRANCH ADDRESSSUPPRESS TELPREFIX TELNO
---------- ---------- ---------- -------------------------- ------ --------------- ----------- -------------
Cust001 2007-02-21 ORD000001 myemailAddress@email.com 0001 false Tel 01234 567890
Cust001 2007-02-21 ORD000001 myemailAddress@email.com 0002 true Tel 01234 567891
---------- ---------- ---------- -------------------------- ------ --------------- ----------- -------------



PIVOT Table using XML

Rows to Column

I want to get output of multiple rows into single variable seperated by comma. How can I achieve that. There is also requirement of using dynamic sql as table and column will be known at runtime only.

Here is the example :

select rtrim(dbms_xmlgen.getxmltype('select empno '','' e from EMP WHERE DEPTNO = '&p_deptno).extract('//text()'),',') x
from dual;

Enter value for p_deptno: 10

X
--------------
7782,7839,7934



Columns to Rows
I've got comma seperated values in single row, now I need the output in multiple rows. How can I get this?






EMPNO
------
7782
7839
7934

3 rows selected.



OCP Oracle Database 11g : New Features for Administrators Exam Guide

Topics related to above exam follows.....

For now... here are some of the cool new features of 11g

Case Sensitive Passwords

Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.


Automatic Stats Collection on Tables

As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.


Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary. This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these views to identify tables with stale statistics.

Readonly Tables

To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.

The following example places the EMP table in read-only mode:

ALTER TABLE EMP READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE EMP READ WRITE;

Invisible Indexes

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.

Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Altering Indexes

Use temporary index structures for certain operations or modules of an application without affecting the overall application.


To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:

CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX ind_emp_dept VISIBLE;

To make a visible index invisible, issue this statement:

ALTER INDEX ind_emp_dept INVISIBLE;

To check whether an index is visible or invisible, query the dictionary views


USER[ALL][DBA]_INDEXES


For example, to determine if above index is invisible, issue the following query:

SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';

INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE

Partitioning Limitations

I was trying to reverse engineer the schema using Visual Paradigm for UML 6.0 Professional Edition and identified that it doesn't work with partitioned tables. However, this is a restriction / bug in Visual Paradigm.
To list out few limitations of Partitioning :

• In 9i, you can not create index organized table with List Partitioning (however in 10g, you can)
• In 9i, you can not Fast Refresh materialized views after truncating partitions on base table. You have to have range or composite (implies range) especially in this case. But In 10g, you can i.e. range OR list (or composite)
• You have to disable referential integrity between partitioned tables if you want to drop old partitions. Even if you drop child partition first, followed by its parent table partition, currently results in Oracle error ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
• We can not move partitions to tablespace with different block size using ALTER TABLE MOVE command
  

Drop partitions

Back to basics...

"I want to drop the partitions however I dont want to rebuild the local indexes. I have enforced referential integrity on tables.. How can I achive this?"

1. Use deferrable FK constraints
2. disable the constraints before dropping partitions
3. Enable constraint with 'NOVALIDATE' clause after dropping partitions.

But what if I have indexes on FK constraints?
You can use 'KEEP INDEX' caluse at the time of disabling the constraints.