Oracle Stored Procedure

[v.maggs]

Folks,
I have tried every way I can think of to get this to work, without
success. We have a COTS application and I am tyring to execute a stored
procedure, Enter_Result. I can connect fine, etc. I can run the SP fine
from the sqlplus prompt and I am certain that nothing amiss there. My
issue is getting it to run via ado.net. I have read numerous articles
and postings all around the www. Most of the examples I have found
involve cursors in the SP but as you can see, this proc does not use a
cursor. So, how does one use an SP having no cursor via ado.net (2.0,
Visual Basic 2005)?

PROCEDURE Enter_Result(Rslt_Id IN NUMBER, New_Value IN VARCHAR2, Stat
IN OUT NUMBER, Rslt_Origin IN VARCHAR2 DEFAULT 'A', SigFigResult IN OUT
VARCHAR2, Result_Inlimit IN OUT VARCHAR2) AS

BEGIN
Stat := Naip_SQLLIMS_Records.SUCCESS;
Naip_Results.Enter_Result(Rslt_Id, New_Value, Stat, Rslt_Origin,
SigFigResult, Result_Inlimit);
EXCEPTION
WHEN OTHERS THEN
Stat := Naip_SQLLIMS_Records.FAILURE;
Naip_Pen_Error.Save_Error('ENTER_RESULT', 'SQLCODE');
END Enter_Result;

Here is the code as it exists at the moment. I am getting an "Fill:
SelectCommand.Connection property has not been initialized." error on
the MyDA.Fill(Ds) statement:

Using cnLims
cnLims.ConnectionString = GetLimsConnectString()
cnLims.Open()
Dim command As OracleCommand = New OracleCommand()
command.CommandText = "Results_API.ENTER_RESULT"
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New OracleParameter("Rslt_Id",
OracleType.Number)).Value = CLng(RsltID)
command.Parameters.Add(New OracleParameter("New_Value",
OracleType.VarChar)).Value = New_Value
command.Parameters.Add(New OracleParameter("Stat",
OracleType.Number)).Value = -1
command.Parameters.Add(New OracleParameter("Rslt_Origin",
OracleType.VarChar)).Value = "A"
command.Parameters.Add(New OracleParameter("SigFigResult",
OracleType.VarChar)).Value = ""
command.Parameters.Add(New
OracleParameter("Result_Inlimit", OracleType.VarChar)).Value = ""

Dim MyDA As New OracleDataAdapter(command)

Try
MyDA.Fill(Ds)
Catch x
MessageBox.Show(x.Message.ToString)
End Try

End Using

 

[David Browne]

Folks,
I have tried every way I can think of to get this to work, without
success. We have a COTS application and I am tyring to execute a stored
procedure, Enter_Result. I can connect fine, etc. I can run the SP fine
from the sqlplus prompt and I am certain that nothing amiss there. My
issue is getting it to run via ado.net. I have read numerous articles
and postings all around the www. Most of the examples I have found
involve cursors in the SP but as you can see, this proc does not use a
cursor. So, how does one use an SP having no cursor via ado.net (2.0,
Visual Basic 2005)?

PROCEDURE Enter_Result(Rslt_Id IN NUMBER, New_Value IN VARCHAR2, Stat
IN OUT NUMBER, Rslt_Origin IN VARCHAR2 DEFAULT 'A', SigFigResult IN OUT
VARCHAR2, Result_Inlimit IN OUT VARCHAR2) AS

BEGIN
Stat := Naip_SQLLIMS_Records.SUCCESS;
Naip_Results.Enter_Result(Rslt_Id, New_Value, Stat, Rslt_Origin,
SigFigResult, Result_Inlimit);
EXCEPTION
WHEN OTHERS THEN
Stat := Naip_SQLLIMS_Records.FAILURE;
Naip_Pen_Error.Save_Error('ENTER_RESULT', 'SQLCODE');
END Enter_Result;

Here is the code as it exists at the moment. I am getting an "Fill:
SelectCommand.Connection property has not been initialized." error on
the MyDA.Fill(Ds) statement:

