Oracle Stored Procedure

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: Oracle Stored Procedure by David

David
Thu Jul 20 13:04:31 CDT 2006


<v.maggs@comcast.net> wrote in message
news:1153418271.712556.156910@b28g2000cwb.googlegroups.com...
> 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


Top

Re: Oracle Stored Procedure by v

v
Thu Jul 20 14:42:46 CDT 2006

David Browne wrote:
> <v.maggs@comcast.net> wrote in message
> news:1153418271.712556.156910@b28g2000cwb.googlegroups.com...
> > 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


.......
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()

Top

Re: Oracle Stored Procedure by David

David
Thu Jul 20 15:34:12 CDT 2006


<v.maggs@comcast.net> wrote in message
news:1153424566.253079.61470@m73g2000cwd.googlegroups.com...
> David Browne wrote:
>> <v.maggs@comcast.net> wrote in message
>> news:1153418271.712556.156910@b28g2000cwb.googlegroups.com...
>> > 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
>
>
> .......
> 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_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;
";

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


Top