Monthly Archives: November 2013

Knowledge

One of my favorite and most trusted sites for Oracle information is AskTom (http://asktom.oracle.com/). One of my favorite quotes I have read at least a hundred times is “I learn something new about Oracle every day”.  I wouldn’t say I learn something new every day, but this has been one of the more fruitful weeks in this regard.

As I spent about 20 minutes on the phone with one of our DBAs today, I realized how lucky I am to work in a place where others are willing to share what they know and answer questions.  I made a mental note to remember to do this with others when they ask the same of me.  In the spirit of “Thanksgiving”, I found myself thankful for having the time and flexibility to learn in my current job and I am thankful for those around me willing to teach.  I don’t care how impressed you are with yourself, there is always someone who can teach you.

I guess the message of this little post is to encourage the three people who read this blog to be willing to take the time to share what they know or learn with others.  When you create an environment that fosters sharing information instead of competition, everybody grows.  That being said… a little competition among the team members is healthy as well.

Advertisements

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.