OraFAQ Forum: Weblogic & Application Server » call function oracle from vb

Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » call function oracle from vb

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

call function oracle from vb [message #124092]

Thu, 16 June 2005 07:09

patrykp
Messages: 31
Registered: April 2005

Member

Hi,
Please can tell me, How do I a call an Oracle Function from a Visual Basic program using oraOLEDB.Oracle Provider.

FUNCTION test(
User_Id 	IN NUMBER  DEFAULT NULL,
name 	IN VARCHAR2  DEFAULT NULL,
RC1 	IN OUT Omwb_emulation.globalPkg.RCT1)
RETURN INTEGER
AS
User_Id_ 	NUMBER(10,0) := User_Id;
name_ 	VARCHAR2(64) := name;
StoO_selcnt	INTEGER;
StoO_error 	INTEGER;
StoO_rowcnt	INTEGER;
StoO_crowcnt	INTEGER := 0;
StoO_fetchstatus	INTEGER := 0;
StoO_errmsg	VARCHAR2(255);
StoO_sqlstatus	INTEGER;
CurPrivs 	NUMBER(10,0);
obj_id 	NUMBER(10,0);
tempVar1 	NUMBER :=1;
BEGIN
...
	END test;

I tried

Public Function finddocbyname(ByRef rst As ADODB.Recordset, ByVal Name) As Long
    Dim par As ADODB.Parameter
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = db
    cmd.CommandText = "sa.find_package.test "
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("User_Id", adInteger, adParamInput, 4, 5)
    'cmd.Parameters.Append par
    cmd.Parameters.Append cmd.CreateParameter("Name", adVarChar, adParamInput, 64, Name)
    'cmd.Parameters.Append par
   'cmd.CommandText = "{call sa.find_package.spcxdb_finddocbyname (?, ?)}"
    Set rst = cmd.Execute
End Function

but
I tried the code, but I get error
PLS-00221:test:StoredFunctionNAme is not a procedure or is
undefined.

Please help me.

Report message to a moderator

Re: call function oracle from vb [message #124928 is a reply to message #124092]

Wed, 22 June 2005 06:31

Maaher
Messages: 7065
Registered: December 2001

Senior Member

How do you handle the return from the function. Oracle's right: it's no procedure.

MHE

Report message to a moderator

Re: call function oracle from vb [message #125537 is a reply to message #124928]

Mon, 27 June 2005 06:00

patrykp
Messages: 31
Registered: April 2005

Member

Documentation Oracle Provider for OLE DB
Oracle Provider for OLE DB allows consumers to execute a PL/SQL stored procedure with an argument of REF CURSOR type or a stored function returning a REF CURSOR.
OraOLEDB returns a rowset for the REF CURSOR bind variable. Because there is no predefined datatype for REF CURSOR in the OLE DB specification, the consumer must not bind this parameter.
If the PL/SQL stored procedure has one or more arguments of REF CURSOR type, OraOLEDB binds these arguments appropriately and returns a rowset for each argument of REF CURSOR type.
If the PL/SQL stored function returns a REF CURSOR or has an argument of REFCURSOR type, OraOLEDB binds these appropriately and returns a rowset for each REF CURSOR type.
To use this feature, stored procedures or functions must be called in the ODBC procedure call escape sequence.
The stored procedure or function being called could be either standalone or packaged. However, the REF CURSOR being returned must be explicitly defined in a package in the database.

There is example code for a FUNCTION.
CREATE OR REPLACE PACKAGE SA.CALLF AS
TYPE empcur IS REF CURSOR;
FUNCTION test(
ec out empcur,
User_ID IN NUMBER DEFAULT NULL,
Parent_ID IN NUMBER DEFAULT NULL)
RETURN integer;
END callf;

CREATE OR REPLACE PACKAGE BODY CALLF AS
FUNCTION SPCXDB_ENUMDOCUMENTS(
ec out empcur,
User_ID IN NUMBER DEFAULT NULL,
Parent_ID IN NUMBER DEFAULT NULL)
RETURN integer
IS
User_ID_ NUMBER(10,0) := User_ID;
Parent_ID_ NUMBER(10,0) := Parent_ID;
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
CurPrivs NUMBER(10,0);
tempVar1 NUMBER :=1;
BEGIN
BEGIN
test.CurPrivs:=sa.verifyuserprivs(test.User_ID_, test.Parent_ID_, tempVar1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
IF test.CurPrivs = 0 THEN
RETURN -1;
END IF;
OPEN ec FOR
SELECT *
FROM VDoc
WHERE Parent_ID = test.Parent_ID_;
RETURN 0;
END test;
END callf;

var r refcursor
var a number
exec :a := sa.callf.test(:r, 5, 2);
print r

PARENT_ID OBJECT_ID USER_ID
2 28 5
2 15 5
2 26 5

How call function test from VB? I'm trying to call Oracle strored function from VB (Provider = "OraOLEDB.Oracle"), but I get error ORA-01008:not all variables bound.

Public Function GetProjDocuments(ByRef rst As ADODB.Recordset, ByVal IDProj) As Long
Dim par As ADODB.Parameter
Dim cmd As ADODB.Command
On Error Resume Next
Set cmd = New ADODB.Command
cmd.ActiveConnection = db
'If Err.Number = "3709" Then
' Err.Clear
' If reconnect <> -1 Then cmd.ActiveConnection = db
'End If
Set par = cmd.CreateParameter("User_ID", adSmallInt, adParamInput, 10, 5)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("Parent_ID", adSmallInt, adParamInput, 10, IDProj)
cmd.Parameters.Append par
cmd.Properties("PLSQLRSet") = True
cmd.CommandType = adCmdText
cmd.CommandText = "{?=CALL sa.callf.test(?, ?)}"
Set rst = cmd.Execute
cmd.Properties("PLSQLRSet") = False
If Err.Number = "-2147467259" Or Err.Number = "3709" Then
'MsgBox Err.Description
Err.Clear
' reconnect
GetProjDocuments = -1
'MsgBox "Utracono połaczenie z Bazą, skontaktuj się z administratorem"
Exit Function
End If
End Function

What is bad? Please sample code.
Best regards.

Report message to a moderator

Re: call function oracle from vb [message #676998 is a reply to message #124092]

Mon, 05 August 2019 21:05

Howd
Messages: 2
Registered: August 2019

Junior Member

I do this in VBScript, not VB.
But I have tried to adjust your code to show the correct way to get an Oracle function return value.
The important thing is that the return value is the first parameter.

Public Function finddocbyname(ByRef rst As ADODB.Recordset, ByVal Name) As Long
    Dim par As ADODB.Parameter
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = db
    cmd.CommandText = "sa.find_package.test "
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("Return_Int", adInteger, adParamReturnValue, 4,  -1)
    cmd.Parameters.Append cmd.CreateParameter("User_Id",    adInteger, adParamInput,       4,  5)
    cmd.Parameters.Append cmd.CreateParameter("Name",       adVarChar, adParamInput,       64, "Name")
    cmd.Parameters.Append cmd.CreateParameter("RC1",        adVarChar, adParamInputOutput, 64, "RC1_in")
    cmd.CommandText = "{call sa.find_package.spcxdb_finddocbyname}"
    Set rst = cmd.Execute
    ' cmd.Parameters("Return_Int") holds return value. cmd.Parameters("RC1") holds out value
End Function

Report message to a moderator

Re: call function oracle from vb [message #676999 is a reply to message #676998]

Mon, 05 August 2019 21:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Howd,

I applaud your attempt to contribute, but I'd like you to be aware that this thread is from 2005, so I seriously doubt that OP will find your contribution.

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator