Monthly Archives: October 2013

Coalesce as an alternative to NVL

While this seems database specific, I think it hits on a query technique I see all the time in ColdFusion code.

How many times have you written a NVL statement in your (Oracle) query like this:
NVL(NVL(NVL(COL1,COL2),COL3)COL4)

Basically in English this says… If COL1 is null then Col2… If COL2 is null then COL3… If COL3 is null then COL4.

It isn’t very readable and depending on the number of columns you are working with, the nesting can get “challenging” to debug/edit. Also… NVL is Oracle specific where COALESCE is ANSI. This post however, will be looking at how Oracle handles things.

There are some things you should be aware of when using COALESCE, but first let’s see it in action.

SQL>  CREATE TABLE CHRIS_IS_NEAT
2     (  COL1    VARCHAR2(10),
3        COL2    VARCHAR2(10),
4        COL3    VARCHAR2(10),
5        COL4    VARCHAR2(10)
6  );
Table created.

SQL> INSERT INTO CHRIS_IS_NEAT VALUES (NULL,NULL,'BLUE','YELLOW');
1 row created.
SQL> INSERT INTO CHRIS_IS_NEAT VALUES ('RED','BLUE','BROWN','YELLOW');
1 row created.
SQL> INSERT INTO CHRIS_IS_NEAT VALUES (NULL,NULL,NULL,'BLACK');
1 row created.
SQL> COMMIT;
Commit complete.

SQL>
SQL> SELECT     NVL(NVL(NVL(COL1,COL2),COL3),COL4),
2               COALESCE(COL1,COL2,COL3,COL4)
3    FROM       CHRIS_IS_NEAT;

