Re: Error

Originally posted by Scott Hamlin
> 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

You cannot pass a VARCHAR2 variable in
where testnumber in (firstVal); like this.

Correct syntax is e.g.
where testnumber in (1,2,3,4); thus you cannot replace 1,2,3,4 with a VARCHAR2 variable which would be equal to '1,2,3,4' resulting in ORA-01722 error.

Passing a single value works because Oracle automatically does a type conversion so e.g if firstVal := '1'; then testnumber in (firstVal); = testnumber in ('1'); = testnumber in (1); and all three are correct syntactically.

However, if you want to pass delimited string to IN clause like this, then there are few choices.
1) Use dynamic sql.
2) Write a user defined function to parse the firstVal parameter and

   return 1 or 0 if the value exists. Use the function call in the    where clause.
3) OR use Oracle INSTR function to parse the firstVal parameter e.g.

where INSTR(firstVal, testnumber, 1, 1) != 0;

There may be performance hit with all these options.

Regards
/Rauf Sarwar

--
Posted via http://dbforums.com