How to call a Function with a RETURN NUMBER

Alex Otto

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;
Scott Hannen

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How to call a number from another function into a function?

From Dev

How to limit the number of failed attempts to call a function?

From Dev

How to call index number of function array in javascript

From Dev

How to string phone number to call function Xamarin?

From Dev

How to call function overloaded by function which return partail function

From Dev

How to call a function with variable number of parameters from a similar function?

From Dev

Is it good to return a function call?

From Dev

Function to return table of a number

From Dev

Mips function return a number

From Dev

How call function which is return from another one?

From Dev

How to wrap or call a C function with void as return in Emscripten?

From Dev

How to get return value in a function with inside Ajax call - JQuery

From Dev

How to call C extern function and get return struct?

From Dev

Java debug mode: how to get return value of function call

From Dev

How to setup return_value for a function call with specific signature?

From Dev

C++ How to get the right return from dll function call

From Dev

How call function which is return from another one?

From Dev

How to force MATLAB to return all values in a nested function call?

From Dev

Python ctypes: How to call a function which should return an array of strings?

From Dev

How to call a function, passing the return value (possibly void) of a functor?

From Dev

How to get the file name and a line number of a function call?

From Dev

How to return uncertain number columns of a table from a postgresql function?

From Dev

How to return uncertain number columns of a table from a postgresql function?

From Dev

how to return a number with 2 decimal places all the time from a function

From Dev

Tell gcc that a function call will not return

From Dev

Return array to parent function call

From Dev

Function call with a union return type

From Dev

Arrow function with return in reduce call

From Dev

Set a value or call a function on return

Related Related

  1. 1

    How to call a number from another function into a function?

  2. 2

    How to limit the number of failed attempts to call a function?

  3. 3

    How to call index number of function array in javascript

  4. 4

    How to string phone number to call function Xamarin?

  5. 5

    How to call function overloaded by function which return partail function

  6. 6

    How to call a function with variable number of parameters from a similar function?

  7. 7

    Is it good to return a function call?

  8. 8

    Function to return table of a number

  9. 9

    Mips function return a number

  10. 10

    How call function which is return from another one?

  11. 11

    How to wrap or call a C function with void as return in Emscripten?

  12. 12

    How to get return value in a function with inside Ajax call - JQuery

  13. 13

    How to call C extern function and get return struct?

  14. 14

    Java debug mode: how to get return value of function call

  15. 15

    How to setup return_value for a function call with specific signature?

  16. 16

    C++ How to get the right return from dll function call

  17. 17

    How call function which is return from another one?

  18. 18

    How to force MATLAB to return all values in a nested function call?

  19. 19

    Python ctypes: How to call a function which should return an array of strings?

  20. 20

    How to call a function, passing the return value (possibly void) of a functor?

  21. 21

    How to get the file name and a line number of a function call?

  22. 22

    How to return uncertain number columns of a table from a postgresql function?

  23. 23

    How to return uncertain number columns of a table from a postgresql function?

  24. 24

    how to return a number with 2 decimal places all the time from a function

  25. 25

    Tell gcc that a function call will not return

  26. 26

    Return array to parent function call

  27. 27

    Function call with a union return type

  28. 28

    Arrow function with return in reduce call

  29. 29

    Set a value or call a function on return

HotTag

Archive