Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 3, 2007

Oracle Native Types Part 1

Filed under: Oracle How To — H.Tonguç Yılmaz @ 7:34 am

1- ANYTYPE, ANYDATA and ANYDATASET

The Any types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These datatypes let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. These types have OCI and PL/SQL interfaces for construction and access.

ANYTYPE : This type can contain a type description of any named SQL type or unnamed transient type.

ANYDATA : This type contains an instance of a given type, with data, plus a description of the type. ANYDATA can be used as a table column datatype and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types.

ANYDATASET : This type contains a description of a given type plus a set of data instances of that type. ANYDATASET can be used as a procedure parameter datatype where such flexibility is needed. The values of the data instances can be of SQL built-in types as well as user-defined types.

Also in $ORACLE_HOME/rdbms/admin/dbmsany.sql you can find the package DBMS_TYPES and types SYS.AnyData etc.


-- DROP TABLE tst_tbl purge ;
-- DROP TYPE tst_typ ;
CREATE OR REPLACE TYPE tst_typ AS OBJECT (mycol sys.anyData ) ;
/

CREATE TABLE tst_tbl OF tst_typ;

INSERT INTO tst_tbl
VALUES (sys.anyData.convertNumber(5));

INSERT INTO tst_tbl
VALUES (sys.anyData.convertDate(SYSDATE));

INSERT INTO tst_tbl
VALUES (sys.anyData.convertVarchar2(’hello world’));

COMMIT;

col typename format a20
SELECT mytab.mycol.gettypeName() typeName
FROM tst_tbl mytab;

TYPENAME
——————–
SYS.NUMBER
SYS.DATE
SYS.VARCHAR2

CREATE OR REPLACE FUNCTION getData(p_x IN sys.anyData)
RETURN VARCHAR2 IS
l_num      NUMBER;
l_date     DATE;
l_varchar2 VARCHAR2(4000);
BEGIN
CASE p_x.gettypeName
WHEN 'SYS.NUMBER' THEN
IF (p_x.getNumber(l_num) = dbms_types.success) THEN
l_varchar2 := l_num;
END IF;
WHEN 'SYS.DATE' THEN
IF (p_x.getDate(l_date) = dbms_types.success) THEN
l_varchar2 := l_date;
END IF;
WHEN 'SYS.VARCHAR2' THEN
IF (p_x.getVarchar2(l_varchar2) = dbms_types.success) THEN
NULL;
END IF;
ELSE
l_varchar2 := '** unknown **';
END CASE;
RETURN l_varchar2;
END getData;
/

col getdata format a20
SELECT getData(mycol) GETDATA
FROM tst_tbl ;

GETDATA
—————–
5
05/12/2006
hello world

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)
http://www.psoug.org/reference/anydata.html
http://mennan.kagitkalem.com/ANYDATATypeInOracle.aspx

2- Binary_double vs. Number in compute intensive processes

Oracle provides two numeric datatypes exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. They support all of the basic functionality provided by the NUMBER datatype. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.

BINARY_FLOAT and BINARY_DOUBLE are approximate numeric datatypes. They store approximate representations of decimal values, rather than exact representations. For example, the value 0.1 cannot be exactly represented by either BINARY_DOUBLE or BINARY_FLOAT. They are frequently used for scientific computations. Their behavior is similar to the datatypes FLOAT and DOUBLE in Java and XMLSchema.

BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype. Each BINARY_FLOAT value requires 5 bytes, including a length byte. BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype. Each BINARY_DOUBLE value requires 9 bytes, including a length byte. BINARY_DOUBLE and BINARY_FLOAT implement most of the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985 (IEEE754).


set serveroutput on
declare

function pi_with_number return number as
subtype my_number is NUMBER;
last_pi my_number := 0;
delta   my_number := 0.000001;
pi      my_number := 1;
denom   my_number := 3;
oper    my_number := -1;
negone  my_number := -1;
two     my_number := 2;
begin
loop
last_pi := pi;
pi := pi + oper * 1/denom;
exit when (abs(last_pi-pi) <= delta );
denom := denom + two;
oper := oper * negone;
end loop;
return pi * 4;
end;

