Error

From: Scott Hamlin <not.me_at_yahoo.com>
Date: Mon, 11 Aug 2003 10:22:37 -0400
Message-ID: <bh88rf$v9fs2$1@ID-147295.news.uni-berlin.de>

I am having a problem when trying to send a PL/SQL procedure inside a package. The value is a varchar2 and can be a single numeric value or a comma delimited value. This value is then stuck into a SQL IN clause, which is used to fill a cursor. If I only send it one value, it works fine. However, it I send it multiple values, then I receive the following error messages:

ORA-01722: invalid number ORA-02063: preceding line from DEVL

Here is the procedure in question. It seems simple enough, but perhaps a different set of eyes can spot something that I am overlooking:

Procedure testSP
(
  pID in number
, firstVal in varchar2
, pUser in varchar2
, pSQLCODE out number
, pSQLERR out varchar2

) is

 cursor test_info is
   select testnumber,partynumber,varnumber1,ptyname,connection1    from systems.parties_vw_at_devl.world
   where testnumber in (firstVal);

 vVarnumber1 number;
 vPeopleID number;
 vErrorCode number;
 vErrorMsg varchar(100);
 vError exception;
 vWhoId number;
 test_row test_info%rowtype;

begin
 if testConnect then

• Loop through rows in the cursor, adding a row in People for each for test_row in test_info loop select id into vWhoId from who where test_row.connection1 = who.name;
  • Copy the varnumber fields into vVarnumber variables so that zeroes
  • are not inserted into the Address.V_Number field

        if test_row.varnumber1 = 0 then
          vVarnumber1 := null;
        else
          vVarnumber1 := test_row.varnumber1;
        end if;
        -- Insert data into People and Address table
        -- The Company value defaults to No
        AddPeople(pID,test_row.ptyname,'N',vWhoId,
        vVarnumber1,pUser,vPeopleID,vErrorCode,vErrorMsg);
        if vErrorCode > 0 then
          pSQLCODE := vErrorCode;
          pSQLERR := vErrorMsg;
          raise vError;
        end if;
      end loop;

 end if;

  return;
exception
  when vError then
    return;

  when others then
    pSQLCODE := SQLCODE;
    pSQLERR := SQLERRM;
end testSP;

Thank you very much for you help,
Scott Received on Mon Aug 11 2003 - 09:22:37 CDT