Re: ORA-06512, 01722, Stored procedure in 9i

Take a line number from ORA-06512 and correct 1722 there - 1722 stands for 'invalid number' and means that there was an attempt to perform an implicit datatype conversion from string to number, which failed due to bad format. Possible point of failure in your case is spGetClientProperties.vClient_Id which is probably VARCHAR2 and may contain badly formatted number.

Additionally, you do not use bind variables here - not using them is a MAJOR scalability inhibitor in Oracle. Instead of contatenating literal spGetClientProperties.vClient_Id several times you could write the query like this:

spGetClientProperties.sSQL := 'Select Distinct P.Property_id,OP.Property_Id,P.Address1,P.Address2, From Property P,

      op_Order_Property OP
Where P.Property_id=Op.Property_Id(+)

      and (OP.Buyer_Client_Id = :ClientId
             Or OP.Seller_Client_Id = :ClientId
             Or P.Entered_by = :ClientId
             Or OP.Ordered_By = :ClientId
             Or P.Buyer_Client_Id = :ClientId
             Or P.Seller_Client_Id = :ClientId)';

or better yet

spGetClientProperties.sSQL := 'Select Distinct P.Property_id,OP.Property_Id,P.Address1,P.Address2, From Property P,

      op_Order_Property OP
Where P.Property_id=Op.Property_Id(+)

      and :ClientId IN (OP.Buyer_Client_Id,
                               OP.Seller_Client_Id,
                               P.Entered_by,
                               OP.Ordered_By,
                               P.Buyer_Client_Id,
                               P.Seller_Client_Id)';

and then open the resulting cursor this way:

open RS for spGetClientProperties.sSQL
  USING IN TO_NUMBER(spGetClientProperties.vClient_Id); (that's in case Client_Id is indeed numeric, otherwise it'll be TO_CHAR to save Oracle from [unnecessary] implicit datatype conversions).

Using bind variable for client id will allow you to create truly SHARED SQL and reduce both hard parses and SGA usage. And it's now clearer and easier to follow and understand, isn't it? ;) This form also shrinks the window for error in case when vClient_Id is character and contains some invalid value, for example, a string with an ' in it - in this case concatenating it to the rest of the string will produce simply invalid SQL statement, while the bound version will correctly throw invalid number exception when you attempt to convert this string to number to bind it to the statement.
There is no need to convert P.Property_Closed = 1|0 to bind variable though, as there are only two possible values for it and having two cached versions of the same SQL with this value being either 1 or 0 will probably give better performance, especially if the data is skewed towards one value.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Ak" <a_sood_at_rediffmail.com> wrote in message
news:10cb5393.0206081804.4d7093e7_at_posting.google.com...


> I am in the process of migrating a database from 8.1.6 to 9i. A stored

> procedure  that works fine when connecting to the 8i database errors

> in the 9i environment.

>

> Package GenricPkg declares a Ref cusrsor.

> A stored procedure - spGetClientProperties, takes in 5 parameter from

> the calling VB application

>  1. builds a query (stores the query string in a varchar variable),

>  2. The query is finally opened as a cursor.

>

> In the 9i database the  stored procedure returns a ORA-01722 and

> ORA-06512 error when called by the VB application.  The code for the

> package and the stored procedure is as follows:

> I would greatly appreciate any hints towards what might be going

> wrong.

>

>

> Package GenericPkg

>  AS

> TYPE GenericCurTyp IS REF CURSOR;

> END GENERICPKG;

>

>

> Procedure spGetClientProperties is

> (

> vClient_Id Number,

> vFilter_Id Number,

> vFilRec VarChar2,

> vSortId Number,

> vActiveFlag Number,

> RS out genericpkg.genericcurtyp

> )As

> sSQL varchar2(1000);

> Begin

>

> --Build the query based on the parameters passed to the procedure.

>

> spGetClientProperties.sSQL  := 'Select Distinct

> P.Property_id,OP.Property_Id,P.Address1,P.Address2,'||

> 'From Property P,

>       op_Order_Property OP

> Where P.Property_id=Op.Property_Id(+)

>       and (OP.Buyer_Client_Id = '||spGetClientProperties.vClient_Id

>      ||' Or OP.Seller_Client_Id = '

> ||spGetClientProperties.vClient_Id||'

>          Or P.Entered_by = ' ||spGetClientProperties.vClient_Id ||'

>          Or OP.Ordered_By = ' || spGetClientProperties.vClient_Id || '

>          Or P.Buyer_Client_Id = ' ||

> spGetClientProperties.vClient_Id||'

>          Or P.Seller_Client_Id = '

> ||spGetClientProperties.vClient_Id||')';

>

> If spGetClientProperties.vActiveFlag = 0 Then

> spGetClientProperties.sSQL := spGetClientProperties.sSQL ||' And

> P.Property_Closed = 1' ;

>

> ElsIf spGetClientProperties.vActiveFlag  = 1 Then

> spGetClientProperties.sSQL := spGetClientProperties.sSQL ||' And

> P.Property_Closed = 0';

>

> End If;

>

>

> If spGetClientProperties.vFilter_Id  = 1 Then

> spGetClientProperties.sSQL := spGetClientProperties.sSQL ||' And

> upper(P.City) Like upper('''|| spGetClientProperties.vFilRec ||'%'')';

> ElsIf spGetClientProperties.vFilter_Id  = 2 Then

> spGetClientProperties.sSQL := spGetClientProperties.sSQL ||' And

> upper(P.Address1) Like upper('''|| spGetClientProperties.vFilRec

> ||'%'')';

> End If;

>

>

> If spGetClientProperties.vSortID=1 Then

> spGetClientProperties.sSQL:= spGetClientProperties.sSQL || ' Order

> by P.Property_Id';

> Else

> spGetClientProperties.sSQL:= spGetClientProperties.sSQL || ' Order

> By P.Address1,P.Address2' ;

> End If;

>

>

> OPEN RS FOR spGetClientProperties.sSQL; --Call the package to open a

> cursor for

>                                          -- the query built in sSql

> variable

> End;