function pi_with_binary_double return number as
subtype my_number is BINARY_DOUBLE;
last_pi my_number := 0;
delta   my_number := 0.000001;
pi      my_number := 1;
denom   my_number := 3;
oper    my_number := -1;
negone  my_number := -1;
two     my_number := 2;
begin
loop
last_pi := pi;
pi := pi + oper * 1/denom;
exit when (abs(last_pi-pi) <= delta );
denom := denom + two;
oper := oper * negone;
end loop;
return pi * 4;
end;

begin
runStats_pkg.rs_start;
dbms_output.put_line( pi_with_number );
runStats_pkg.rs_middle;
dbms_output.put_line( pi_with_binary_double );
runStats_pkg.rs_stop(1000);
end;
/

3,14159465358579324446263938327350288021
3,1415946535856922

Run1 ran in 103 hsecs
Run2 ran in 40 hsecs
run 1 ran in 257,5% of the time

Name                                  Run1        Run2        Diff
LATCH.shared pool simulator          1,167           1      -1,166
STAT...session uga memory           65,464           0     -65,464
STAT...session pga memory           65,536           0     -65,536
STAT...session uga memory max      261,964      65,464    -196,500

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
3,012         901      -2,111    334.30%

PL/SQL procedure successfully completed.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
Oracle® Database PL/SQL User’s Guide and Reference 10g Release 2 (10.2)
http://asktom.oracle.com/tkyte/runstats.html

3- XMLType and XDB

With Oracle9iR2 XML and SQL worlds integrated in Oracle. This Oracle-supplied native type(XMLType) can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. XPath is another standard developed by the W3C committee to traverse XML documents. Oracle XMLType functions support many W3C XPath expressions. Oracle also provides a set of SQL functions and PL/SQL packages to create XMLType values from existing relational or object-relational data.

XMLType is a system-defined type, so you can use it as an argument of a function or as the datatype of a table or view column. You can also create tables and views of XMLType. When you create an XMLType column in a table, you can choose to store the XML data in a CLOB column or object relationally.

You can also register the schema (using the DBMS_XMLSCHEMA package) and create a table or column conforming to the registered schema. In this case Oracle stores the XML data in underlying object-relational columns by default, but you can specify storage in a CLOB column even for schema-based data.

createXML: creates an XMLtype instance from a string or CLOB
existsNode: returns 1 if the given XPath expression returns any result nodes
extract: applies an XPath expression over the XML data to return a XMLType instance containing the resultant fragment
isFragment: returns 1 if the XMLtype contains a fragment
getCLOBval, getStringval, getNumberval: returns an XML document or fragment as CLOB, string or number

You can verify that XML DB has been installed by simply running the SQL below:


select comp_name from dba_registry where comp_name like '%XML%';

COMP_NAME
--------------------
Oracle XML Database

The XML DB schema and its objects can also be checked to see that Oracle XML DB has been installed.

Here are some XML data store and query examples;


DROP TABLE xwarehouses PURGE ;
CREATE TABLE xwarehouses OF XMLTYPE;

