Oracle Analytics / Analytic Functions: first_value()

Oracle database version : 10g
Schema : Scott

Analytic Functions Syntax :
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

On the “emp” table, say the requirement is to “get the highest paid job in each department”.

select * from emp order by deptno asc, sal desc;

If you run the above select query, you will see that the result should be :

DEPTNO JOB
10 PRESIDENT
30 MANAGER
20 ANALYST

So, how do we get this result ?

Analytics just Rock & Roll !!!

Note : This is one of the very common requirements … based on the column1, get the column2 for the respective max(column3).

Here we go …

select distinct deptno, first_value(job) over (partition by deptno order by sal DESC ) job
from emp;

first_value() : Returns the first value in an ordered set of values. If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS

Note : Google for last_value() function as well.

Following is an example from asktom.

Script to create the test table and data.

CREATE TABLE myTable (
Ship_date DATE NOT NULL
,Ship_type VARCHAR2(10) NOT NULL
,Shipment NUMBER(6,0) NOT NULL
)
/
INSERT INTO myTable VALUES('01-01-2000', 'SHIP1', 27);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP1', 26);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP1', 25);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP2', 24);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP2', 23);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP2', 22);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP3', 21);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP3', 20);
INSERT INTO myTable VALUES('01-01-2000', 'SHIP3', 19);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP1', 18);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP1', 17);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP1', 16);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP2', 15);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP2', 14);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP2', 13);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP3', 12);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP3', 11);
INSERT INTO myTable VALUES('01-01-2001', 'SHIP3', 10);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP1', 9);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP1', 8);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP1', 7);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP2', 6);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP2', 5);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP2', 4);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP3', 3);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP3', 2);
INSERT INTO myTable VALUES('01-01-2002', 'SHIP3', 1);
COMMIT;

Requirement 1 : Select only those rows that have the greatest Shipment value, per Ship_type, per Ship_date.

Solution :

select *
from (select myTable.*,
max(shipment) over(partition by ship_date, ship_type) max_shipment
from myTable)
where shipment = max_shipment

Requirement 2 : select only those rows that have the greatest Shipment value, per Ship_type, per Ship_date, showing ONLY those records with greatest Ship_date per Ship_type.

The result should be :

SHIP_DATE SHIP_TYPE SHIPMENT
---------- ---------- ----------
01-01-2002 SHIP1 9
01-01-2002 SHIP2 6
01-01-2002 SHIP3 3

Solution :

select * from (
select a.*, max(ship_date) over(partition by ship_type) max_ship_date
from (select myTable.*, max(shipment) over(partition by ship_date,
ship_type) max_shipment from myTable
) a
where shipment = max_shipment
) where ship_date = max_ship_date;


Long live “Tom Kyte”.

Good Luck,
r-a-v-i

Insert with check option

One of the ways to validate data while insertion, directly at the database level is : INSERT …. WITH CHECK OPTION

Let’s take an example and see what it is.

Database version : Oracle 10g.

Schema : Scott

Let’s create a test table from the standard “emp” table and work on it.

create table test_emp as select * from emp;

Let us suppose that our requirement is something like this :

Hence forth, in Department 30 manager’s should have commission between 750 – 1000 (included).

For our requirement above, the select query would be :

select *
from test_emp e
where e.deptno = 30
and e.job = 'MANAGER'
and e.comm >= 750
and e.comm <= 1000;

Usually the developers take care of this validation on the front-end itself. If there is only one point of data entry to your database (Eg : Web front-end), it’s okay to validate at the entry point. But think of a situation where there are multiple point of data entries to your database (Eg : Web front-end, Feeds, Web Services, JMS, MDB’s …etc.,).

So,

a) You have to duplicate the validation (it is almost impossible to maintain a common code base between these many discrete systems. Even if you have a common code base, on system might just not invoke the validation at all.)

b) If a common code base for validations is not used, then there could be a bug or missing implementation in any of the systems.

So, how about having a validation just before inserting the data into the database ? PL/SQL strikes immediately right 

We can put the volition logic in a pl/sql procedure/function and let all the above discrete systems invoke it.

But…….. there is a performance overhead. We are adding another layer after data access layer. For simple/moderate systems, it is okay. What we have a high volume system.

Oracle “almost always” has an option.

Oracle prohibits any changes to the table or view that would produce the rows that are not included in the sub query.

