Category Archives: Oracle

The next half hour

I have a job in Oracle that runs every 30 minutes (during work hours), and wanted to schedule the first run at the next “xx:30” mark on the clock. Nothing all that exciting, but took me a few minutes to figure out the logic and I thought I would share.

Line of interest from my call to the scheduler:

 start_date      =>   CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE )
 

Basic demonstration of the calculation:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI AM";
  
Session altered.
  
SQL> SELECT SYSDATE FROM DUAL;
  
SYSDATE
-------------------
04/02/2014 12:11 PM
  
SQL> SELECT CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE ) FROM DUAL;
  
CEIL((SYSDATE-TRUNC
-------------------
04/02/2014 12:30 PM

Comparing lists in Oracle

While Oracle is incredibly powerful, there are some things it generally isn’t asked to do. One of those things is compare a list of values. You can compare recordsets, tables, strings, etc… But trying to compare two separate lists of values can be a challenge.  Other languages such has ColdFusion have quite a few LIST functions baked in, but there are times I don’t want to (or can’t) switch from Oracle to CF for processing.

Below is a package I wrote with two functions I have found I need from time to time. If you are needing them every day, you should probably re-evaluate your data model… but there are times you have to deal with the data you got.

First, declare a datatype you will need.


CREATE TYPE LIST_PACK_TYPE AS TABLE OF VARCHAR2(4000);
/

Compile the package

CREATE OR REPLACE PACKAGE LIST_PACK 
AS
  FUNCTION LIST_CONVERT_FUNC
   ( P_LIST IN VARCHAR2,
     P_DELIM IN VARCHAR2 DEFAULT ',' )
   RETURN LIST_PACK_TYPE
   PIPELINED;
 --
 FUNCTION LIST_SINGLE_MATCH_FUNC
 ( P_LIST1 IN VARCHAR2,
   P_LIST2 IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ','
 )
 RETURN NUMBER;
 --
END LIST_PACK;
/

CREATE OR REPLACE PACKAGE BODY LIST_PACK 
AS
 FUNCTION LIST_CONVERT_FUNC
 ( P_LIST IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ',' 
 )
 RETURN LIST_PACK_TYPE
 PIPELINED
 AS
   L_STRING LONG := P_LIST ||P_DELIM;
   L_DELIM_INDEX PLS_INTEGER;
   L_INDEX PLS_INTEGER := 1;
 BEGIN
 LOOP
   L_DELIM_INDEX := INSTR(L_STRING, P_DELIM, L_INDEX);
   EXIT WHEN L_DELIM_INDEX = 0;
   PIPE ROW (SUBSTR(L_STRING, L_INDEX, L_DELIM_INDEX - L_INDEX));
   L_INDEX := L_DELIM_INDEX + 1;
 END LOOP;
 RETURN;
 END LIST_CONVERT_FUNC;
 --
 FUNCTION LIST_SINGLE_MATCH_FUNC
 ( P_LIST1 IN VARCHAR2,
   P_LIST2 IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ','
 )
 RETURN NUMBER
 IS
   v_MATCHES NUMBER;
   GET_OUT EXCEPTION;
 BEGIN
   IF TRIM(P_LIST1) IS NULL AND TRIM(P_LIST2) IS NULL THEN
     v_MATCHES := 0;
     RAISE GET_OUT;
   END IF;
   SELECT COUNT(*) INTO v_MATCHES 
   FROM ( SELECT COLUMN_VALUE 
          FROM TABLE(LIST_CONVERT_FUNC(P_LIST1,P_DELIM))
          INTERSECT 
          SELECT COLUMN_VALUE 
          FROM TABLE(LIST_CONVERT_FUNC(P_LIST2,P_DELIM))
        );
   IF v_MATCHES >= 1 THEN
     RETURN 1;
   ELSE 
     RETURN 0;
   END IF;
 EXCEPTION
   WHEN GET_OUT THEN
     RETURN v_MATCHES;
 END LIST_SINGLE_MATCH_FUNC;
 --
END LIST_PACK;
/

Examples:

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5')
-------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6')
-------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5',',')
-----------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6',',')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','3|4|5','|') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','3|4|5','|')
-----------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6','|') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6','|')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6',',')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC(NULL,NULL,',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC(NULL,NULL,',')
-----------------------------------------------
 0