INSERT INTO xwarehouses VALUES
(xmltype('<?xml version="1.0"?>
<Warehouse>
<WarehouseId>1</WarehouseId>
<WarehouseName>Southlake, Texas</WarehouseName>
<Building>Owned</Building>
<Area>25000</Area>
<Docks>2</Docks>
<DockType>Rear load</DockType>
<WaterAccess>true</WaterAccess>
<RailAccess>N</RailAccess>
<Parking>Street</Parking>
<VClearance>10</VClearance>
</Warehouse>'));

set pagesize 0
select e.getstringval() from xwarehouses e;

<?xml version="1.0"?>
<Warehouse>
<WarehouseId>1</WarehouseId>
<WarehouseName>Southlake, Texas</WarehouseName>
<Building>Owned</Building>
<Area>25000</Area>
<Docks>2</Docks>
<DockType>Rear load</DockType>
<WaterAccess>true</WaterAccess>
<RailAccess>N</RailAccess>
<Parking>Street</Parking>
<VClearance>10</VClearance>
</Warehouse>

SELECT e.getClobVal() FROM xwarehouses e;

E.GETCLOBVAL()
---------------------------------

<?xml version="1.0"?>
<Warehouse>
<WarehouseId>1</WarehouseId>
<Wareho..

-- Creating an XMLType View: In some cases you may have an object-relational table upon which you would like to build an XMLType view.
CREATE TABLE warehouse_table
(
WarehouseID       NUMBER,
Area              NUMBER,
Docks             NUMBER,
DockType          VARCHAR2(100),
WaterAccess       VARCHAR2(10),
RailAccess        VARCHAR2(10),
Parking           VARCHAR2(20),
VClearance        NUMBER
);

INSERT INTO warehouse_table
VALUES(5, 103000,3,'Side Load','false','true','Lot',15);

CREATE VIEW warehouse_view OF XMLTYPE
-- XMLSCHEMA "http://www.oracle.com/xwarehouses.xsd"
--    ELEMENT "Warehouse"
WITH OBJECT ID
(extract(OBJECT_VALUE, '/Warehouse/Area/text()').getnumberval())
AS SELECT XMLELEMENT("Warehouse",
XMLFOREST(WarehouseID as "Building",
area as "Area",
docks as "Docks",
docktype as "DockType",
wateraccess as "WaterAccess",
railaccess as "RailAccess",
parking as "Parking",
VClearance as "VClearance"))
FROM warehouse_table;

SELECT VALUE(e) FROM warehouse_view e;

VALUE(E)
--------------------------------------------------------------------------------

<Warehouse><Building>5</Building><Area>103000</Area><Docks>3</Docks><DockType>Si..

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Oracle® Database Concepts 10g Release 2 (10.2) Chapter 26 Native Datatypes
Oracle® XML DB Developer's Guide 10g Release 2 (10.2)
Steven Feuerstein presentation - NINE NEW and NIFTY NUANCES OF ORACLE 9i PL-SQL
"Getting into SQL/XML" OTN article by Tim Quinlan

Note Related : How to give datatype to columns with create table as select?


drop table test purge;
create table test as
select sysdate dcol, user vcol1, ‘ ‘ vcol2, ‘ ‘ vcol3, 0 ncol1, 5.5 ncol2
from dual ;

describe test

Name                                      Null?    Type
----------------------------------------- -------- ------------

DCOL                                               DATE
VCOL1                                              VARCHAR2(30)
VCOL2                                              CHAR(1)
VCOL3                                              CHAR(1)
NCOL1                                              NUMBER
NCOL2                                              NUMBER

one solution may be using CAST function, CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value;


drop table test purge;
create table test as
select cast( null as varchar2(10) ) a,
cast( null as date ) b,
cast( null as number(5,2) ) c
from dual;

describe test

Name                                      Null?    Type
----------------------------------------- -------- ------------

A                                                  VARCHAR2(10)
B                                                  DATE
C                                                  NUMBER(5,2)

Continue reading with Part 2

3 Comments »

  1. […] There are more than one NUMBER and Character Native Type Series Like Other Character Datatypes, Other Numeric Datatypes, LOB Datatypes, RAW and LONG RAW Datatypes, […]

    Pingback by H.Tonguç YILMAZ Blog » Last warning, I moved my blog - Son uyarı, taşındım — January 3, 2007 @ 7:51 am | Reply

  2. […] Part 1 we […]

    Pingback by Oracle Native Types Part 2 « H.Tonguç YILMAZ Oracle Blog — March 31, 2007 @ 8:30 pm | Reply

  3. […] the IEEE 754 standard so a CPU can directly process them and they are fixed-length. Please check Binary_double vs. Number in compute intensive processes post for an example how this decision may affect the […]

    Pingback by On Troubleshooting Oracle Performance book by Christian Antognini « H.Tonguç Yılmaz - Oracle Blog — February 22, 2009 @ 9:02 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.