OracleTips

Ref Cursor - 1

2009-07-01T14:45:00.000-07:00

Is ref cursor the only light at end of the tunnel?

Consider a requirement: Based on the given condition, the base table of the query will change whereas select statement will remain intact.

Say for example we need to select
Account number,
Dr_Cr ,
Currency and
Amount

From Account_Posting_USD table when condition Local_Currency is True or
From Account_Posting table when condition Local_Currency is False.

In Static cursor I cannot parameterize the table name but still this
is a simple requirement if you are aware of dynamic cursor/REF cursor!!! Isn’t it?

So let’s implement this with ref cursor.

CREATE TABLE Account_Posting (
Posting_Txn_Ref INTEGER,
Account_Number NUMBER,
Dr_Cr VARCHAR2(1),
Currency VARCHAR2(3),
Amount NUMBER,
CONSTRAINT Account_Posting_PK PRIMARY KEY
(Posting_Txn_Ref));

CREATE TABLE Account_Posting as SELECT * FROM Account_Posting_Usd;

CREATE OR REPLACE PROCEDURE Accounting_Refcur_Example(i_Local_Ccy BOOLEAN) AS
Ret_Val SYS_REFCURSOR;
l_Val Account_Posting%ROWTYPE;
BEGIN
-- IF local Currency is true means select from Account_Posting_Usd table.
IF i_Local_Ccy THEN
OPEN Ret_Val FOR
SELECT Account_Number,
Dr_Cr,
Currency ,
Amount
FROM Account_Posting_Usd;
ELSE
OPEN Ret_Val FOR
SELECT Account_Number,
Dr_Cr,
Currency ,
Amount
FROM Account_Posting;
END IF;
LOOP
FETCH Ret_Val INTO l_Val;
EXIT WHEN Ret_Val%NOTFOUND;
Dbms_Output.Put_line(l_Val.Account_Number);
END LOOP;
CLOSE Ret_Val;
END Accounting_Refcur_Example;

Problem at hand solved. But is this implementation more efficient?

While looking behind the hoods of PL/SQL ref cursors are just pointers to the cursors (Query result set). They are not parsed at “Compile Time”.

More importantly the queries are not parsed and stored in Shared Pool. This means
each time the query will under go hard parse (plan and row data generation will occur again).

For more details on hard/soft parse refer :
http://asktom.oracle.com

Question for your thought:
1. What are the other ways to implement the same requirement?

Word of Caution:
Though this performance burden is there on ref cursor, it is advisable to use ref cursor to pass between procedures, between client/server applications etc..

XML in Oracle PL/SQL - Part-1

2009-06-26T14:15:00.000-07:00

To keep abreast with new technologies oracle now integrated XML functionalities into PL/SQL. This articles describes some feautures.

XML Type:

Oracle 9i release 1 introduced a new datatype , XMLType, to facilitate native handling of XML data in database.

Use XMLType anytime you want to use the database as a persistent storage of XML.

XML Type data can be stored in two ways:

1. In Large Objects (LOBs) – LOB storage maintains content fidelity,
also called document fidelity. The original XML is preserved, including whitespace. An entire XML document is stored as a whole in a LOB. For non-schema-based storage, XMLType offers a Character Large Object (CLOB) storage option.

Note: Use this option only when your application requires content fidelity, since this option provides mediocre performance for DML and limited accessibility to SQL features.

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.

2. In Structured storage (in tables and views) – Structured storage maintains DOM (Document Object Model) fidelity.

Structured storage of XML documents is based on decomposing the content of the document into a set of SQL objects. These SQL objects are based on the SQL 1999 Type framework. When an XML schema is registered with Oracle XML DB, the required SQL type definitions are automatically generated from the XML schema.
A SQL type definition is generated from each complexType defined by the XML schema. Each element or attribute defined by the complexType becomes a SQL attribute in the corresponding SQL type. Oracle XML DB automatically maps the 47 scalar data types defined by the XML Schema Recommendation to the 19 scalar datatypes supported by SQL. A varray type is generated for each element and this can occur multiple times.

The generated SQL types allow XML content, compliant with the XML schema, to be decomposed and stored in the database as a set of objects without any loss of information. When the document is ingested the constructs defined by the XML schema are mapped directly to the equivalent SQL types. This allows Oracle XML DB to leverage the full power of Oracle Database when managing XML and can lead to significant reductions in the amount of space required to store the document. It can also reduce the amount of memory required to query and update XML content.

