ORACLE - NEED HELP , PLS !

[lirlir]

hello

Is there a possibility to call an ORACLE function (not SP)
from VB ???

tnx

LirLir

[lirlir]

i can't belive this, No 1 knows ?

[HughLacey]

You need to select it from DUAL.

For eample if you define the following ORACLE Function:

CREATE OR REPLACE FUNCTION SQUARE
(
x IN Number
)
RETURN Number

IS
BEGIN
return (x * x);
END;

Then in vb you can use the following SQL:

SELECT Square(16) FROM DUAL

[lirlir]

that i know the problem is that inside the function
i have 1 select than 1 update and another select than
return value...

so when i m calling the Oracle function from VB i get
error because i cant put UPDATE in Select...

[HughLacey]

If you post a more detailed example of what you are trying to do I may be able to help (some punctuation might be good too).

A very good book on Oracle /VB is

Dov Trietsch's "Visual Basic Oracle 8 Programmer's Reference" published by Wrox

[lirlir]

Ok, tnx

this is my Oracle Function:
----------------------------------------------
CREATE OR REPLACE FUNCTION F_PRODUCT_MOBILE_PREPAID
(
N_ENTITY_ID_OWNER IN NUMBER,
N_SOLD_TO_ENTITY_ID IN NUMBER,
N_AMOUNT IN NUMBER,
V_PHONE_NUMBER IN VARCHAR
)

RETURN VARCHAR
IS


N_SALE_NUMBER NUMBER(5);
V_RETURN_VALUE CHAR(100);

BEGIN

--SELECT NEW SALE_NUMBER
-------------------------------------------------
SELECT SQ_SALE_NUMBER.NEXTVAL
INTO N_SALE_NUMBER
FROM DUAL;

UPDATE PRODUCT_MOBILE_PREPAID
SET SALE_NUMBER = N_SALE_NUMBER
WHERE ENTITY_ID_OWNER = N_ENTITY_ID_OWNER
AND AMOUNT = N_AMOUNT
AND SALE_NUMBER IS NULL
AND ROWNUM<2;

SELECT 'Visible code = ' || ' ' || VISIBLE_CODE || ' Secret code = ' || ' ' || SECRET_CODE
INTO V_RETURN_VALUE
FROM PRODUCT_MOBILE_PREPAID
WHERE SALE_NUMBER = N_SALE_NUMBER;

RETURN V_RETURN_VALUE;
END;
---------------------------------------------------------
i m trying to call it from VB like this :

Private Sub cmdSend_Click()

Dim p As New ADODB.Parameter
Dim c As New ADODB.Command
Dim r As New ADODB.Recordset

Dim sql As String
Dim nsn As Long

Set c.ActiveConnection = ddnDataAccess.ddnConnection
c.CommandText = "select F_PRODUCT_MOBILE_PREPAID(1,5,110,'5484555') from emp where rownum<2"
.
.
.
.

End Sub

The thing is as i wrote before, that this aint working...


It will be great if u could help me .

tnx again,
LirLir

[HughLacey]

Without your database etc this is obviously untested, but try something like this...

Private Function CallMyFunc() as Integer

Dim c = New ADODB.Command
Dim mSQL as String

mSQL = "{? = call F_PRODUCT_MOBILE_PREPAID(?,?,?,?)}

With c
.Commandtext = mSQL
.CommandType = adCmdText
.ActiveConnection = ddnDataAccess.ddnConnection
.Parameters.Append .CreateParameter("retval", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("N_ENTITY_ID_OWNER", adInteger, adParamInput, 1, 1)
.Parameters.Append .CreateParameter("N_SOLD_TO_ENTITY_ID", adInteger, adParamInput, 1, 5)
.Parameters.Append .CreateParameter("N_AMOUNT", adInteger, adParamInput, 1, 110)
.Parameters.Append .CreateParameter("V_PHONE_NUMBER", adVarChar, adParamInput, 10, "5484555")
.Execute
end with

CallMyFunc = c(0).Value ' or c("retval").Value
Set c = Nothing

End Function

[lirlir]

thank u very much...

very very helpfull