Thank you! I'll look into these options.
"Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message
news:3225227.1060616811_at_dbforums.com...
>
> 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
Received on Tue Aug 12 2003 - 08:51:55 CDT