Make the above select query as a sub query to insert() as shown below.

insert into
(select *
from test_emp e
where e.deptno = 30
and e.job = 'MANAGER'
and e.comm >= 750
and e.comm <= 1000) values (7935, 'New Guy', 'MANAGER', 7782, sysdate, 1600.00, 500, 30);

The data that we are trying to insert in this query is against our rules, as the commission is = 500.

If you execute the above insert, Oracle still inserts the record.

select * from test_emp e where e.empno = 7935;

1 7935 New Guy MANAGER 7782 5/29/2008 9:02:05 AM 1600.00 500.00 30

Provide “WITH CHECK OPTION” in the sub query as shown below :

insert into
(select *
from test_emp e
where e.deptno = 30
and e.job = 'MANAGER'
and e.comm >= 750
and e.comm <= 1000 with check option)
values
(7935, 'New Guy', 'MANAGER', 7782, sysdate, 1600.00, 500, 30);

When you try to execute the above insert, you will get an error message :

ORA-01402 : view WITH CHECK OPTION where-clause violation

So, no matter where the data is coming from, we could perform a validation directly on the insert itself.

Also, note that we are NOT performing an extra query, as the sub query is within the insert itself.

Useful links :

http://www.oracle.com/technology/oramag/oracle/04-mar/o24asktom.html
http://www.orafaq.com/node/55
http://www.psoug.org/reference/analytic_functions.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3170642805938

Hope this helps.

Long Live “Tom Kyte”.

Good Luck,
r-a-v-i

Oracle Analytics/Analytic Functions : How to get number of rows(count ) returned by a given query, in the same query ?

Often we, the Java/Oracle developers run into the situation, where we need to find the number of rows (count) returned by a query.
You can just find out the count by firing 2 queries.
Let's take an example and see how it works.

Schema : scott/tiger

Analytic Functions Syntax :
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

Let's suppose that,we are dealing with the employees in departments 10, 20.

Query#1 :

SELECT empno, deptno
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, empno;

Let's suppose that the requirement is : if the number of employees in departments 10,20 is more than 10, then set their manager as SCOTT (EmpNo#7788).
(Some dummy requirement ...please don't bother about it).

So, now we need to find out the count(). Let's find out the count.

Query#2 :

SELECT sum(count(*))
FROM emp
WHERE deptno IN (10, 20)
group by deptno;

This query would return the count, but if you did notice, we don't have the "empno" column in the select list.

We can't have "empno" in the column list as it is not in the group by columns. So, to get the empno of each department,we have to
fire the Query#2.

So, the conclusion is, using this approach without firing 2 queries we can't get the count() of rows returned by Query#1.

In Java world, you can use ScrollableResultSet to get the count. But again, you are doing an extra operation there to get the count.

So....without doing any extra operation or firing an extra query, how to get the count ?

Oracle Analytics comes to rescue.

Query#3 :

SELECT empno, deptno, count(*) over() cnt
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, empno;

OVER(), acts on entire record set returned by the where clause. Here is the result of Query#3.

EMPNO DEPTNO CNT
1 7782 10 8
2 7839 10 8
3 7934 10 8
4 7369 20 8
5 7566 20 8
6 7788 20 8
7 7876 20 8
8 7902 20 8

If you notice, using this approach, we have 2 benefits :

1) We can find the count() of rows returned by a query
2) We can also include other columns in the select list which are not part of the where clause.
Since we are not using "group by", we are out of it's limitations.

Long live "Tom Kyte".

Hope this helps !!

Good Luck,
r-a-v-i

Java/Oracle : Should I Encrypt / Hash the passwords ? What is the difference ?

The standard question asked by a Java/Oracle developer.

Should I Encrypt OR Hash the passwords ? What is the difference ?

The BIG difference between Encryption and Hashing is that, the data that is encrypted, should be able to be decrypted. Whereas, the data that is hashed, CANNOT be reversed.

Let's take an example.

Usually we authenticate users to log on to our systems or web sites. So, following are the steps :

1. User logs onto a web site and provides user name/password.
2. We need to authenticate the user and if the credentials are valid, log him in or deny access.

So, in step 2, we just need to make sure that he entered a correct password. For that, we DON'T need to store the user's password either in text form or in an encrypted form.
In either of these cases, I mean either you store the password in text form or in encrypted form, there is a possibility that the password can be stolen and reversed.
If we store the password in the HASH form, since it cannot be reversed, we are SAFE.
Unix/Oracle ...etc., follow the same methodology for authentication.

