Oracle Stored Function Accessing from VB

From: <rkamirapu_at_my-deja.com>
Date: Wed, 08 Nov 2000 12:26:50 GMT
Message-ID: <8ubgq5$drn$1@nnrp1.deja.com>

I have been using oracle stored procedures (oracle v7.3) for quite some time and I call them from VB (version 5.0) applications using RDO objects.

A peculiar problem has struck me and I am unable to solve it.

A simple function (code given below) which has been working for some time has suddely started giving the error

'S1105 [Microsoft][ODBC drver for Oracle]Invalid parameter type'.

While there are certain other functions designed in the similar fashion are working. I am sure that the function is not modified. For my satisfaction, I have recompiled it but of no use. The said VB executable application is being called from a local network from different clients which is working since long time. Now all clients are reporting the same problem in accessing this function.

I have created another function in oracle by just changing the name of the function to ‘GETPROJCODE’ and keeping the remaining contents as it is, and tested. It is working from all my VB applications. But why it is not working when the function name is ‘GETPROJECTCODE’ ?

Any help or sugesstion is appreciated.

Please send a mail to ark_at_iict.ap.nic.in

Thanks in advance

Oracle Function Code is as follows

CREATE OR REPLACE FUNCTION GETPROJECTCODE(

	PROJECTCODE IN RMA_MASTER.PROJECT_CODE%TYPE)
	RETURN NUMBER IS

P1 RMA_MASTER.PROJECT_CODE%TYPE;
N number:=0;
CURSOR RMA IS SELECT PROJECT_CODE FROM RMA_MASTER WHERE PROJECT_CODE=PROJECTCODE;
BEGIN

	OPEN RMA;
	LOOP
		FETCH RMA INTO P1;
		EXIT WHEN RMA%NOTFOUND;
		IF P1=PROJECTCODE THEN
			N:=N+1;
		END IF;
	END LOOP;
	CLOSE RMA;
	RETURN N;

END GETPROJECTCODE; VB Code for calling the function is as follows (CN is rdo connection object which is initialised for global use in the program)

Public Function GetProjectCode(ProjCode As String) As Boolean

    Dim S As String

    S = "{ ? = call getprojectcode( ? )}"     Set Qy = CN.CreateQuery("", S)

    Qy(0).Direction = rdParamReturnValue
    Qy(1).Direction = rdParamInput
    Qy(0).Type = rdTypeNUMERIC
    Qy(1).Type = rdTypeVARCHAR
    Qy(1).Value = ProjCode

    Qy.Execute
    While Qy.StillExecuting: Wend
    GetProjectCode = IIf(Qy(0) > 0, True, False) End Function

--
rkamirapu


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Nov 08 2000 - 06:26:50 CST