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

From: Ak <a_sood_at_rediffmail.com>
Date: 8 Jun 2002 19:04:20 -0700
Message-ID: <10cb5393.0206081804.4d7093e7@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 Sat Jun 08 2002 - 21:04:20 CDT