Cursor in PL/SQL function never returns data.

From: Nick Hancock <nhancock_at_charlestoncounty.org>
Date: Mon, 21 May 2001 10:59:42 -0400
Message-ID: <tgibaupvmg1g98@news.supernews.com>

I've learned quite a bit about writing PL/SQL over the past several months while learing Oracle, but I've finally run into a problem I just can't solve and our DBA is stumped as well.

The following function is inside a package called Conversion_PKG. Its full of stuff I'm using on a data conversion and will not be needed once the new system is in production. The NewValue column is Number(12) the others are VarChar2(30), except for the OldDescription column which is Varchar2(50).

/*===============================================*/
FUNCTION GetNewValueByDescription

   (OldSystem_In IN VARCHAR2,
    OldTableName_In IN VARCHAR2,
    OldFieldName_In IN VARCHAR2,
    OldDescription_In IN VARCHAR2,
    NewTableName_in IN VARCHAR2,
    NewFieldName_in IN VARCHAR2)
    RETURN NUMBER
AS
  TheNewValue NUMBER;
  CURSOR NewValCur
  IS
    SELECT NewValue
 FROM OLDNEWID
 WHERE
   OldSystem = OldSystem_in AND
   OldTableName = OldTableName_In AND
   OldFieldName = OldFieldName_In AND
   OldDescription = OldDescription_In AND    NewTableName = NewTableName_in AND
   NewFieldName = NewFieldName_in;
BEGIN
  TheNewValue := -1;
  IF NOT NewValCur%IsOpen
  THEN
    OPEN NewValCur;
  END IF;
  IF NewValCur%RowCount > 0
  THEN
    FETCH NewValCur INTO TheNewValue;
  END IF;
  CLOSE NewValCur;
  RETURN TheNewValue;
END GetNewValueByDescription;
/*===============================================*/

My problem is that this returns a -1 no matter what I feed into it. The following select statement returns one row as it should:

SELECT NewValue
 FROM OLDNEWID
 WHERE
   OldSystem = 'SAMS' AND
   OldTableName = 'SOL' AND
   OldFieldName = 'SolicitorInitials' AND    OldDescription = 'CS' AND
   NewTableName = 'SOLINFO' AND
   NewFieldName = 'SolicitorInfoID';

The following anonymous block returns -1:

DECLARE
  TheNewID NUMBER;
BEGIN
  TheNewID := Conversion_Pkg.GetNewValueByDescription

       ('SAMS', 'SOL', 'SolicitorInitials',
      'CS', 'SOLINFO', 'SolicitorInfoID');

  DBMS_OUTPUT.PUT_LINE(TheNewID);
END; The package and the tables are all in the same schema with the same owner. I have tried prefixing the tablename in the cursor with the schema name, but it doesn't work. I have had this problem in other instances as well, but this time I have to solve it. The alternative methods I have will slow the conversion down by a factor of ten or more.

--
Nick Hancock
SCT Corporation
nickhancock_at_charlestoncounty.org

Received on Mon May 21 2001 - 09:59:42 CDT