NVL(NVL(NV COALESCE(C
---------- ----------
BLUE       BLUE
RED        RED
BLACK      BLACK

SQL> DROP TABLE CHRIS_IS_NEAT;
Table dropped.
SQL> PURGE RECYCLEBIN;
Recyclebin purged.

The COALESCE function returns the first non-NULL value in the list. I don’t know about you, but that is a lot easier to follow than the NVL syntax.

Important things to remember about COALESCE

  • Datatypes… Try to make them the same. If you do that, skip the rest of the mumbo-jumbo on this bullet. NVL will do an implicit conversion of the datatype to the first parameter if it can. COALESCE… If you don’t have consistent datatypes, you will get a “datatype error”. If the columns are different types, make sure you convert them on the way into the function. Caveat… Different numeric types will implicitly be converted to the argument with the highest numeric precedence. Also, if you have a non-numeric datatype than can be implicitly converted to a number, Oracle will try.
  • NVL evaluates both columns represented. COALESCE stops evaluating as soon as it finds a non-NULL value. Oracle calls this a “short-circuit evaluation*” in the docs. This can provide a speed bump as well over NVL. Most of the time it will probably be too small to notice. (There is great disagreement on this point (speed bump), but limited testing shows COALESCE “can” be faster depending on how it is being used.)
  • If all columns passed in are null, COALESCE returns null.
  • My example above uses column names, but they could also be functions or constant values.

*Other examples of short-circuit functionality are DECODE and CASE.

Advertisements

Aggregating your data in the query

How the data is stored rarely matches how we want it displayed. How often you need to aggregate data will normally hinge on the degree of normalization your data architect has chosen.

As with quite a few of the items I have written about or plan to write about, there are multiple ways to accomplish what is being described. Chances are that if you are a ColdFusion developer, you already perform this type of aggregation in the middle tier. That way is no less valid, especially if you are dealing with a shared query and messing with it (technical term there) might cause other developers or applications problems. If given the opportunity though, I always try to let the database do what the database does well. Deal with the data.

Oracle 11g introduced some new functionality that easily allows you to aggregate data directly in your query. The function is called listagg(). Prior to Oracle 11g, to accomplish the same goal you needed to create custom datatypes and write your own functions. While the solution was genius, it was often more complex than people wanted to take on. If you are on an older version of Oracle, I recommend checking out the AskTom (http://asktom.oracle.com/) site and search for “stragg”. I could write a post about the function, but I don’t think I could add more than Tom’s site provides.

I am going to demonstrate the listagg() function in SQLPlus; obviously the query demonstrated can go in your cfquery tag.

/*	CREATE SOME TEMP TABLES	*/
SQL> CREATE TABLE CCH_PERSON
  2  (  PERSON_PK               NUMBER,
  3     PERSON_NAME             VARCHAR2(30),
  4     IS_ACTIVE               NUMBER(1) DEFAULT 1 NOT NULL,
  5     PROJECT_FK              NUMBER,
  6     CONSTRAINT PK_CCH_PERSON PRIMARY KEY(PERSON_PK)
  7  );

Table created.

SQL>
SQL> CREATE TABLE CCH_PROJECTS
  2  (  PROJECT_PK      NUMBER,
  3     PROJECT_NAME    VARCHAR2(30),
  4     CONSTRAINT PK_CCH_PROJECTS PRIMARY KEY(PROJECT_PK)
  5  );

Table created.

SQL>
SQL> CREATE TABLE CCH_PERSON_PROJECTS_J
  2  (  PERSON_FK       NUMBER  NOT NULL,
  3     PROJECT_FK      NUMBER  NOT NULL
  4  );

Table created.

SQL> set feedback off;
SQL>
SQL> /* CREATE SOME PEOPLE      */
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('10','Chris');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('11','Anthony');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('12','Tim');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('13','John');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('14','Fred');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('15','Sam');
SQL> commit;
SQL>
SQL> /* CREATE SOME PROJECTS    */
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('1','Big Blog');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('2','Project Tracking');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('3','Property DB');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('4','Employee Relations');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('5','Billing System');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('6','World Domination');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('7','Pet Project');
SQL> commit;
SQL>
SQL> /* MAKE PEOPLE WORK ON PROJECTS    */
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('10','1');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('10','2');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('10','3');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('11','4');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('11','5');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('12','6');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('13','7');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('15','5');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('15','1');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('15','6');
SQL> COMMIT;
SQL> set feedback on;

--	RUN A QUERY TO FIND OUT WHAT PEOPLE ARE WORKING ON
SQL> SELECT     A.PERSON_NAME,
  2             C.PROJECT_NAME
  3  FROM       CCH_PERSON A,
  4             CCH_PERSON_PROJECTS_J B,
  5             CCH_PROJECTS C
  6  WHERE      A.PERSON_PK     = B.PERSON_FK(+)
  7     AND     B.PROJECT_FK    = C.PROJECT_PK(+)
  8  ORDER BY A.PERSON_NAME;

PERSON_NAM PROJECT_NAME
---------- ------------------------------
Anthony    Employee Relations
Anthony    Billing System
Chris      Big Blog
Chris      Project Tracking
Chris      Property DB
Fred
John       Pet Project
Sam        World Domination
Sam        Big Blog
Sam        Billing System
Tim        World Domination

11 rows selected.

SQL> -- ADD SOME COLUMN FORMATTING SO THE DISPLAY IS NICE
SQL> column PERSON_NAME format a10
SQL> column PROJECTS format a46
SQL>
SQL> -- AGGREGATE THE DATA
SQL> SELECT     A.PERSON_NAME,
  2             LISTAGG(C.PROJECT_NAME,', ') WITHIN GROUP (ORDER BY A.PERSON_NAME) AS PROJECTS
  3  FROM       CCH_PERSON A,
  4             CCH_PERSON_PROJECTS_J B,
  5             CCH_PROJECTS C
  6  WHERE      A.PERSON_PK = B.PERSON_FK(+)
  7     AND     B.PROJECT_FK = C.PROJECT_PK(+)
  8  GROUP BY A.PERSON_NAME
  9  ORDER BY A.PERSON_NAME;

PERSON_NAM PROJECTS
---------- ----------------------------------------------
Anthony    Billing System, Employee Relations
Chris      Big Blog, Project Tracking, Property DB
Fred
John       Pet Project
Sam        Big Blog, Billing System, World Domination
Tim        World Domination

6 rows selected.

SQL> -- CLEAN UP AFTER YOURSELF
SQL> DROP TABLE CCH_PERSON;
Table dropped.

SQL> DROP TABLE CCH_PERSON_PROJECTS_J;
Table dropped.

SQL> DROP TABLE CCH_PROJECTS;
Table dropped.

SQL> PURGE RECYCLEBIN;
Recyclebin purged.

Ok… So what is happening here? You are running a query to collect two columns. Person and Project. Person is the value that you group on and Project is the data you want to aggregate. ListAgg() has two input parameters; column to aggregate, and delimeter. Then the ‘WITHIN GROUP (…’ tells Oracle which group to aggregate over.

The above example is using Listagg() as a group-set aggregate. It has some other options as well such as an analytical function, but that would be a considerably longer post. Maybe down the road.

Listagg() is a easy but powerful function to implement… As soon as you use it once, you will want to implement it everywhere.

Insert or update? Upsert!

For years, when saving data to the database I have always run the logic “Does the record exist? If so, update it. If not, insert a new record.”

Difficult? No. Do I get tired of writing it? Yeah.

While there are many ways to deal with this, below is the method I like to use.

I have the same example below in both SQL Plus and ColdFusion.

SQL Plus Example

CREATE TABLE UPSERT_TAB
(	UPSERT_TAB_PK	NUMBER(1),
	UPSERT_VALUE 	VARCHAR2(30)
);

GRANT SELECT ON UPSERT_TAB TO SNWACF;

INSERT INTO UPSERT_TAB VALUES (1,'Dog');
INSERT INTO UPSERT_TAB VALUES (2,'Cat');
COMMIT;

/*	TRY TO UPDATE A RECORD THAT DOES NOT EXIST	*/
begin
	UPDATE	UPSERT_TAB
	SET 	UPSERT_TAB_PK = 3,
	        UPSERT_VALUE = 'Big Foot'
	WHERE 	UPSERT_TAB_PK = 3;
	--
	IF ( sql%rowcount = 0 )
	    THEN
	    INSERT INTO UPSERT_TAB
	        VALUES (3, 'Big Foot');
	END IF;
end;
/

SQL> begin
  2     UPDATE  UPSERT_TAB
  3     SET     UPSERT_TAB_PK = 3,
  4             UPSERT_VALUE = 'Big Foot'
  5     WHERE   UPSERT_TAB_PK = 3;
  6     --
  7     IF ( sql%rowcount = 0 )
  8         THEN
  9         INSERT INTO UPSERT_TAB
 10             VALUES (3, 'Big Foot');
 11     END IF;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM UPSERT_TAB;

UPSERT_TAB_PK UPSERT_VALUE
------------- ------------------------------
            1 Dog
            2 Cat
            3 Big Foot

/*	Now try an update	*/

SQL> begin
  2     UPDATE  UPSERT_TAB
  3     SET     UPSERT_TAB_PK = 2,
  4             UPSERT_VALUE = 'Cats are weird'
  5     WHERE   UPSERT_TAB_PK = 2;
  6     --
  7     IF ( sql%rowcount = 0 )
  8         THEN
  9         INSERT INTO UPSERT_TAB
 10             VALUES (2, 'Cats are weird');
 11     END IF;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM UPSERT_TAB;

UPSERT_TAB_PK UPSERT_VALUE
------------- ------------------------------
            1 Dog
            2 Cats are weird
            3 Big Foot

/*	rollback the data and try it in CF	*/
DELETE FROM UPSERT_TAB WHERE UPSERT_TAB_PK = 3;
UPDATE UPSERT_TAB SET UPSERT_VALUE = 'Cat' WHERE UPSERT_TAB_PK = 2;
COMMIT;

Now try the same thing in ColdFusion

<b>Fresh table of data before we start messin' with it.</b><BR>

<cfquery name="getFreshTable" datasource="#application.strDatasource#" >
	SELECT * FROM UPSERT_TAB
</cfquery>

<cfdump var="#getFreshTable#" metainfo="false" label="getFreshTable">
<!----------------------------------------------------------------------->

<br><br>
<b>Run the first upsert and then look at the table again.</b><br>

<cfquery name="Upsert1" datasource="#application.strDatasource#">
	begin
		UPDATE  UPSERT_TAB
		SET     UPSERT_TAB_PK	= <cfqueryparam cfsqltype="cf_sql_numeric" value="3" >,
				UPSERT_VALUE 	= <Cfqueryparam cfsqltype="cf_sql_varchar" value="Big Foot">
		WHERE   UPSERT_TAB_PK 	= <cfqueryparam cfsqltype="cf_sql_numeric" value="3" >;
		--
		IF ( sql%rowcount = 0 )
		THEN
			INSERT INTO UPSERT_TAB
				VALUES (
					<cfqueryparam cfsqltype="cf_sql_numeric" value="3" >, 
					<Cfqueryparam cfsqltype="cf_sql_varchar" value="Big Foot">
				);
		END IF;
	end;
</cfquery>

<cfquery name="getUpsertResult1" datasource="#application.strDatasource#" >
	SELECT * FROM UPSERT_TAB
</cfquery>

<cfdump var="#getUpsertResult1#" metainfo="false" label="getUpsertResult1">
<!-------------------------------------------------------------------->

<br><Br>
<b>Run the second upsert and then look at the table yet again.</b>

<cfquery name="upsert2" datasource="#application.strDatasource#">
	begin
		UPDATE  UPSERT_TAB
		SET     UPSERT_TAB_PK = <cfqueryparam cfsqltype="cf_sql_numeric" value="2" >,
				UPSERT_VALUE = <Cfqueryparam cfsqltype="cf_sql_varchar" value="Cats are weird">
		WHERE   UPSERT_TAB_PK = <cfqueryparam cfsqltype="cf_sql_numeric" value="2" >;
		--
		IF ( sql%rowcount = 0 )
		THEN
			INSERT INTO UPSERT_TAB
				VALUES (
					<cfqueryparam cfsqltype="cf_sql_numeric" value="2" >, 
					<Cfqueryparam cfsqltype="cf_sql_varchar" value="Cats are weird">
				);
		END IF;
	end;
</cfquery>

<cfquery name="getUpsertResult2" datasource="#application.strDatasource#" >
	SELECT * FROM UPSERT_TAB
</cfquery>

<cfdump var="#getUpsertResult2#" metainfo="false" label="getUpsertResult2">

Screenshot:
upsert

Generate an artificial list of dates in a query

If you are running a query against a data set that is sparsely populated (with regards to the dates), but your resulting recordset needs all dates represented… One option in the past has been to create a “dummy” table containing dates that you could then join to.  These dummy tables will result in somebody years down the road saying “What h*** is this?!”  I say this often and with great enthusiasm.

Instead of using a dummy table, you could also use a query as your join for dates.  This example returns all date for one year from today.  The same logic could be applied to a sequence of numbers:

select    trunc(sysdate)+level as adate
from      dual
connect   by level <=365;

ADATE
---------
30-OCT-13
31-OCT-13
01-NOV-13
02-NOV-13
03-NOV-13
04-NOV-13
05-NOV-13
06-NOV-13
07-NOV-13
etc.

To do basically the same thing between two arbitrary dates…

SELECT    TRUNC(to_date('01-JAN-2011'))+LEVEL AS ADATE
FROM      DUAL
CONNECT   BY LEVEL <=to_date('01-JAN-2011') - to_date('01-JAN-2010');

ADATE
---------
02-JAN-11
03-JAN-11
04-JAN-11
05-JAN-11
06-JAN-11
07-JAN-11
08-JAN-11
09-JAN-11
10-JAN-11
etc.

Pivot your query

*Note: Syntax below is for Oracle.

Similar functionality can easily be implemented in ColdFusion, but I lean towards putting this type of thing in the originating query instead of massaging the data after retrieval from the database.  Massaging data can be fun, but like many things in life, not always a good idea.

Every time I hear the word “pivot”, I can’t help but think of Ross and Joey trying to carry a sofa. Get the reference? No? Ok… moving on…

In the past, when you wanted to “pivot” rows of data into columns, you could use the “max(decode(…))” method to accomplish the task (If anybody wants me to explain that, hit me up in the comments).  In Oracle 11g, the “pivot” and “unpivot” keywords have been added that will allow pivoting your data from rows to columns as well as from columns to rows.  I will demonstrate the “pivot” method.  This is a technique that should be in the library for anybody wishing to analyze data.

First, let’s set up some test data to work with.

Note: I wouldn’t design tables this way, but for the sake of illustration I will keep it simple.

CREATE TABLE WORKER
( WORKER_ID         NUMBER,
WORKER_NAME         VARCHAR2(30),
WORKER_DEPT_NO      NUMBER,
WORKER_JOB          VARCHAR2(30),
WORKER_SALARY       NUMBER(6),
WORKER_FUNCTION     VARCHAR2(30)
);

INSERT INTO WORKER VALUES (100,'Chris',20,'Managerial',100000,'MANAGE');
INSERT INTO WORKER VALUES (110,'Tim',20,'Programmer',60000,'WORK');
INSERT INTO WORKER VALUES (120,'Larry',30,'Programmer',75000,'WORK');
INSERT INTO WORKER VALUES (130,'Mike',30,'Managerial',130000,'MANAGE');
INSERT INTO WORKER VALUES (140,'Lisa',30,'Programmer',65000,'WORK');
INSERT INTO WORKER VALUES (150,'Jan',30,'Programmer',55000,'WORK');
INSERT INTO WORKER VALUES (160,'Akash',30,'Intern',25000,'WORK');
INSERT INTO WORKER VALUES (170,'Summer',20,'Intern',22000,'WORK');
INSERT INTO WORKER VALUES (200,'Ian',20,'Intern',23000,'WORK');
INSERT INTO WORKER VALUES (180,'Josh',40,'Managerial',110000,'MANAGE');
INSERT INTO WORKER VALUES (190,'Monika',40,'Programmer',95000,'WORK');
COMMIT;

Get a count of staff by department number.

SELECT    WORKER_DEPT_NO, COUNT(WORKER_DEPT_NO) AS WORKER_COUNT
FROM      WORKER
GROUP BY  WORKER_DEPT_NO;

WORKER_DEPT_NO WORKER_COUNT
-------------- ------------
 30                5
 40                2
 20                4

The query returns three rows; one for each department with a count of staff for that department. Neat. Ok… not really.

What if you wanted the department numbers as columns? Pivot is the answer.

WITH QRY_WORKER AS
 ( SELECT WORKER_DEPT_NO
   FROM   WORKER
 )
SELECT *
FROM   QRY_WORKER
PIVOT ( COUNT(WORKER_DEPT_NO) FOR (WORKER_DEPT_NO) IN (20,30,40) );

20     30     40
----------------------------
 4     5      2

So that is a little more neat. If you are unfamiliar with the “with” above, go read about it! You will be glad you did. (Maybe I will write a future post about that.) Now we have department numbers as columns and the counts represented in the data. Note that the values for the department numbers are explicitly listed.

Let’s try something similar finding min/max salaries for each of the non-managerial positions by department.

/* MAKE THE OUTPUT PRETTY IN SQLPLUS. PRETTY IS A SUBJECTIVE TERM */
SET LINESIZE 100;
WITH QRY_WORKER AS
 ( SELECT WORKER_DEPT_NO, WORKER_JOB, WORKER_SALARY
   FROM   WORKER
 )
SELECT *
FROM   QRY_WORKER
PIVOT ( MIN(WORKER_SALARY) AS MINSAL,
 MAX(WORKER_SALARY) as MAXSAL
 FOR (WORKER_JOB)
 IN ('Intern' AS INTERN,
 'Programmer' as PROGRAMMER
 )
 )
ORDER BY WORKER_DEPT_NO;

WORKER_DEPT_NO INTERN_MINSAL INTERN_MAXSAL PROGRAMMER_MINSAL PROGRAMMER_MAXSAL
-------------- ------------- ------------- ----------------- -----------------
 20            22000         23000         60000             60000
 30            25000         25000         55000             75000
 40            95000         95000

More data would make this more interesting; a small data set means there isn’t a big variation in the data represented by min/max… but you get the idea.

In the query above three rows are selected. The WORKER_JOB is the pivot, the salaries are aggregated and the departments are returned as distinct rows. Note the different values for the pivot are explicitly listed.

The inline view QRY_WORKER contains three columns, the salary is aggregated, the job is transposed into multiple columns, and the last column is used for grouping. Department, which contains three distinct values, returns one row each.

Go clean up after yourself.

SQL> DROP TABLE WORKER;
Table dropped.
SQL> PURGE RECYCLEBIN;
Recyclebin purged.

Returning records to CF from an Oracle Package

I realize this is old-hat to most… But if someone hadn’t helped me figure it out back in the day; Well… I probably would have figured it out, but somebody helped save me a lot of time.

Running queries against tables and views in ColdFusion is a piece of cake, but then sometimes you might want to call a stored procedure (or package) and let Oracle do the heavy lifting.  Below is the method for doing just that.  This example will show how to create the package in Oracle (all test code included), and then call it from ColdFusion with the package returning a record set.

Start by creating the base objects in Oracle we will test against.

SQL> CREATE TABLE MY_TAB
2  (  COL1    NUMBER,
3     COL2    VARCHAR2(20),
4     COL3    DATE
5  );

Table created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('1','ABC',SYSDATE);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('2','DEF',SYSDATE+1);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('3','GHI',SYSDATE+2);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('4','JKL',SYSDATE+3);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('5','MNO',SYSDATE+4);
1 row created.

SQL> COMMIT;

Commit complete.

Create the package we are going to call.

SQL> CREATE OR REPLACE PACKAGE MY_PACK
2  AS
3     ----------------------CURSORS---------------------
4     TYPE O_CUR IS REF CURSOR;
5     --------------------------------------------------
6     PROCEDURE       MY_PROC
7     ( O_REX  OUT O_CUR );
8     --------------------------------------------------
9  END MY_PACK;
10 /
Package created.

SQL> CREATE OR REPLACE PACKAGE BODY MY_PACK
2  AS
3  PROCEDURE       MY_PROC
4  ( O_REX  OUT O_CUR )
5  IS
6  BEGIN
7   OPEN O_REX FOR
8    SELECT COL1, COL2, COL3
9    FROM  MY_TAB
10    ORDER BY COL1;
11  END MY_PROC;
12 END MY_PACK;
13 /
Package body created.

Create a CF template to hit the package and output the data.  

(Note… I only included code needed for the example)

<CFSTOREDPROC PROCEDURE="MY_PACK.MY_PROC" DATASOURCE="myDsn">
  <!--- OUTS --->
  <CFPROCRESULT NAME=O_REX>
</CFSTOREDPROC>

<CFDUMP VAR="#O_REX#">

Results would look like this.

cfprocdump

Notes:

  • There is a proper time and place for different connections to the database and this is just one option.
  • If you want to return multiple recordsets back, add resultset = “2”, resultset = “3” etc. to the CFPROCRESULT tags.
  • Regarding the Oracle code… I kept it simple for the example.  Please no lectures on how I am using a cursor.
  • As a rule, I always (almost always) put procedures inside packages.  I don’t believe this is a requirement.

An Oracle “roles and privs” script

As I mentioned in my “An Oracle ‘who’ script” (https://hagoodc.wordpress.com/2013/10/26/8/) post, I have several scripts I always drop into the oracle bin directory when I start working somewhere.

Next on the list… @rolesprivs.sql

This script quickly displays the roles and privs granted to an Oracle user. The script below displays both normal roles as well as system privs. Nothing special going here, but running @rolesprivs is quicker than running each query seperately.

</pre>
REM ########################################################################
REM ## Author : Chris Hagood
REM ##
REM ## This script will display role/priv information for the username
REM ## provided at the prompt
REM ##
REM #######################################################################

ACCEPT v_username PROMPT 'Enter USERNAME> '
set pages 100 lines 100 verify off feedback off;

col grantee heading 'USERNAME' format a15;
col granted_role heading 'Role' format a35;
col admin_option heading 'Admin|Option?' format a7
col default_role heading 'Default|Role?' format a7

break on grantee;

select grantee, granted_role, admin_option, default_role
from dba_role_privs
WHERE GRANTEE = UPPER('&v_username') or GRANTEE LIKE UPPER('%&v_username%')
order by grantee,granted_role;

COL grantee heading 'USERNAME' format a15
col privilege heading 'PRIVILEGE' format a35
col admin_option heading 'Admin|Option?' format a7

select grantee, privilege, admin_option
from dba_sys_privs
WHERE GRANTEE = UPPER('&v_username') or GRANTEE LIKE UPPER('%&v_username%')
order by grantee, privilege;

clear columns;
clear breaks;
set verify on;
set feedback on;

A couple of other notes:

  • The script doesn’t have to go in your bin directory.  You can tell SQL Plus where to look.  I just use the bin directory.
  • I have been using this script for years.  When I started, I grabbed it from somebody and have modified it for my use.  I would like to give credit to who started it… But I have no idea who they are.

Download: rolesprivs.sql

Screenshot:

rolesprivs screenshot

CFClient is getting a bad rap

As I was sitting in the Adobe Cold Fusion Summit 2013 keynote, one of the topics was a new feature called CFClient. Since I live in the middle tier/back end of the CF development world I was only mildly interested in the new feature. While listening I was a bit surprised by the nearly immediate bashing of the feature. The CFClient portion of the keynote wasn’t even over before everybody had already decided it was a horrible choice by Adobe to even develop.

I am not a mobile app developer, but I suspect that existing developers are not the target audience for CFClient. I remember back in the early days of HTML, I scoffed when applications like PageMill were released. I thought “If you want a web site, you should hire somebody who knows how to build them!!!” Admittedly, that was a very self-serving opinion to have. PageMill, without an understanding of what was happening under the hood, suddenly gave people the ability to create sites with absolutely dreadful code powering them. This wasn’t a positive result, but it also gave people an avenue to learn. Suddenly the world-wide-web wasn’t quite as intimidating. I would say the majority of people created a simple site and walked away… But there were some that looked under the hood. Tried to understand what was happening, and then shed the shackles of the WYSIWYG editor to write there own code.

I think CFClient is headed down the same road. Will the apps be pristine? Doubtful. Will the code meet the standards of “true mobile app” developers? I don’t know, but I don’t think so. Will new people finally decide to give mobile app development a try? Yes! Many will build something akin to “Hello World”, but I think several years down the road, the CF community will have new power developers due to trying out this new CFClient technology.

One of the stats I remember hearing in Ben Forta’s keynote was something like “25% of companies have mobile apps, but 85% want them”. I am roughly paraphrasing here. I think CFClient will bring those numbers together. Sure… I expect to see some really awful apps, but I also expect to see some gems as well. From Adobe’s perspective, I think this only has an upside. There will be sales decided by the managers being marketed to. Some companies don’t have mobile developers and they will pull the trigger on ColdFusion specifically because of this feature. (My opinion here). Adobe sales will increase and the development community will grow. I can’t see that as a bad thing.

To the advanced developers out there saying “I wish Adobe would focus on my needs instead of marketing to managers!”. Remember, that managers are the ones who decide to pony up the money for the CF Enterprise license. I don’t love the idea of bowing to one group of people, but this is an important group that needs some attention. I encourage existing mobile app developers to welcome new developers to the community and to help them understand how to make their apps better. If we immediately dismiss them simply because they started by using CFClient, we are going to discourage people from embracing CF.

Will CFClient survive for years to come? Or will the 2017 CFSummit keynote include this as a slide describing CFClient as a failure? I don’t know, but I look forward to finding out.

If you would like some information from a great source, Ram’s Blog (http://ramkulkarni.com/blog/cfsummit2013-day1-and-cfclient/) has an outstanding entry on the topic.

ColdFusion Summit 2013 (CFSummit2013)

This was my first CF conference and I walked away extremely happy with the experience. I wasn’t sure what exactly to expect when I forked over the early bird price… But the early bird price was low enough I didn’t have to think about it too hard. For me, being a Las Vegas local, I didn’t have to worry about traveling but appreciated those who did. I believe the conference calling Las Vegas home probably helped the attendance level quite a bit as well. Folks sitting on the fence, I assume, were swayed by the prospect of a trip to Vegas on top of the conference. At least, quite a few people I talked to said as much.

Mandalay Bay is on my short list of favorite properties here in town. That being said, I think the venue was too much given that CFSummit2013 was only sporting 500 attendees. A smaller property would have fostered the “community” feeling even more. I hope Adobe decides to make Vegas the home of this conference for years to come.

Again, being my first CF conference, I wasn’t sure what to expect in the way of content to be presented. This really blew me away. After nearly every session, I walked away wishing I could get home to my computer to try something new out. A few of the presenters struggled with audio problems… screen problems… angering the live demo gods… etc. But even those left me with new ideas and new knowledge I wanted to apply.

Ben Forta’s (http://www.forta.com/) keynote on the first day was perfectly on point. For years I have heard “ColdFusion is dead.” Over and over (AND OVER) again. From PHP folks, Java folks, and especially those associated with the Microsoft world. Hearing that CF sales are growing instead of being on the decline made me really happy. When CF was purchased by Adobe, I was really worried about CF’s future. With the preview of what is on the horizon, I can’t wait! My only heartache is how long I am going to have to wait for my next version. Middle of next year was not the answer I was hoping for.

A couple of the sessions that I really enjoyed were:

Using Solr for Better, More Relevant Searches – Raymond Camden (http://www.raymondcamden.com/)
Being a fan of Raymond’s blog for years, I was happy to finally see him in person. The content covered is something I plan to try applying immediatly on Monday. The best part was the presentation style. Raymond made everybody feel at ease and peppered in humor along with content. Great way to start the day!

Object-Oriented ColdFusion – Dan Wilson (http://www.nodans.com/)
This presentation was a bit more philosophy than hard examples. Code was presented, but as more of an illustration than instruction. This was an overview course I think and since I was the proverbial choir instead of somebody needing to be converted, it didn’t make as much of an impact on me. I spent most of the session nodding in approval for what was being said which is a good sign I think. Dan was a very polished presenter. He answered questions easily and made all his points clearly. I missed his other session, but if I am ever at a conference he presents at again, my butt will be in the chair listening.

Pixel Perfect PDF Generation – Tim Cunningham (http://cfmumbojumbo.com/cf/)
Now I have to say this first… PDF generation has been a thorn in my side for years. It has been a constant struggle to match the quality of print data with screen presentation. So Tim had my attention from the beginning. His subject matter was like music to my ears and I was really sad when the session ended. One thing Tim did which I think was genius was engaging the audience before the session began. Questions about first time attenders (to a CF conference) etc. You could tell he was genuinely excited by the response to the conference and that bled out into the audience. I also thought Tim’s intro to the day 2 key note was outstanding. It is the reason I started this blog.

Start Using Amazon Web Services in Your ColdFusion Apps – Brian Klass (http://www.iterateme.com/blog/)
Oh…my… god… That is the best way to describe the 1 hour knowledge bomb he dropped on us. Since I realized early on, the content wasn’t something that I would use, I almost left. Brian’s presentation skills kept me sitting and kept me engaged in spite of not being that interested in the content. In my current organization, there is just no way AWS would be an option. This presentation must have had over 100 slides. Everything was clear, concise, and on point. When there were questions, Brian would answer them and as if almost on cue the next slide would be on the topic he was just asked about. Easily four hours of material covered in a one hour session. Just an amazing hour to witness.

I missed the closing session on the last day, but am excited to hear that it appears there will be a ColdFusion Summit 2014. I definaly plan to be there!

An Oracle “who” script

Since I am not sure where to start, I thought I would begin by posting some utility scripts that I always use in Oracle. When I start a new project, one of the first things I do is drop my scripts (starting with this one) into the bin directory.

If you work in an environment that has more than one instance… And honestly… there should always be at least a Development and Production environment; Knowing where you are before you run anything is REALLY important.  Figuring out where you are isn’t really that hard, but typing @who (my script) just makes it a little bit quicker.

The script below determines who you are connected as and where you are connected.  It then sets the SQL> prompt to reflect that information.

REM ########################################################################
REM ## Author : Chris Hagood
REM ##
REM ## This script when run will change the SQL> prompt to display
REM ## the current user logged in and the db connected to.
REM ##
REM #######################################################################

set pause off
set termout off
column the_db_name new_value the_db_name
SELECT USER||'@'||INSTANCE_NAME AS THE_DB_NAME FROM V$INSTANCE;
set sqlp "&the_db_name.> "
set termout on
set serveroutput on

A couple of other notes:

  • The scripts don’t have to go in your bin directory.  You can tell SQL Plus where to look.
  • I prefer to always run @who before I make any data/structure changes, however you could easily write your own connect script that would set the prompt every time you log in.

Download: Who.sql

Screenshot:

who screenshot