I'm going to have to find a decent PL/SQL reference. The language seems like VFP -- it's huge and there's several ways to do the same thing. Put both of those together and it's easy to get lost.

My fundamental question is how do I create a cursor in PL/SQL with a SELECT statement structure that is conditioned on a a passed parameter? My issue seems to be that I must declare the cursor above my BEGIN...END block but that I can only have IF...ELSE...END IF logic *inside* the BEGIN...END loop. Or at least that's what the compiler seems to be telling me.

The cursor has to be the kind that I can LOOP through, not a REF CURSOR. Here's how I'm doing it now (note the NULL check within the WHERE clause). The code below works, but I'd rather have a choice among two SELECT statements, one with a WHERE and one without a WHERE, conditioned on whether the passed IN parameter is NULL.

Note that what I'd *like* to do is commented out at the top of the BEGIN block. I get an error during compile that way, indicating that I can't declare the cursor inside the BEGIN...END. That makes sense, I guess, but how can I do this without kludging the cursor's SELECT, as I've done here?

TIA and sorry for what I'm sure is a 1.01 (not even 101!) question.

Kelly

CREATE OR REPLACE PACKAGE AbbrvData  AS

/* Package: AbbrvData 
   By: 	    Kelly Conway, WSA CORP 
   Updated: 2/26/2002 
   Purpose: Provides all data access and manipulation for the ABBRV table. 
            [Well, it will eventually...<s>]
*/ 

-- Define one "TABLE OF" per field to appear in returned cursor.  
TYPE tblAbbrv_code IS TABLE OF abbrv.abbrv_code%TYPE
  INDEX BY BINARY_INTEGER;
TYPE tblTitle_1    IS TABLE OF abbrv.title_1%TYPE
  INDEX BY BINARY_INTEGER;
TYPE tblTitle_2    IS TABLE OF abbrv.title_2%TYPE
  INDEX BY BINARY_INTEGER;
TYPE tblTitle_3    IS TABLE OF abbrv.title_3%TYPE
  INDEX BY BINARY_INTEGER;
TYPE tblPriority_C IS TABLE OF abbrv.priority_c%TYPE
  INDEX BY BINARY_INTEGER;
TYPE tblP_Key      IS TABLE OF abbrv.p_key%TYPE
  INDEX BY BINARY_INTEGER;

PROCEDURE GetData ( key_value_in  IN  abbrv.abbrv_code%TYPE,
		    abbrv_code    OUT tblAbbrv_code,
		    title_1       OUT tblTitle_1,
		    title_2       OUT tblTitle_2,
		    title_3       OUT tblTitle_3,
		    priority_c    OUT tblPriority_C,
		    P_Key         OUT tblP_Key );

END; /* Package Declaration */ 
/

CREATE OR REPLACE PACKAGE BODY AbbrvData  AS

PROCEDURE GetData ( key_value_in  IN  abbrv.abbrv_code%TYPE, 
		    abbrv_code    OUT tblAbbrv_code,
		    title_1       OUT tblTitle_1,
		    title_2       OUT tblTitle_2,
		    title_3       OUT tblTitle_3,
		    priority_c    OUT tblPriority_C,
		    P_Key         OUT tblP_Key ) IS

/* 
   NOTE: The key_value_in IN parameter is optional. If not passed, 
   ALL records are returned. 

   NOTE: Despite the existence of p_key in this table, whoever  
   set up the database decided for some reason to use abbrv_code 
   as the foreign key in related tables. 
*/ 

	CURSOR c_data IS
	SELECT abbrv_code, title_1, title_2, title_3, priority_c, p_key
	  FROM abbrv
	 WHERE key_value_in IS NULL 
	    OR abbrv_code = key_value_in
	 ORDER BY abbrv_code;

	table_index NUMBER := 1;

	BEGIN

/* This section is commented because the compiler chokes on the first
   "c_data", saying that it expected a symbol like ;@ etc.

	   IF key_value_in IS NULL
	   THEN

  	      CURSOR c_data IS
	      SELECT abbrv_code, title_1, title_2, title_3, priority_c, p_key
	        FROM abbrv
	       ORDER BY abbrv_code;

	   ELSE

	      CURSOR c_data IS
 	      SELECT abbrv_code, title_1, title_2, title_3, priority_c, p_key
	        FROM abbrv
	       WHERE abbrv_code = key_value_in

	   END IF;

   *** End of commented section ***

*/

           -- Loop through the cursor and fill the TABLE arrays.
           -- Note that, by using the {resultset ...} syntax featured
           -- in several MS KB articles wrt getting cursors from Oracle 
           -- procedures, this mess of arrays is returned as columns of 
           -- a single cursor.
	   FOR SingleRecord IN c_Data LOOP

		abbrv_code ( table_index ) := SingleRecord.abbrv_code;
		title_1    ( table_index ) := SingleRecord.title_1;
		title_2    ( table_index ) := SingleRecord.title_2;
		title_3    ( table_index ) := SingleRecord.title_3;
		priority_c ( table_index ) := SingleRecord.priority_c;
		p_key      ( table_index ) := SingleRecord.p_key;

		table_index := table_index + 1;

	   END LOOP;

	END;

END; /* Package Body Declaration */ 
/

The VFP syntax I'm using to execute this procedure:

nConnection = SQLSTRINGCONNECT ( ... )

cSQL = "{call AbbrvData.GetData(?cCode, {resultset 1000, " ;
     + "abbrv_code, title_1, title_2, title_3, priority_c, p_key})}"

cCode = "CEO"
? SQLEXEC ( nConnection, cSQL, "c_Abbrv" )
* One record is returned, the one with abbrev_code = "CEO"

cCode = NULL
? SQLEXEC ( nConnection, cSQL, "c_Abbrv" )
* All ABBRV table records are returned (since the table has < 1000 records)