Annotating an XML schema allows control over the naming of the SQL objects and attributes created. Annotations can also be used to override the default mapping between the XML schema data types and SQL data types and to specify which table should be used to store the data.

Registering XML Schema:

One of the very basic requirment will be to register XML Schema to validate XML docs.

Oracle PL/SQL Package Dbms_Xmlschema have set of utilities to play around XML schema. Procedure RegisterSchema need to be used to register XML Schema.

Example:
The following example register a schema namely mt202.xsd (Xml Schema document) into
oracle database using Dbms_Xmlschema.RegisterSchema procedure.

DECLARE
doc VARCHAR2(2000) :=
'< schema
targetNamespace="http://xmlns.oracle.com/xdb/schemas/Scott/mt202.xsd"
xmlns="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
< element name ="mt202">
< complexType >
< sequence>
< element name="tag_20" type="integer" fixed="20"/>
< element name="txnref" >
< simpleType>
< restriction base="string">
< length value="16"/>
< /restriction>
< /simpleType>
< /element>
< element name="tag_21" type="integer" fixed="21"/>
< element name="relref" maxOccurs="1">
< simpleType>
< restriction base="string">
< length value="16"/>
< /restriction>
< /simpleType>
</ element>
</ sequence>
</ complexType>
< /element>
</schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('mt202.xsd', doc);
END;
/

In the above example Variable doc carries complete xsd value. To know more about what is Xsd and syntaxes please refer W3Schools tutorial @
http://www.w3schools.com/schema/schema_intro.asp

Deleting the registered XML Schema:

BEGIN
Dbms_XmlSchema.DeleteSchema('mt202.xsd',Dbms_Xmlschema.DELETE_CASCADE_FORCE);
END;

PL/SQL Questions

2008-03-27T12:40:00.000-07:00

PL/SQL:

1.what is the difference between char & varchar2, number & integer, binary int& pls int
2.usage of dbms_job package
3.usage of dbms_keep package
4.usage of dbms_pipe package
5.which cursor is efficient explicit or implicit?
6.how to built implicit multirow query?
7.where is the result set of the cursor stored?
8.what are the operations & results involved with the cursors?
9.what is dynamic cursor?
10.what is cursor object?
11.what is cursor expressions?
12.what is syscursor,sysrefcursor?
13.what is NOCOPY compiler hint?
14.what is PL/SQL table?
15.what is bulk collect and set back with limit clause?
16.what is NDS? Native Dynamic Sql?
17.how will you use NDS for multirow query?
18.use of auth_id and importance of auth_id in NDS?
19.bind Vs concatenation?
20.sql query Vs pl/sql block while binding in NDS?
21.What will happen if unexpected exception raised in called block and not handled?
22.usage of returning clause in dml statements – during lob dml, bulk dml?
23.exception handling in dml – transaction not rolled back only variables !!
24.dml of records – and restrictions?
25.bulk dml and exception handling – usage save exceptions, bulk_errors, sql%bulk_exceptions.count,error_index , usage of sqlerrm(-1*error_code)?
26.Discuss about triggers.
27.Pragma restrict references
28.what is the difference between procedures and functions?
29.what is the advantage of using package?
30.Dependency management
31.what is format_error_backuptrace in exception handling ?
new feature in oracle 10g with dbms_utility package.

SQL Questions

2008-03-27T12:39:00.000-07:00

SQL:
1. What is the difference between in & exists?
2. What is the difference between delete & truncate?
3. what is constrained and unconstrained datatype?
4. what is the default storage of native datatypes?
5. What will happen when u exit out of sqlplus without commit or rollback?
6. What will happen if u executed series of dml & then a invalid ddl ?
7. what is temporary table?
8. what is pctfree and pctused parameters?
9. what is row chaining & row migration?
10. What is consistent get?
11. Use of analytic functions?
12. Use of advanced grouping functions rollup & cube?
13. What is scalar subquery and futures?
14. What are the different partitioning methods and 10g enhancements?
15. What is local index & global index?
16. what is Cluster index
17. what is merge statement and usage of delete clause?
18. what is multi table insert?
19. what is parallel and direct insert?
20. how to use sql loader? What is direct load in sql loader?
21. What is materialized view? Usage of dbms_mview?
22. What are the refresh option and query rewrite in mview?
23.what is difference between remainder and mod
24.what is index_ff hints -- index fast full scan
25.What is sql injection?