So, how do we apply hashing on plain text passwords.

Let us suppose that the password is : password123

Then the query would look like :

select dbms_crypto.hash(utl_raw.cast_to_raw('password123'),dbms_crypto.HASH_MD5) hashed_password from dual;

If you notice, the hash() takes a second parameter where you can specify, which hashing algorithm you would like to use.

-- Hash Functions
HASH_MD4 CONSTANT PLS_INTEGER := 1;
HASH_MD5 CONSTANT PLS_INTEGER := 2;
HASH_SH1 CONSTANT PLS_INTEGER := 3;

If you are using Oracle 10g, then you can use dbms_crypto.hash().
Otherwise, if you are using Oracle 8i/9i, you have to use - DBMS_OBFUSCATION_TOOLKIT.MD5.

Take a stab at the Oracle Guru's web site : http://asktom.oracle.com/tkyte/Misc/Passwords.html


Long Live "Tom Kyte".

Good Luck !!

r-a-v-i

Oracle 10g : PL/SQL : Conditional Compilation

If you are a pl/sql developer and if you looking for best practices on unit testing, this is for you.

Usually we run into situations where we need to debug some code, but we don't want to be running the debug code in production. Do we ???

Oracle 10g provides a beautiful feature - conditional compilation. (Like #ifdef in C).

Let's see an example.

Create a test procedure ...

create or replace procedure test_proc
as
begin
dbms_output.put_line( 'Debug 1' );
dbms_output.put_line( 'Debug 2' );
$IF $$debug_code $THEN
dbms_output.put_line( 'Debug 3' );
dbms_output.put_line( 'Debug 4' );
$END
dbms_output.put_line( 'Debug 5' );
end;

set echo on;
set serveroutput on;

Let us suppose that we are running this procedure on our dev/qa environment.

Then, you have to run the following command :
------------------------------------------------------------------------------------
SQL> alter procedure test_proc compile plsql_ccflags = 'debug_code:true' reuse settings;

Procedure altered

SQL>
------------------------------------------------------------------------------------

Now let's run the procedure and see what the output is ....
------------------------------------------------------------------------------------
SQL> exec test_proc;

Debug 1
Debug 2
Debug 3
Debug 4
Debug 5

PL/SQL procedure successfully completed

SQL>
------------------------------------------------------------------------------------

Let's suppose that we are all set to go to production and we need to deploy this procedure on production and we don't want all the debugging.

Then, execute the command :

------------------------------------------------------------------------------------
SQL> alter procedure test_proc compile plsql_ccflags = 'debug_code:false' reuse settings;

Procedure altered

SQL>
------------------------------------------------------------------------------------
That's it. Let's see the output of the procedure, if we run it on production.

------------------------------------------------------------------------------------
SQL> exec test_proc;

Debug 1
Debug 2
Debug 5

PL/SQL procedure successfully completed

SQL>
------------------------------------------------------------------------------------
So, on production when the debug is disabled, you might have noticed that the statements "Debug 3" and "Debug 4" did not print.

Plan B : If you don't want to pollute your code, you can you Log4PLSQL.

Long Live "Tom Kyte".

Good Luck !!

r-a-v-i

Oracle 10g : Case-Insensitive Searching

Say in the famous Scott's "emp" table, I have data like : "Ravi", "rAvi", "RAvi" ...etc.,. So, if I need to fire a query for "ravi", the standard practice is to create a Upper(emp_name) index and fire the following query :

select * from emp where upper(emp_name) like upper('%raVI%');

Prior to Oracle 10g, you had to adopt one of the following strategies:

* Use a function-based index on UPPER (column_name) and modify the queries to use WHERE UPPER (column_name) = value.
* Use a trigger to roll the column value to upper- or lowercase upon modification.
* Use Oracle Text to create a TEXT index on the column; text indexes (which would then mandate the use of the CONTAINS operator in the predicate) can be case-sensitive or -insensitive.

In each of these cases, one of your conditions would have been violated. You would have needed to use triggers, or UPPER() or CONTAINS in the WHERE clause.

In Oracle 10g, you can do this transparently, without effecting your query. Which means that, you don't have to use UPPER() or CONTAINS in your where clause.

Wait ...wait ...I know ... you will believe only if I show you an example. right ?

Here you go ...

drop table cit;

create table cit ( data varchar2(20) );

insert into cit values ( 'Ravi' );
insert into cit values ( 'rAVi' );
insert into cit values ( 'rAvI' );

commit;

/*Create a function-based index on the DATA column and use the binary case insensitive sort*/

create index cit_idx on cit( nlssort( data, 'NLS_SORT=BINARY_CI' ) );

select * from cit where data = 'ravi';
--You will get zero records.

alter session set nls_comp=ansi;
alter session set nls_sort=binary_ci;

select * from cit where data = 'ravi';
--You will get 3 records.

--Now, let's take a look at the explain plan. CBO is using FTS.
--Let's fake CBO that there are 10000000 in the table.
exec dbms_stats.set_table_stats (ownname=>user,tabname=>'CIT',numrows=> 10000000);

--Now run the select again and take a look at the explain plan.
select * from cit where data = 'ravi';

Execution Plan
------------------------------
SELECT STATEMENT (Cost=2)
TABLE ACCESS (BY INDEX ROWID)
INDEX (RANGE SCAN) OF 'CIT_IDX'

Isn't it just awsome !!


Long live "Tom Kyte".

Source : http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

Good Luck !!

r-a-v-i

Oracle 10g : How to pass ARRAYS of records from Java/Tomcat to Oracle

Environment : JDK 1.5, Tomcat 5.5, Oracle 10gR2.

Let's suppose that you have a web app where you get some records from the user interface and from your DAO, you are trying to pass the records as Oracle ARRAYS to database. Here is a step by step example.

Yes, it's a nightmare as you have to take care of some steps. But, once you understand what to do and what are the issues, it's pretty easy.

Use Case : Let us suppose that we have a java bean Employee and we are trying to send an array of employee records at a time to database.

/*Step 1 : Create a object type in the database*/
/*
CREATE OR REPLACE TYPE "EMP_TYPE" is object(
emp_id Varchar2(500),
emp_name varchar2(500));
*/
/*
Step 2 : Create a type EMP_TYPE_TABLE

CREATE OR REPLACE TYPE "EMP_TYPE_TAB";
*/

/*Step 3 : Create a Java bean which maps the attributes of the above object type in Step 2.*/

import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Employee implements SQLData, Serializable{
static final long serialVersionUID = 4070409649129120458L;
public Employee(){}

// constructor that takes parameters
// getters and setters for emp_id, emp_name
// You have to implement readSQL() and writeSQL() methods, as shown below.
// This is where you are mapping the Employee table's columns to the Employee
//java bean.

public void readSQL(SQLInput stream, String typeName) throws SQLException {
this.emp_id = stream.readString();
this.emp_name = stream.readString();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(emp_id);
stream.writeString(emp_name);
}
}

//EmpDAO class gets a connection to the database and passes the data.

public class EmpDAO{
java.sql.Connection conn;
java.sql.Connection dconn;
/*
Step 1 : Get database connection
This is a very important step. To pass your records of data as Arrays, you need to get a oracle.jdbc.driver.T4CConnection and then use ArrayDescriptor's. So, how do you get a T4CConnection ?


To get T4CConnection from java.sql.Connection, you need to cast like this :
t4cConn = ((DelegatingConnection)conn).getInnermostDelegate();

If you are working on tomcat, you have two options to get a DataSource in your context.xml.
a) By using apache commons-dbcp
OR
b) by directly using javax.sql.DataSource.
Let's see how to get the T4CConnection in both these cases.
*/

public void sendRecordsToDB(){

//Use Case (a) : if you configured apache commons-dbcp
BasicDataSource ds = (BasicDataSource)ctx.lookup(jndiName);
ds.setAccessToUnderlyingConnectionAllowed(true);
conn = ds.getConnection();
dconn = ((DelegatingConnection)conn).getInnermostDelegate();

//Use Case (b) : if you are directly using javax.sql.DataSource

BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName("");
bds.setUsername("");
bds.setPassword("");
bds.setUrl("jdbc:oracle:thin:@");
bds.setAccessToUnderlyingConnectionAllowed(true);
conn = bds.getConnection();
dconn = ((DelegatingConnection)conn).getInnermostDelegate();

/*So, using either of the above approaches we got dconn, which is an instance of T4CConnection.*/

/* Now let's build an array list of employees.
*/
final List