Manage your SQL Plus Window

Update your glogin.sql file to contain the code below to automatically modify your sql plus window to reflect database environment and logged in user.  My glogin.sql file is located in the following directory:

C:\oracle\Ora11gR2x64\product\11.2.0\client_1\sqlplus\admin


set term off
set sqlprompt "_user > "
define sql_prompt= '&_user @ &_connect_identifier'

COLUMN host_cmd_col NEW_VALUE host_cmd

SELECT ( CASE
when upper('&_CONNECT_IDENTIFIER') = 'PROD' THEN 'COLOR F0'
when upper('&_CONNECT_IDENTIFIER') = 'DEV' THEN 'COLOR 1F'
when upper('&_CONNECT_IDENTIFIER') = 'STAGE' THEN 'COLOR 0F'
when upper('&_CONNECT_IDENTIFIER') = 'QA' THEN 'COLOR 4F'
else 'COLOR 0E'
END) host_cmd_col
FROM dual;

HOST &host_cmd
set term on
HOST title &sql_prompt

/* Color attributes are specified by TWO hex digits -- the first corresponds to the
background; the second the foreground. Each digit can be any of the below values.

0 = Black      8 = Gray
1 = Blue       9 = Light Blue
2 = Green      A = Light Green
3 = Aqua       B = Light Aqua
4 = Red        C = Light Red
5 = Purple     D = Light Purple
6 = Yellow     E = Light Yellow
7 = White      F = Bright White
*/

One source I used heavily for writing my own script:  http://steveharville.wordpress.com/2010/01/29/change-sqlplus-color-according-to-database-name/

An updated sqlprompt script

Basically, the meat of this post is this command (in sql plus):

set sqlprompt "_user '@' _connect_identifier > "

If you run the above command, your login prompt will update automatically even when you switch connections.  Previous methods had to be run manually over and over with every new connection… unless you wrote your own “connect” script.  This way… run once and it follows you throughout your session.

You can also update your glogin.sql file to contain the command above.  My glogin.sql file is located in the following directory:

$ORACLE_HOME\product\11.2.0\client_1\sqlplus\admin

CFStoredProc – When Oracle and CF stop talking…

Today I had a coworker run into something that has caused me hours of pulling my hair out. He had a stored procedure in Oracle that he made some minor changes to, and them BAM! CF errors. There were several different errors served up from CF including (paraphrased) “wrong number/types of arguments” or “no statement parsed” or my favorite “unsupported datatype”. I am going from memory since I am at home, but you get the flavor of the messages even if my language is off.

I run into this problem about once every 8-10 months. Fortunately, I have hit it enough that I vaguely remember that it isn’t me. It is Oracle/CF not playing nice together.

Here is the scenario:
– Write a procedure that compiles fine. Runs from the command prompt and from CF pages just fine.
– Make some change to the procedure like adding an input variable.
– Update cfstoredproc arguments to match new Oracle definition
– Fail. Hard… unsympathetic fail. (See above error messages)
– Sometimes I find swearing under my breath helps at this point, but while I feel better, it does nothing to clear the error.

What I have gleaned from experience and some online resources is that CF is caching the call to the stored proc. The stored proc changed, but CF isn’t ready to try new things.

So the next question is “Great… now how do I fix it?”

(I should also mention that the call to the stored proc is in a CFC)

Rebooting the application (my CF app… not the server) does nothing. Clearing the query cache does nothing. Changing positions at my desk for new perspectives does nothing. So far, the server admins and I found only 2 things that will fix the problem. 1) Time. (time heals many things) and 2) In CF Admin, disable/re-enable the database connection (Datasource … Disable connections checkbox)

Item number 2 above does the trick, but I gotta believe there is a better way. I hoping one of the three people that read my blog might have a suggestion. Anybody?

One in a million