All About Joins

2008-03-27T12:38:00.001-07:00

What is SEMI JOIN?
Co-related sub query with exists operator is called semi join. Optimizer may choose any one of the hash or merge semi join according to the need.

What is horizontal join & vertical join?
Queries with set operator UNION,UNION ALL, MINUS & INTERSECT are called horizontal join. Normal equi joins are called vertical joins since they compare column wise.

What is self join:
Queries that join the same tables in the join condition is known as self join.
What is equii join:
This is determined by join condition, when join condition relates two tables by equating the columns from the tables it is equi-join. If the join condition relates two tables by an operator other than equality it is non-equi-join.

What is CROSS JOIN?

The cross join is new syntax to tell explicitly we need Cartesian product.
Select * from A cross join B;

Inner join, outer join:
AN inner join returns the row that satisfy the join condition. In outer join one of the table is optional, the operator (+) is used in the join condition in the where clause following a field name from the optional table.

Some restrictions on outer join:
1. outer join operator can appear on only one side of an expression in the join condition.
2. If join involves more than two tables the one table can’t be outer joined with more than one another table in the query. But this can be achieved by inline view eg:
3. outer join condition may not use the IN operator. Error ora-01719
4. cannot be combined with OR operator.Error ora-01719
5. cannot compare subquery. Error ora-01799

FULL OUTER JOINS – get results from two outer join and do UNION.
Now ANSI sql syntax also available

What is ANTI JOIN?
Outer join with coln name is null check is called anti join because select result is exactly opposite to join result set. (can be achieved by minus operation also)

JOIN VIEW & KEY-PRESERVED TABLES:

A DML statement on a join view can modify only one base table of the view, so a join view must also have one key preserved table to be modified.
Every primary key or unique key value in the base table must also be unique in the result set of the join.

Select * from user_updatable_columns where table_name=’V_RTLR_EMP’;

ACID Properties

2008-03-27T12:37:00.000-07:00

1. Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. The transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won't be debited if the other is not credited as well.

2. Consistency refers to the database being in a legal state when the transaction begins and when it ends. This means that a transaction can't break the rules, or integrity constraints, of the database. If an integrity constraint states that all accounts must have a positive balance, then any transaction violating this rule will be aborted.

3. Isolation refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; a bank manager can see the transferred funds on one account or the other, but never on both -- even if he ran his query while the transfer was still being processed. More formally, isolation means the transaction history is serializable.

Isolation level the oracle supports are 1.Read committed (default) & 2. Serializable
Set transaction Serializable –

Means read or write only commited data before start of transaction and No Phantom phenomenon.

Different statements in a transactin may see datatabase in different state and this is referred as Phantom Phenomenon. (In read commited txn, say stmt at line 1 reads 100 rows, in same time stmt at line 20 may read 120 rows that is called as phantom phenomenon)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

4. Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction. Typically, all transactions are written into a log that can be played back to recreate the system to its state right before the failure. A transaction can only be deemed committed after it is safely in the log.

IN & Exists

2008-03-27T12:36:00.000-07:00

IN & EXISTS:

Select * from T1 where x in ( select y from T2 )
is typically processed as:

select * from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.

As opposed to

select * from t1 where exists ( select null from t2 where y = x )
That is processed more like:

for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).