Using cnLims
cnLims.ConnectionString = GetLimsConnectString()
cnLims.Open()
Dim command As OracleCommand = New OracleCommand()
command.CommandText = "Results_API.ENTER_RESULT"
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New OracleParameter("Rslt_Id",
OracleType.Number)).Value = CLng(RsltID)
command.Parameters.Add(New OracleParameter("New_Value",
OracleType.VarChar)).Value = New_Value
command.Parameters.Add(New OracleParameter("Stat",
OracleType.Number)).Value = -1
command.Parameters.Add(New OracleParameter("Rslt_Origin",
OracleType.VarChar)).Value = "A"
command.Parameters.Add(New OracleParameter("SigFigResult",
OracleType.VarChar)).Value = ""
command.Parameters.Add(New
OracleParameter("Result_Inlimit", OracleType.VarChar)).Value = ""
. . .

You need to set the parameter directions appropriately, use ExeucteNonQuery
and then investigate the resulting parameter values. This procedure does
not return a resultset, so you shouldn't be using a DataAdapter.

David

 

[v.maggs]

You need to set the parameter directions appropriately, use ExeucteNonQuery
and then investigate the resulting parameter values. This procedure does
not return a resultset, so you shouldn't be using a DataAdapter.

David

........
David,
I added the ParameterDirection statements. I used either Input or
InputOutput, according to the SP, and added the ExecuteNonQuery
statement. I am not sure about the applicability of the the
..ReturnValue direction. Now I am getting error ORA-06550,
RESULTS_API.ENTER_RESULT must be declared. I have verified that this is
the correct name. Any suggestions?

Using cnLims
cnLims.ConnectionString = GetLimsConnectString()
cnLims.Open()
Dim command As OracleCommand = cnLims.CreateCommand()
command.CommandText = "Results_API.ENTER_RESULT"
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New OracleParameter("Rslt_Id",
OracleType.Number)).Value = CLng(RsltID)
command.Parameters("Rslt_ID").Direction =
ParameterDirection.Input
command.Parameters.Add(New OracleParameter("New_Value",
OracleType.VarChar)).Value = New_Value
command.Parameters("New_Value").Direction =
ParameterDirection.Input
command.Parameters.Add(New OracleParameter("Stat",
OracleType.Number)).Value = -1
command.Parameters("Stat").Direction =
ParameterDirection.InputOutput
command.Parameters.Add(New OracleParameter("Rslt_Origin",
OracleType.VarChar)).Value = "A"
command.Parameters("Rslt_Origin").Direction =
ParameterDirection.Input
command.Parameters.Add(New OracleParameter("SigFigResult",
OracleType.VarChar)).Value = ""
command.Parameters("SigFigResult").Direction =
ParameterDirection.InputOutput
command.Parameters.Add(New
OracleParameter("Result_Inlimit", OracleType.VarChar)).Value = ""
command.Parameters("Result_Inlimit").Direction =
ParameterDirection.InputOutput

command.ExecuteNonQuery()

 

[David Browne]

.......
David,
I added the ParameterDirection statements. I used either Input or
InputOutput, according to the SP, and added the ExecuteNonQuery
statement. I am not sure about the applicability of the the
.ReturnValue direction. Now I am getting error ORA-06550,
RESULTS_API.ENTER_RESULT must be declared. I have verified that this is
the correct name. Any suggestions?

Well, when in doubt, you can issue the exact same query from SQL Plus. Just
use CommandType.Text instead of CommandType.StoredProcedure and set the
CommandText to
(this is C# which allows multi-line literal strings. in VB you should to use
one long line instead.
command.CommandText = @"
BEGIN
Stat := Naip_SQLLIMS_Records.SUCCESS;
Naip_Results.Enter_ResultRslt_Id, :New_Value, :Stat, :Rslt_Origin,
:SigFigResult, :Result_Inlimit);
EXCEPTION
WHEN OTHERS THEN
Stat := Naip_SQLLIMS_Records.FAILURE;
Naip_Pen_Error.Save_Error('ENTER_RESULT', 'SQLCODE');
END Enter_Result;
";

Note the bind variables. Then bind the parameters as before with the
direction and execute.

If you still have trouble, start with a parameterless block

@"
DECLARE
Rslt_Id number := 1;
New_Value number := 3;
Stat varchar2(50) := 'whatever';
.. . . et cetera
BEGIN
Stat := Naip_SQLLIMS_Records.SUCCESS;
Naip_Results.Enter_Result(Rslt_Id, New_Value, Stat, Rslt_Origin,
SigFigResult, Result_Inlimit);
EXCEPTION
WHEN OTHERS THEN
Stat := Naip_SQLLIMS_Records.FAILURE;
Naip_Pen_Error.Save_Error('ENTER_RESULT', 'SQLCODE');
END Enter_Result;
";
and bind out the parameters one-by one.

David