How many times have you been creating or migrating data and have everything fail because of one stinkin’ record? Can you deal with it? Of course. There are a lot of ways to trap that. Try/Catch with single inserts at a time. Spend time finding the offending record. Etc. When you do bulk inserts from CF, you have to be especially careful if you don’t want your page to fail half way through the operation with half the data saved.

Wouldn’t it be nice if you could basically say “Hey Oracle… I am going to insert a bunch of records. A few might not be valid, but please insert the rest and just let me know which ones failed. Please….”

Well, it turns out you can do just that. If you want to Goolge this, the proper term is DML Error Logging and was part of Oracle 10g Release 2.

Since I prefer to show and then explain…

SQL> CREATE TABLE EXAMPLE_TAB
 2 ( EMP_ID NUMBER,
 3   EMP_NAME VARCHAR2(30),
 4   EMP_POSITION VARCHAR2(30),
 5   CONSTRAINT PK_EXAMPLE_TAB PRIMARY KEY (EMP_ID)
 6 );

Table created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (100,'Chris','Standing');

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Angela','Sitting');

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (120,'Monika','Running');

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Jon Connor','Crawling');
INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
*
ERROR at line 1:
ORA-00001: unique constraint (SNWAENGDRAW.PK_EXAMPLE_TAB) violated
SQL> COMMIT;

Commit complete.

SQL> select count(*) from example_tab;

COUNT(*)
----------
 3

What happened? Three records were created and one failed. No big deal. Running this set of queries, it would be easy to identify the problem and fix it. If you had this in code somewhere, you would need to handle that ugly error message.

Let’s try it again with the error logging.

SQL> CREATE TABLE EXAMPLE_TAB
 2 ( EMP_ID NUMBER,
 3   EMP_NAME VARCHAR2(30),
 4   EMP_POSITION VARCHAR2(30),
 5   CONSTRAINT PK_EXAMPLE_TAB PRIMARY KEY (EMP_ID)
 6 );

Table created.

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG( 'EXAMPLE_TAB' );

PL/SQL procedure successfully completed.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (100,'Chris','Standing') LOG ERRORS REJECT LIMIT UNLIMITED;

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Angela','Sitting') LOG ERRORS REJECT LIMIT UNLIMITED;

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (120,'Monika','Running') LOG ERRORS REJECT LIMIT UNLIMITED;

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Jon Connor','Crawling') LOG ERRORS REJECT LIMIT UNLIMITED;

0 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from example_tab;

COUNT(*)
----------
 3

SQL> DESC ERR$_EXAMPLE_TAB;
 Name             Null?             Type
 ---------------------------------------------
 ORA_ERR_NUMBER$                    NUMBER
 ORA_ERR_MESG$                      VARCHAR2(2000)
 ORA_ERR_ROWID$                     ROWID
 ORA_ERR_OPTYP$                     VARCHAR2(2)
 ORA_ERR_TAG$                       VARCHAR2(2000)
 EMP_ID                             VARCHAR2(4000)
 EMP_NAME                           VARCHAR2(4000)
 EMP_POSITION                       VARCHAR2(4000)

SQL> SELECT ORA_ERR_MESG$ FROM ERR$_EXAMPLE_TAB;

ORA_ERR_MESG$
-------------------------------------------------------------------------
ORA-00001: unique constraint (SNWAENGDRAW.PK_EXAMPLE_TAB) violated

Neat. For the sake of brevity, I will talk in bullets about the above code.

  • “EXEC DBMS_ERRLOG.CREATE_ERROR_LOG( ‘EXAMPLE_TAB’ );” Is telling Oracle, “Hey… log DML errors for the table EXAMPLE_TAB.
  • When you tell Oracle to set up logging, it creates a new table with your origal table name and “ERR%_” prepended to it.
  • Adding “LOG ERRORS REJECT LIMIT UNLIMITED” is telling Oracle, “Hey… I think this will work, but if it doesn’t, don’t throw an error. Just log the failure in that table I told you about.” Also… if dealing with a huge amount of data, you might want to think about not setting the limit to UNLIMITED.
  • Notice that the insert above that failed returned “0 rows created”.
  • If you start logging, have a plan in place to purge the data from the log table. Log tables are notorious for growing faster than you thought they would. At least monitor the size.

