Dynamic SQL and strong cursor type

From: Elizabeth <EDalton_at_reviewjournal.com>
Date: 1 Oct 2002 13:33:09 -0700
Message-ID: <bbf2637b.0210011233.55128560_at_posting.google.com>

If anyone out there can offer me assistance, I would greatly appreciate it.

I have a java bean calling a stored procedure which executes dynamic sql. (a user may conduct a search using between 1 and 3 columns to search for a user-specified string)

With dynamic sql, I am aware that a strongly typed cursor cannot be used. So, I have a weakly typed cursor retrieving the values and fetching them into my custom record type. This is all working great...except that I cannot get my values back in my bean! Using JDBC I cannot return a record type, only a reference cursor, but I cannot return a weakly typed cursor.

I have pasted my code below. Please help if you can. Thank you very much. Elizabeth

FUNCTION  search (col       VARCHAR2,
                  string    VARCHAR2,
                  yr        VARCHAR2)   RETURN TYPES.search_cursor AS

        --instantiate strongly typed cursor
        search_db_cursor   TYPES.search_cursor;



	--instantiate weakly typed cursor


	TYPE search_ref_cursor IS REF CURSOR;
		 ref_cursor search_ref_cursor;

        --variables
        boColName1         BEST.category%TYPE;
        boColName2         BEST.category%TYPE;
        boColName3         BEST.category%TYPE;
        boText             VARCHAR2(500)    :=  string;
        boYr               BEST.year%TYPE  :=  TO_NUMBER(yr);
        boColInt           VARCHAR2(500) := LTRIM(UPPER(col));
        delimComma         NUMBER := INSTR(col, ',');
        sqlStatement       VARCHAR2(500);
	dataRec 	   TYPES.search_database;

        --location of the first and last space delimiters
        firstSpace         NUMBER := INSTR(col, ' ');
        lastSpace          NUMBER;


        BEGIN
/*  *************** PARSE THE COL PARAMETER *****************   */
            IF delimComma = 0 AND firstSpace = 0
                THEN
                    --only one column to search, so put it in
boColName1
                    boColName1 := boColInt;
                    boColName2 := NULL;
                    boColName3 := NULL;
            ELSIF delimComma != 0
                THEN
                    --more than one column to search
                    boColName1 := SUBSTR(boColInt, 1, delimComma-1);

                    --assign the remainder to boColName2 -- if no more
spaces   then there are no more columns to search
                    boColName2 :=
LTRIM(SUBSTR(boColInt,delimComma+1));
                    firstSpace := INSTR(boColName2, ' ');
                    IF firstSpace = 0
                        THEN
                            --no more columns to search
                            boColName3 := NULL;
                    ELSIF delimComma != 0
                        THEN
                            --more than two columns to search
                            boColName2 := SUBSTR(boColInt, 1,

delimComma-1);

                            --assign the remainder to boColName3
                            boColName3 :=
LTRIM(SUBSTR(boColInt,delimComma+1));
                    END IF;
            END IF;
/*  *************** END PARSE THE COL PARAMETER ******************/

/* DYNAMIC SQL DEPENDANT UPON NUMBER AND NAMES OF COLUMNS PASSED IN ****** */

--initialize the base SQL statement

        sqlStatement := 'SELECT id, winner, pick_type, category
        FROM BEST
        WHERE year = ' || boYr ||' AND active = ''Y'' ';



	--if boColName1 is not null, add it to the base SQL statement as the


ategory and search for "like" boText
        IF boColName1 IS NOT NULL
        THEN
          sqlStatement := sqlStatement || ' AND ( upper(' ||

boColName1 || ') LIKE upper(''%' || (boText) || '%'')';

--if boColName2 is not null, add it to the SQL statement as the
category and search for "like" boText

	IF boColName2 IS NOT NULL
        THEN
          sqlStatement := sqlStatement || ' OR upper(' || boColName2

|| ') LIKE upper(''%' || (boText) || '%'')';

--if boColName3 is not null, add it to the SQL statement as the
category and search for "like" boText

	IF boColName3 IS NOT NULL
        THEN
          sqlStatement := sqlStatement || ' OR upper(' || boColName3

|| ') LIKE upper(''%' || (boText) || '%''))';

--if boColName3 is null, close the SQL statement

	ELSIF boColName3 IS NULL
        THEN
          sqlStatement := sqlStatement || ')';
         END IF;

	 --if boColName2 is null, close the SQL statement
         ELSIF boColName2 IS NULL
         THEN
           sqlStatement := sqlStatement || ')';
         END IF;
         END IF;

	 OPEN ref_cursor FOR
             	 sqlStatement;
		 LOOP

		 FETCH ref_cursor INTO dataRec;
 	           EXIT WHEN ref_cursor%NOTFOUND;

		 END LOOP;

	CLOSE ref_cursor;

				
				
				
         --RETURN search_db_cursor;
         --CLOSE search_db_cursor;

/*END DYNAMIC SQL DEPENDANT UPON NUMBER AND NAMES OF COLUMNS PASSED IN */

	EXCEPTION
	  WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR::' || SQLCODE || '   ' || SQLERRM);

END search; Received on Tue Oct 01 2002 - 22:33:09 CEST