PL/SQL issues returning a sequence scalar value

Answer 1

I've just compiled your package, in PL/SQL Developer it works fine.

The problem seems to be with the datatypes in your C# code.

From what I see in description, you don't bind any parameters. You should bind parameters somewhere in your code, like

OracleParameter bid = new OracleParameter("bookmarkID", OracleDbType.Number);
bid.Direction = ParameterDirection.Output;
command.Parameters.Add(bid);

If there are lots of abstractions you need to deal with, you may redefine you procedure as a function:

FUNCTION procGet_Bookmark_Id RETURN INTEGER
IS
  res INTEGER;
BEGIN
  SELECT seq_bookmarks.nextval
  INTO res
  FROM dual;
  RETURN res;
END procGet_Bookmark_Id;

and call it in a SELECT query:

SELECT bookmarks.procGet_Bookmark_id FROM dual;

, which you seem to be able to do.

Answer 2

The error: "Wrong number or types of arguments in call to blah" refers to errors in the calling code, not the called code.

You should check the following:

• The parameter you're passing is a NUMBER or a type that can be easily converted from a NUMBER.
• The parameter you're passing is a variable, as opposed to a literal or constant.

Answer 3

I believe you need to pass in the parameter in your .net code. The name you give the parameter needs to be identical in the .net code and the procedure definition.

-OR-

Use a PL/SQL function instead of a procedure. You won't need to use a parameter at all.

Edit: Assuming you are using the Microsoft Provider you will need a return parameter. Here is the .net code.

// proc is the procedure name, oraConn is the oracle connection
OracleCommand cmd = new OracleCommand(proc, oraConn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

OracleParameter ret = new OracleParameter();
ret.Direction = System.Data.ParameterDirection.ReturnValue;
ret.OracleType = OracleType.Number;
cmd.Parameters.Add(ret);