This technique works with UPDATES as well. The constraint type is also flexible. I just chose a PK for my example.

When I first learned of this technique, my world shifted a little. I began going back over the hours and hours I spent trying to track down malformed records in bulk inserts/updates. Give this technique a try and keep it in your library. It isn’t appropriate for everything, but when it is… it can save you A LOT of time.

For a much better explanation of what you can do with the error logging:

http://tkyte.blogspot.com/2005/07/how-cool-is-this.html

Hierarchical Queries in Oracle

The concept of hierarchical queries is one that seems simple, but can quickly become confusing to those who don’t commonly write them. I still have to occasionally walk away from the keyboard when writing these queries and I have been doing it for years.

Everybody has seen the example queries with employees and managers. I am not going to rehash this example again. Instead I am going to provide a practical example looking at roles granted to a user in Oracle.

The same concept can be applied to inheritance and all sorts of other real world data rollup issues.

Privileges in Oracle can be granted on an object to a user or a role. It gets more difficult to traverse when you have roles granted to other roles.

Here is a basic query that needs some work to get there.

SQL> SET LINESIZE 200;
SQL> SET PAGESIZE 50;
SQL> SELECT SYS_CONNECT_BY_PATH(GRANTED_ROLE,'-->') AS USER_AND_ROLE_STR
 2   FROM   ( SELECT NULL as GRANTEE,
 3                   USERNAME as GRANTED_ROLE
 4            FROM   DBA_USERS
 5            WHERE  USERNAME = 'CHRIS'
 6            UNION
 7            SELECT GRANTEE, GRANTED_ROLE
 8            FROM   DBA_ROLE_PRIVS
 9          )
 10 START WITH GRANTEE IS NULL
 11 CONNECT BY GRANTEE = PRIOR GRANTED_ROLE;

USER_AND_ROLE_STR
-----------------------------------------------------
-->CHRIS
-->CHRIS-->AA_USER
-->CHRIS-->CONNECT
-->CHRIS-->CONTRACT_TRACKING
-->CHRIS-->PROJPLANNING
-->CHRIS-->PROJPLANNING-->PLANNINGUPDT
-->CHRIS-->EDUSER
-->CHRIS-->PLANNINGUPDT
-->CHRIS-->GISUSER
-->CHRIS-->GISUSER-->TELE_USER
-->CHRIS-->MC_CLIENT
-->CHRIS-->AGENDAUSER
-->CHRIS-->VACUPDATE
-->CHRIS-->CAM_S
-->CHRIS-->CAM_U
-->CHRIS-->PLNING
-->CHRIS-->SDE_VIEWER
-->CHRIS-->SDE_VIEWER-->SDE_SYS_PRIVS
-->CHRIS-->ENGDEPT
-->CHRIS-->PLAN_DATA_ENTRY
-->CHRIS-->PLAN_DATA_ENTRY-->PLAN_USER
-->CHRIS-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
-->CHRIS-->PLAN_POWER_USER
-->CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY
-->CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY-->PLAN_USER
-->CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
-->CHRIS-->PR_USER
-->CHRIS-->TRACKING_USER
-->CHRIS-->PORTAL_ADMIN
-->CHRIS-->STRATEGIC_PLAN_ADMIN

30 rows selected.

Neat. The query above shows the user, roles granted to the user, and then roles granted to those roles.

What’s happening in the query…
1) We are looking for our user in the DBA_USERS object. And then we are getting a list of all the roles, and roles granted to roles from DBA_ROLE_PRIVS. The union of the two queries gives us our base set of data that we will then use.
2) The “START WITH” and “CONNECT BY” portions of the query are telling Oracle to look at the data as a tree using a parent/child relationship.
3) “SYS_CONNECT_BY_PATH” returns the path of the hierarchy set up with the “START WITH” and “CONNECT BY”. First parameter is the column you want to work with. Second parameter is your delimiter. Note: Your delimiter cannot be something that can be found in the data of your column. Do that and you get a nasty error. Example: If your data has commas, don’t use a comma as your delimiter.

