Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Error
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
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;
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