Category Archives: PL SQL

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

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?

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