Next, let’s clean things up a bit to make the data slightly more “pretty”. Well… as pretty as this type of data can be.

SQL> SET LINESIZE 200;
SQL> SET PAGESIZE 50;
SQL> SELECT LTRIM(SYS_CONNECT_BY_PATH(GRANTED_ROLE,'-->'),'-->') AS USER_AND_ROLE_STR
 2   FROM   ( SELECT NULL as GRANTEE,
 3                   USERNAME as GRANTED_ROLE
 4            FROM   DBA_USERS
 5            WHERE  USERNAME = 'CHRIS'
 6            UNION
 7            SELECT GRANTEE, GRANTED_ROLE
 8            FROM   DBA_ROLE_PRIVS
 9          )
 10  where  CONNECT_BY_ISLEAF = 1
 11  START WITH GRANTEE IS NULL
 12  CONNECT BY GRANTEE = PRIOR GRANTED_ROLE;

USER_AND_ROLE_STR
-----------------------------------------------------------
CHRIS-->AA_USER
CHRIS-->CONNECT
CHRIS-->CONTRACT_TRACKING
CHRIS-->PROJPLANNING-->PLANNINGUPDT
CHRIS-->EDUSER
CHRIS-->PLANNINGUPDT
CHRIS-->GISUSER-->TELE_USER
CHRIS-->MC_CLIENT
CHRIS-->AGENDAUSER
CHRIS-->VACUPDATE
CHRIS-->CAM_S
CHRIS-->CAM_U
CHRIS-->PLNING
CHRIS-->SDE_VIEWER-->SDE_SYS_PRIVS
CHRIS-->ENGDEPT
CHRIS-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
CHRIS-->PR_USER
CHRIS-->TRACKING_USER
CHRIS-->PORTAL_ADMIN
CHRIS-->STRATEGIC_PLAN_ADMIN

21 rows selected.

So what’s different?
1) We have added an “LTRIM()” function that strips off our delimiter from the front of our return. You don’t really need that leading delimiter.
2) We are restricting our results to only leaves instead of the branch and leaf. Think of the data as a tree with the trunk being the user you searched for. If they have a role granted to them and it ends there, it is a leaf. If you have a role (1) granted to the “trunk”, and then another role (2) granted to that… role (1) is a branch where role (2) is a leaf. We still want to know about the branch (role (2)), but having a separate row returned just muddies the water.  CONNECT_BY_ISLEAF is a pseudocolumn that returns 1 for leaves, and 0 for branches. In the query above, we reduce our results using this pseudocolumn.

Where does the business logic go?

This is a debate I have both listened to and been a part of for years. When you are building an app of any respectable size, where does the business logic go? Specifically, for the sake of this discussion… In ColdFusion or Oracle?

To be honest about my opinion I think it is only fair to share where I “fit” into things. I would consider myself extremely proficient in data design, database programming, and query writing. Especially in the world of Oracle. I consider myself proficient, if not a bit rusty, in ColdFusion. The way our team is set up, I am the “backend” guy and therefore I live in the database. UI? Hacker. I follow what is going on, and can make modifications easily, but I am not the guy you want coding your front end.

I have been fortunate to be with my current company for quite a few years which means I have seen the full range of philosophies on this subject.

When I was a one man team, or working with a single other developer… if it could go in Oracle, it went in Oracle. Data updates all went through Packages and data retrieval, most of the time, did so as well. This worked well for us. I would be out front with the customers gathering requirements and designing the architecture for the app. Once I reached around an 80% completion range, the CF and UI guy (same guy) was brought in to bring it all together and get the app to the customer.

In the case above, the Oracle packages ended up serving similar needs as CF components. If you wanted a “project” object, you ended up with output parameters representing the “project” as well as recordsets representing things like invoices, checks, people/roles, etc. CF said “give me a project and everything about it”, Oracle did the heavy lifting. I liked it.

