Alex Rivera | Logout

C# Oracle Stored Procedure Parameter Order

Asked 2009-06-06T08:20:11.863
14

With this

PROCEDURE "ADD_BOOKMARK_GROUP" (
  "NAME" IN VARCHAR2, 
  "BOOKMARK_GROUP_ID" IN NUMBER, 
  "STAFF_ID" IN VARCHAR2,
  "MAX_NO" IN INT,
  "NUMFOUND" OUT INT, 
  "NEW_ID" OUT NUMBER) IS

BEGIN

NEW_ID := -1;

SELECT COUNT(*) INTO NUMFOUND FROM BOOKMARK_GROUP_TABLE WHERE STAFF_ID = STAFF_ID;

IF NUMFOUND < MAX_NO THEN
    INSERT INTO BOOKMARK_GROUP_TABLE (NAME, BOOKMARK_GROUP_ID, STAFF_ID) VALUES(NAME, BOOKMARK_GROUP_ID, STAFF_ID);
    SELECT BGT_SEQUENCE.currval INTO NEW_ID FROM dual;
END IF;
END;

I find it interesting that if I don't add parameters in the order they were defined in, e.g.

OracleCommand cmd = new OracleCommand("ADD_BOOKMARK_GROUP", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("NAME", name));
...
cmd.Parameters.Add(new OracleParameter("NEW_ID", OracleDbType.Decimal)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("NUMFOUND", OracleDbType.Int32)).Direction = ParameterDirection.Output;

instead of

OracleCommand cmd = new OracleCommand("ADD_BOOKMARK_GROUP", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("NAME", name));
...
cmd.Parameters.Add(new OracleParameter("NUMFOUND", OracleDbType.Int32)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("NEW_ID", OracleDbType.Decimal)).Direction = ParameterDirection.Output;

The values returned by

cmd.Parameters["NEW_ID"].Value.ToString()

and

cmd.Parameters["NUMFOUND"].Value.ToString()

get swapped, although running the procedure through the VS2008 Server Explorer returns correct data.

Why is this?

Edit
Report

1 Answer

1

Not an answer to the question but you can use 'insert ... returning ... into ' in stead of select bgt_sequence.currval from dual, for example:

begin
  insert into test (id)
  values(test_seq.nextval)
  returning id into p_id;
end;

See http://www.adp-gmbh.ch/ora/sql/insert_into_x_returning_y.html

answered 2009-06-06T15:39:01.143

Your Answer