Oracle Scheduler – Repeat Interval

Oracle’s scheduler offers a great deal of flexibility regarding when a particular job will run.  While I like the functionality, I don’t use it often enough to remember off the top of my head the specific variations.

The table below is more for me as a reference than anybody else, but I thought I would put it out here in case anybody finds it useful.  If you want any variations added, just add a comment.

The following are for the “repeat_interval” attribute in the scheduler.

Readable explanation

(all based on the start date of the job)

Code
Every day FREQ=DAILY; INTERVAL=1
Every Monday FREQ=WEEKLY; BYDAY=MON
Every 8 hours FREQ=MINUTELY; INTERVAL=480
Mon-Thur, 5 past the hours of 9am, 11am, 1pm, 3pm FREQ=DAILY; BYDAY=MON,TUE,WED,THU; BYHOUR=,9,11,13,15; BYMINUTE=05; BYSECOND=0;
Mon-Thur, 5 past the hour every hour between 7am and 6pm FREQ=DAILY; BYDAY=MON,TUE,WED,THU; BYHOUR=7,8,9,10,11,12,13,14,15,16,17,18; BYMINUTE=05; BYSECOND=0;
Mon-Thur, at 6am, 12pm, and 6pm FREQ=DAILY; BYDAY=MON,TUE,WED,THU; BYHOUR=6,12,18;
Daily at 5 past every hour FREQ=DAILY; INTERVAL=1; BYHOUR=5;
Mon-Thur at 12pm FREQ=DAILY; BYDAY=MON,TUE,WED,THU; BYHOUR=12; BYMINUTE=00
Every 2 hours FREQ=MINUTELY; INTERVAL=120

For more examples: http://awads.net/wp/2011/02/02/25-unique-ways-to-schedule-a-job-using-the-oracle-scheduler/

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?

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.

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?