Hi Tim,

Perhaps you are right and I should just have the query Client side where needed in the VFP code. But I was under the idea that if I keep calling the same procedure in different programs, it should go server side. This is a judgement call. As I'm new to packages I might be holding too tight to that doctorine??? The way I see it is, the things I learn in these small package builds, will help later when it counts more.

I've been able to convert the VFP code into a Package that compiles. But I get an error:

ORA-06512 Connectivity error: [Oracle][ODBC][Ora]ORA-00904: invalid column name
ORA-06512: at "PROD.GET_EMP_COMBO_LIST", line 45
ORA-06512: at line 1

Below is the package. If I remove the referance to the string I built. The cursor is returned. It is the part where the field 'EMP.OUT_DT' is compared to the date variable 'pdCurrDate' If I replace the variable with SYSDATE it works. Or if I do the query part without the quotes and place the date comparison with the variable straight into the query it works. So It's some syntax error on the comparison inside quotes. Can you help me with this last little bug?

CREATE OR REPLACE PACKAGE Get_Emp_Combo_List AS
 Type RetCursor Is Ref Cursor;
 Procedure ReturnEmpComboList(
   pnEmpHireNo IN NUMERIC,
   pnTypeNo IN NUMERIC,
   pdCurrDate IN EMP.OUT_DT%TYPE,
   pCursor OUT RetCursor);

END;
/

CREATE OR REPLACE PACKAGE BODY Get_Emp_Combo_List AS
 Procedure ReturnEmpComboList(
   pnEmpHireNo IN NUMERIC,
   pnTypeNo IN NUMERIC,
   pdCurrDate IN EMP.OUT_DT%TYPE,
   pCursor OUT RetCursor)

 IS
   lcWhereString VARCHAR2(150);
 
 BEGIN
        -- The user wants ALL employees
	IF pnEmpHireNo = 1 THEN
	    lcWhereString := '';
	
	-- The user wants only ACTIVE employees
	ELSIF pnEmpHireNo = 2 THEN
	    lcWhereString := ' AND (EMP.OUT_DT IS NULL OR EMP.OUT_DT >= pdCurrDate) ';
	
	-- The user wants only FORMER employees
	ELSIF pnEmpHireNo = 3 THEN
	    lcWhereString := ' AND (EMP.OUT_DT IS NOT NULL AND EMP.OUT_DT < pdCurrDate) ';
	
	-- The user wants only Current & Former who worked in last 2 weeks.
	ELSE 
	    lcWhereString := ' AND (EMP.OUT_DT IS NULL OR EMP.OUT_DT > pdCurrDate) ';

    END IF;

    -- The user wants Unionized Shop employees.
    IF pnTypeNo = 1 THEN
        lcWhereString := lcWhereString || ' AND EMP.HRS_BASE = 1 AND EMP.UNION_EMP = 1 ';
		
    -- The user wants NON-Unionized Shop employees.
    ELSIF pnTypeNo = 2 THEN
        lcWhereString := lcWhereString || ' AND EMP.HRS_BASE = 1 AND EMP.UNION_EMP = 0 ';

    -- The user wants Office employees.
    ELSE
        lcWhereString := lcWhereString || ' AND EMP.HRS_BASE = 0 ';

    END IF;
 
   open pCursor for
    'SELECT RTRIM(PERS.LAST_NAME) ||' || ''', ''' || '|| RTRIM(PERS.FIRST_NAME) AS EMP_NAME,' ||
	'  PERS.PERS_NO ' ||
	'FROM EMP,' ||
	'  PERS ' ||
	'WHERE EMP.PERS_NO = PERS.PERS_NO ' || lcWhereString ||
	'ORDER BY 1' ;
 
 End;
END;
/

Mike