Is there a problem get an Oracle 8i Stored Procedure to Return a value to VB.NET, or is it just me?
Burt Wilkins
I am having trouble getting a Oracle 8i Stored Procedure return value to
come back in VB.NET. Is there a known problem getting an Oracle Stored
Procedure to return a value, or have I simply goofed as usual. (Please
don't advise me to use Microsoft SQL. I am just the employee. Oracle was
not my idea.) Here is the Oracle Stored Procedure:
PROCEDURE Device_Names_Insert
(my_codeid OUT device_names.dn_codeid%TYPE,
dn_abbreviation IN device_names.dn_abbreviation%TYPE,
dn_description IN device_names.dn_description%TYPE,
dn_inuse IN device_names.dn_inuse%TYPE,
dn_color IN device_names.dn_color%TYPE,
dn_messages IN device_names.dn_messages%TYPE,
dn_script IN device_names.dn_script%TYPE,
dn_transactions IN device_names.dn_transactions%TYPE,
dn_date IN varchar2)
IS
BEGIN
INSERT INTO DEVICE_NAMES
(DN_CODEID, DN_ABBREVIATION, DN_DESCRIPTION,
DN_INUSE, DN_COLOR, DN_MESSAGES,
DN_SCRIPT, DN_TRANSACTIONS,
DN_DATE)
VALUES (DN_CODE_ID.NextVal, dn_abbreviation, dn_description,
dn_inuse, dn_color, dn_messages, dn_script, dn_transactions,
TO_DATE(dn_date, 'MM/DD/YYYY HH:MI:SS AM'))
returning DN_CODEID into my_codeid;
COMMIT;
END;
The Oracle Stored Procedure seems to compile quite nicely in Oracle, but I'm
not getting any value back in my VB.NET application other than 0.
Now, the only thing that appears to be at all ambitious to me in this Stored
Procedure is that I am using the "NextVal" of the Sequence DN_CODE_ID to
initially fill the column value DN_CODEID. However, even if I amend my
procedure, create a numeric variable, and then "Select DN_CODE_ID.NextVal
into My_Variable from Dual", and attempt to return either My_Variable or
DN_CODEID, the only value I am getting back in the VB.NET application is "0"
.
The line of code that returns the variable from the Oralce stored procedure
to VB.NET is
"intReturn = CType(cmdCommand.Parameters("@my_codeid").Value, Integer)"
exactly constructed as Microsoft .Net Help advises.
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #1
pParameter.ParameterName = "@my_codeid"
pParameter.Direction = ParameterDirection.Output 'Output Parameter.
pParameter.OleDbType = OleDbType.Integer
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #1
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #2
pParameter.ParameterName = "@DN_ABBREVIATION"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.VarChar
pParameter.Size = 15
pParameter.Value = DN_ABBREVIATION
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #2
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #3
pParameter.ParameterName = "@DN_DESCRIPTION"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.VarChar
pParameter.Size = 45
pParameter.Value = DN_DESCRIPTION
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #3
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #4
pParameter.ParameterName = "@DN_INUSE"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.Integer
pParameter.Value = DN_INUSE
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #4
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #5
pParameter.ParameterName = "@DN_COLOR"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.VarChar
pParameter.Size = 40
pParameter.Value = DN_COLOR
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #5
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #6
pParameter.ParameterName = "@DN_MESSAGES"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.Integer
pParameter.Value = DN_MESSAGES
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #6
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #7
pParameter.ParameterName = "@DN_SCRIPT"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.VarChar
pParameter.Size = 100
pParameter.Value = DN_SCRIPT
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #7
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #8
pParameter.ParameterName = "@DN_TRANSACTIONS"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.Integer
pParameter.Value = DN_TRANSACTIONS
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #8
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #9
pParameter.ParameterName = "@DN_DATE"
pParameter.Direction = ParameterDirection.Input 'Input Parameter.
pParameter.OleDbType = OleDbType.VarChar
pParameter.Size = 22 'Dates when made to strings have the same size
pParameter.Value = DN_DATE.ToString
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #9
Connection(True) 'Open the database.
cmdCommand.Connection = mConnection 'Connect to Database.
cmdCommand.CommandType = CommandType.StoredProcedure 'It's A Stored
Procedure.
cmdCommand.CommandText = strStoredProcedure 'Command to execute.
cmdCommand.ExecuteNonQuery() 'Insert this Row.
Connection(False) 'Update this database.
mintRecordCount = mintRecordCount + 1
intReturn = CType(cmdCommand.Parameters("@my_codeid").Value, Integer)
'Return new key.
Could you offer some correction?
Burton G. Wilkins, Programmer.
Ice
When you execute the stored proc directly from Oracle, do you get results?
Now that I've asked the dumb question.
Did you define a Return parameter. That's what you should be accessing the
return value off of?
Ice
"Burt Wilkins" <burton_...@hotmail.com> wrote in message
news:eQ5iuYUoCHA.2220@TK2MSFTNGP09...
Burt Wilkins
Do I get results? Yes. I am attempting to Insert a new row, and the Insert
is working. What I am not getting back in the "Returning" is the sequence
"DN_CODE_ID.NextVal". All I get back is a zero, yet in the database the
"NextVal" is working.
Did I define a Return parameter? Yes. The first parameter I included in my
VB.NET code was return parameter which looks like this:
pParameter = New OleDb.OleDbParameter() 'Beginning of Parameter #1
pParameter.ParameterName = "@my_codeid"
pParameter.Direction = ParameterDirection.Output 'Output Parameter.
pParameter.OleDbType = OleDbType.Integer
cmdCommand.Parameters.Add(pParameter) 'Add Parameter #1
Another gentlemen wrote me and said my error was that I should get rid of
all the "@" used prior to Parameter Names for Oracle. I did what he
suggested, but it had no affect. For Oracle parameters should I be using
the "@" to proceed the Parameter Name.
The Oracle Stored Procedure begins in this way:
PROCEDURE Device_Names_Insert
(my_codeid OUT device_names.dn_codeid%TYPE,
So there you see the VB.NET output parameter being set up as first
parameter, being matched to the "OUT" parameter of the Oracle Stored
Procedure. Then at the bottom of the Stored Procedure after the INSERT has
been accomplished a "RETURNING" is used to return the field populated by the
sequence with this scrap of Oracle SQL code:
returning DN_CODEID into my_codeid;
COMMIT;
END;
The Stored Procedure then having returned the value, I then have the
following line of VB.NET code receive the value of the parameter back into
the program, which reads as follows:
intReturn = CType(cmdCommand.Parameters("@my_codeid").Value, Integer)
'Return new key.
So having covered all these basis, so how come it don't work. It still
fails to return a value whether or not the "@" is used.
Could you offer a suggestion, or better yet a solution?
Sincerely,
Burton G. Wilkins, Programmer.
"Ice" <eik...@manh.com> wrote in message
news:#hfEf7foCHA.2540@TK2MSFTNGP12...
Burt Wilkins
Earlier I wrote and said that getting rid of the "@" in parameters had no
affect. Well I kept playing with it, and suddenly it did work. So then
the lesson of this issue is that when working with Oracle Stored Procedures
don't use "@" in the parameters.
Sincerely,
Burton G. Wilkins.
"Burt Wilkins" <burton_...@hotmail.com> wrote in message
news:uemXZKhoCHA.2220@TK2MSFTNGP09...