Several years back, I began working with a larger team that was comprised of quite a few more proficient CF and UI folks. Given the high number of applications to produce/maintain, our development strategy changed. All of the business logic got pulled out of Oracle and into CFCs. My role was still data architect, but business logic was rarely my realm and I also got labeld “the query guy”. We have a lot of apps and a lot of data to manage, so since I am the main Oracle guy, this works well for our team. We have more CF people so in this case, it makes sense to have the business logic where it is accessible to the most people to understand and maintain.

So my question is this… with all things being equal, where “should” it go? Or is it always a function of the skills/staff available on the project?

Query to delete duplicate rows…

First: I didn’t come up with this technique. I found it on the AskTom (http://asktom.oracle.com/) web site. That being said, I use it all the time and refer others to the technique often. Link to where I got it from: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15258974323143

Let us say you have a table that has duplicates across a couple (or all) columns, and you need to get rid of the dups.  Assuming table “T” with columns “COL1, COL2″… this is the script that would kill the dups.

DELETE FROM T
WHERE ROWID IN 
(SELECT RID FROM 
  (SELECT ROWID RID, 
          ROW_NUMBER() OVER (PARTITION BY COL1, COL2 ORDER BY ROWID) RN 
   FROM T) 
 WHERE RN<>1
);

If you have more columns, just add them into the partition area of the query.

DECODE, CASE… Does it Matter?

If I had a nickel for every time I get asked that question… well, I wouldn’t have much money; but judging by the applications I have worked on, quite a few programmers are 50/50 on the topic. Their code reflects not knowing which to use.

When I discovered CASE many moons ago, I never looked back. It was infinitely more readable than DECODE and handled complex evaluations with simpler code. All things being close to equal, readability/simple is important to me. While I am working on something, I can remember day to day what I was thinking. When an app is in maintenance mode, deciphering nested DECODEs is a pain.

Why is DECODE so rampant in queries?
There are a couple contributing factors that play to the answer here. First… if you want a simple if/then/else, DECODE is less typing. Secondly… Before Oracle 8.1, DECODE was the only game in town for this type of logic in the query… outside of writing your own function. If you have been around for a while, you probably started with DECODE and it comes as naturally as using CFQUERY.

Before we get to the differences between DECODE and CASE, for those not familiar with one or the other, let’s see an equivalent example of both.

/*	WHAT IS IN THE TABLE?	*/

SQL> SELECT * FROM EMP;

    EMP_ID EMP_NAME   SUPERVISOR GENDER
---------- ---------- ---------- -
        10 Larry      Chris      M
        15 Tim        Chris      M
        20 Monika     Eric       F
        25 Jon Connor Angela     M

SQL> SELECT     DECODE(
  2                     SUPERVISOR,'Chris','Works for Chris',
  3                     'Does not work for Chris'
  4             ) AS DECODE_RESULT,
  5             (       CASE    WHEN SUPERVISOR = 'Chris' THEN 'Works for Chris'
  6                             ELSE 'Does not work for Chris'
  7                     END
  8             ) AS CASE_RESULT
  9  from       EMP;

DECODE_RESULT           CASE_RESULT
----------------------- -----------------------
Works for Chris         Works for Chris
Works for Chris         Works for Chris
Does not work for Chris Does not work for Chris
Does not work for Chris Does not work for Chris

Both are easy to read and both return the same results.

Let’s try nesting a bit and see how it looks.

SQL> SELECT     DECODE(
  2                     SUPERVISOR,'Chris','Works for Chris',
  3                     DECODE(
  4                             SUPERVISOR,'Eric','Works for Eric',
  5                             'No idea'
  6                     )
  7             ) as DECODE_RESULT1,
  8             DECODE(
  9                     SUPERVISOR,'Chris','Works for Chris','Eric','Works for Eric','No Idea'
 10             ) as DECODE_RESULT2,
 11             (       CASE    WHEN SUPERVISOR = 'Chris' THEN 'Works for Chris'
 12                             WHEN SUPERVISOR = 'Eric' THEN 'Works for Eric'
 13                             ELSE 'No idea'
 14                     END
 15             ) AS CASE_RESULT
 16  FROM       EMP;

DECODE_RESULT1  DECODE_RESULT2  CASE_RESULT
--------------- --------------- ---------------
Works for Chris Works for Chris Works for Chris
Works for Chris Works for Chris Works for Chris
Works for Eric  Works for Eric  Works for Eric
No idea         No Idea         No idea

In the above queries, there are are two different styles for the DECODE. DECODE_RESULT2 is probably the easier of the two to read. I still think the CASE is the more readable.

If you aren’t careful, you can end up with DECODE(…,DECODE(…,(DECODE(…,)))). And if any of those DECODES have multiple tests, then good luck debugging. (Good code formatting will help, but still can get hairy when trying to debug).

So what are some of the differences?

1) How they handle nulls is an important distinction.

