Oracle - Close Your Cursors

Thu, 02 Nov 2006 18:24:44 GMT

Be sure to close all of your cursors. I'm not a big fan of cursors, but they are useful some of the time.

I added a slightly altered version of the join UDF, found here , to my Oracle box today. When I ran the first test, it ran great. Then I went to run it with a larger result set and it bombed. The error returned was:

ORA-01000: maximum open cursors exceeded

So I looked at the function a little closer, and realized that it was leaving the cursors open. So I added the following line after the end loop:

CLOSE p_cursor;

Now everything works great!

Oracle - Compare a List to a List

Thu, 26 Oct 2006 20:40:43 GMT

Today I came across an issue where I needed to compare one list to another in Oracle before I could return the result set. One list is in a column of the table that I want to return. The other list is going to be passed in. I want the row returned if any value in the first list matches any value in the second. After doing a bit of searching, this is what I came up with.

I thought that it would be easier to work with the list if I could mutate it into it's own table. A friend of mine created a listToTable function in MS SQL Server a while back, so I knew there had to be an Oracle version out there. So I found this link , which I've modified slightly below.

/* you must first create a type for the returned table */
CREATE OR REPLACE TYPE split_table AS TABLE OF VARCHAR2(32767);

/* now we create the actual function */
CREATE OR REPLACE FUNCTION udf_split
    (
        nList VARCHAR2,
        nDelim VARCHAR2 := ','
    ) RETURN split_table PIPELINED
    IS
        wID PLS_INTEGER;
        wList VARCHAR2(32767) := nList;
        wValue VARCHAR2(32767);
    BEGIN
        LOOP
            wID := INSTR(wList,nDelim);
            IF wID > 0 THEN
                PIPE ROW(SUBSTR(wList, 1, wID - 1));
                wList := SUBSTR(wList, wID + LENGTH(nDelim));
            ELSE
                PIPE ROW(wList);
                EXIT;
            END IF;
        END LOOP;
    RETURN;
END udf_split;

From my understanding VARCHAR2(32767) is the largest varchar field you can have.

Now we have to create the package that we're going to call to get our results. This package will contain the procedure that will take in one list and compare it to another using the udf_split function twice.

/* first we create the package */
CREATE PACKAGE upk_test
AS
TYPE cursor_type IS REF CURSOR;
PROCEDURE getResults(
    pList IN VARCHAR2,
    pResults OUT cursor_type);
END upk_test;

/* then we create the package body */
CREATE OR REPLACE PACKAGE BODY upk_test
AS
PROCEDURE getResults(
    pList IN VARCHAR2,
    pResults OUT cursor_type)
IS
BEGIN
    OPEN pResults FOR
      SELECT *
      FROM (
        SELECT a.*,
          CASE WHEN a.list_a IS NOT NULL THEN (
            SELECT COUNT(*)
            FROM TABLE(CAST(udf_split(a.list_a) AS split_table)) b
              INNER JOIN TABLE(CAST(udf_split(pList) AS split_table)) c
                ON b.column_value = c.column_value)
            ELSE 0
          END AS allowed
        FROM my_table a) d
      WHERE d.allowed > 0;
END;
END upk_menu;

Now you can call the procedure for ColdFusion as such:

<cfstoredproc datasource="#myDSN#" procedure="upk_test.getResults">
<cfprocparam cfsqltype="cf_sql_varchar" dbvarname="pList" value="#someList#" />
<cfprocresult name="menu" />
</cfstoredproc>

The above takes in a list like "1,3,7" and compares it to the list column of a table like:

ID	SOMETHING	LIST
1	do	2
2	go	3,6,8
3	say	7

Since rows 2 and 3 have a match to the input list, the package will return the following result set:

ID	SOMETHING	LIST
2	go	3,6,8
3	say	7

What do you think? Is this a long way of going about what I'm trying to do? I would imagine that a good REGEXP would do the trick, but I'm not that good with REGEXP.

Anyone?

Friday

Fri, 15 Sep 2006 19:54:35 GMT

Well, it's Friday. Thank goodness for that.

RSS feed for InstantSpot site fro

Oracle - Close Your Cursors

Oracle - Compare a List to a List

Friday