This type of thing is going to kill scalability even on a small system - use
bind variables!
Look on asktom.oracle.com and do a seach to see some examples.
Jim
"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;
Received on Sun Jun 09 2002 - 09:55:49 CDT