So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery
( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.

Temporary Tables

2008-03-27T12:35:00.000-07:00

What is temporary table?

Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session. Index created on the table is also temporary and it is dropped when the session exits. Views and triggers can also be created.

Transaction specific temp table:
Create global temporary table temptab
(
Col1 number,
Col2 varchar2(8)
)
On commit delete rows  default

Session specific temp table:
Create global temporary table temptab
(
Col1 number,
Col2 varchar2(8)
)
On commit preserve rows

Normalization Principles

2008-03-27T12:34:00.001-07:00

What is normalization and common forms?
Normalization is process of removing anomalies, redundancy and retaining functional dependencies. A table should be in atleast the 3 NF or BCNF for effective design.

1NF: Table should be atomic. i.e. every tuple should have only one attribute.
Every table should have a primary key.

Fully functional dependency or irreducible dependency:
• Let A and B are attributes of a relation
• B is fully dependent on A, if B is functionally dependent on A but not on any subset of A.

2NF: All the non candidate key should irreducibly depend only on primary key. OR Every non primary key attribute is fully functionally dependent on th primary key. (for functional dependency)

If functional dependency exists say A -> B and B -> C then A -> C this is called transitive dependency.

3NF: All the non candidate keys should non transitively depend on primary key.

Difference between 3NF and BCNF is that for a functional dependency A -> B, 3NF allows this dependency in a relation if B is primary key attribute and A is not a candidate key.

BCNF: All the non candidate keys should be non transitively depend on the primary key and also there should not be transitive dependence in the candidate keys.
Only determinants are candidate key. Arrows always out of candidate keys.

MVD

Dependency between attributes (forexample, A, B, and C) in a relation, such that
for each value of A there is a set of values for B and a set of values for C. However, set of values for B and C are independent

MVD between attributes A, B, and C in a relation using the following notation:

A ->> B,
A ->> C

MVD can be defined as trivial or nontrivial – MVD A ->>B in relation R is defined
as being trivial if:
(a) B is a subset of A or
(b) A U B = R

MVD is nontrivial if neither (a) nor (b)
Trivial MVD does not specify a constraint on a relation, while a nontrivial MVD does

4NF is defined as a relation that is in BCNF and contains no nontrivial MVDs

4NF: In a relvar R if exists a non trivial MVD A ->> B, then the candidate key should also functionally dependent on A.

5NF: In a relvar R if exists a join dependency then all the join dependencies should be visible and all the candidate key should be visible.

External Tables

2008-03-27T12:32:00.001-07:00

External Tables:

CREATE TABLE emp_load (employee_number CHAR(5), employee_last_name
CHAR(20),employee_first_name CHAR(15),
employee_middle_name CHAR(15))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS FIXED 62 FIELDS
(employee_number CHAR(2),
employee_dob CHAR(20),
employee_last_name CHAR(18),
employee_first_name CHAR(11),
employee_middle_name CHAR(11)))
LOCATION ('info.dat'));

Type -- ORACLE_LOADER (Default) text files, ORACLE_DATADUMP binary dump
Default Directory – The directory where data file will present
CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles';
GRANT READ ON DIRECTORY ext_tab_dir TO scott;

Access Parameters –
Record info ->
Records fixed or delimited by newline or delimited by “,” etc
Badfile dirname:filename
logfile dirname:filename
discardfile dirname:filename
Load when empno > 1000;
Field info ->
Fields terminated by whitespace optionally encolsed by “(“ and “)”
Fields terminated by “,”
Fields enclosed by “’” and “’” ltrim MISSING FIELD VALUES ARE NULL
Field List ->
Field_name, pos_spec,datatype_spec, init_spec
First_name (1:15) char(15),
Last_name (*:20) ,

Example with Scott:

CREATE DIRECTORY EXT_TAB_DIR AS 'D:\';
GRANT WRITE,READ ON DIRECTORY EXT_TAB_DIR TO SCOTT;

Sample files:
DATA.TXT -- 2002,Kamal,Cons,7566,18-FEB-2008
Dat.txt -- 2003ABCDEFGHIJZYXWVUTSR756620-FEB-2008

create table emp_ext (empno number(4) , enamE varchar2(10), job varchar2(9),mgr number(4),hiredate date)
organization external (TYPE ORACLE_LOADER DEFAULT DIRECTORY
EXT_TAB_DIR
ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ","
)
LOCATION('DATA.TXT'))

create table emp_ext2 (empno number(4) , enamE varchar2(10), job
varchar2(9),mgr number(4),hiredate date)
organization external TYPE ORACLE_LOADER DEFAULT DIRECTORY EXT_TAB_DIR
ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE LOGFILE 'EMP_EXT.LOG' FIELDS(
EMPNO (1:4) INTEGER EXTERNAL,
ENAME (*:+10) CHAR,
JOB (*:+9) CHAR,
MGR (*:+4) INTEGER EXTERNAL,
HIREDATE (*:+11) DATE 'DD-MON-YYYY'
)
)
LOCATION('DAT.TXT'))