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
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 