SQL> SELECT     DECODE(
  2                     NULL,NULL,'I AM NULL',
  3                     'I AM NOT NULL'
  4             ) AS DECODE_RESULT
  5  FROM       DUAL;

DECODE_RE
---------
I AM NULL

SQL> SELECT     (       CASE    NULL
  2                             WHEN NULL THEN 'I AM NULL'
  3                             ELSE 'I AM NOT NULL'
  4                     END
  5             ) AS CASE_RESULT1
  6  FROM       DUAL;

CASE_RESULT1
-------------
I AM NOT NULL

SQL> SELECT     (       CASE    WHEN NULL IS NULL THEN 'I AM NULL'
  2                             ELSE 'I AM NOT NULL'
  3                     END
  4             ) AS CASE_RESULT1
  5  FROM       DUAL;

CASE_RESULT1
-------------
I AM NULL

If you aren’t careful, the CASE can throw you off when testing for nulls.

2) CASE can work with logical operators other than equals. (You can also use ranges in CASE statements)

SQL> SELECT     DECODE(EMP_ID,<100,'Early Hire','New Hire') as DECODE_RESULT
  2  FROM       EMP;

SELECT     DECODE(EMP_ID,<100,'Early Hire','New Hire') as DECODE_RESULT
                         *
ERROR at line 1:
ORA-00936: missing expression

SQL> SELECT     (       CASE    WHEN EMP_ID<100 THEN 'Early Hire'
  2                             ELSE 'New Hire'
  3                     END
  4             ) AS CASE_RESULT
  5  FROM       EMP;

CASE_RESUL
----------
Early Hire
Early Hire
Early Hire
Early Hire

3) CASE can use subqueries.

SQL> SELECT     (       CASE    WHEN SUPERVISOR = 'Chris' THEN 'Good supervisor'
  2                             WHEN EMP_ID NOT IN 
                                    (    SELECT EMP_ID 
                                         FROM   EMP 
                                         WHERE  SUPERVISOR = 'Chris'
                                    ) THEN 'Questionable leadership'
  3                             ELSE    NULL
  4                     END
  5             ) AS CASE_RESULT
  6  FROM       EMP;

CASE_RESULT
-----------------------
Good supervisor
Good supervisor
Questionable leadership
Questionable leadership

4) CASE can be used as a PL/SQL construct. I won’t demonstrate that here, but it can be.

5) CASE can be used as a parameter to a procedure.

SQL> CREATE PROCEDURE STOOPID_PROC
  2  (  P_VAL IN VARCHAR2       )
  3  AS
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE(P_VAL);
  6  END STOOPID_PROC;
  7  /

Procedure created.

SQL> EXEC STOOPID_PROC(DECODE(NULL,'NULL','NOT NULL'));

BEGIN STOOPID_PROC(DECODE(NULL,'NULL','NOT NULL')); END;

                   *
ERROR at line 1:
ORA-06550: line 1, column 20:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> EXEC STOOPID_PROC((CASE WHEN NULL IS NULL THEN 'NULL' ELSE 'NOT NULL' END));
NULL

PL/SQL procedure successfully completed.

6) CASE is ANSI. DECODE is not.

7) Speed. There is a difference, but I never seem to find a good answer on which way this one goes. Mostly, it appears CASE is faster, but I have never done a full on test to see. At least not yet.

*Note: You don’t need all the parenthesis around the CASE statements. I like them.