Alex Otto Alex Otto - 4 months ago 14
Vb.net Question

How to call a Function with a RETURN NUMBER

Forgive my lack of knowledge . I'm trying to call an insert function from

sqlnavigator
. I'm able to capture the
prm1.Value
, but I'm not sure how I'm supposed to include
RETURN NUMBER
(see SQL below) from my package spec in sqlnavigator. When I run it, nothing is added to ORACLE.

Public Shared Function INSERT_CATEGORY()

Dim cmdOraCommand As New OracleCommand

Try
cmdOraCommand.CommandType = CommandType.StoredProcedure
cmdOraCommand.CommandText = "SF_ALEX_TEST.insertCategory"

Dim prm1 As OracleParameter = cmdOraCommand.Parameters.Add( _
"inCategory", OracleType.VarChar)
prm1.Direction = ParameterDirection.Input
prm1.Value = strCategory

Catch ex As Exception
MsgBox(ex.Message)

Finally
cmdOraCommand.Dispose()
End Try

End Function


SQL/ORACLE

FUNCTION insertCategory(inCategory IN VARCHAR2) RETURN NUMBER

IS
vReturnedValue NUMBER;
vID NUMBER;
vExist NUMBER;
BEGIN
IF inCategory IS NULL THEN

vReturnedValue := 0;
RETURN vReturnedValue;
ELSE

BEGIN
SELECT COUNT(MNT_CATEGORY) INTO vExist
FROM MNT_CATEGORIES
WHERE MNT_CATEGORIES.MNT_CATEGORY = upper(inCategory);
END;

IF vExist = 0 THEN
BEGIN
SELECT SEQ_MNT_LOG.NEXTVAL INTO vID FROM DUAL;

INSERT INTO MNT_CATEGORIES(MNT_CATEGORY_ID, MNT_CATEGORY)
VALUES (vID, UPPER(inCategory));
COMMIT;

vReturnedValue := vID;
RETURN vReturnedValue;

EXCEPTION
WHEN OTHERS THEN
vReturnedValue := SQLCODE;
RETURN vReturnedValue;
END;

ELSE

vReturnedValue := vExist * -1;
RETURN vReturnedValue;
END IF;
END IF;
END;

Answer

You're creating the command but not opening a connection or executing the command.
You're also creating a parameter but not adding it to the command.
You will also need another parameter with ParameterDirection.Return if you want to capture the return value. You cannot check the value of that parameter until the reader is closed.

Here are the docs.