"dlevine54" <dlevin***@discussions.microsoft.com> wrote in message
news:E7606451-330E-49DF-A88F-CDF398D55514@microsoft.com...
>I am calling an oracle procedure in a package. It is defined as
>  function get_request_status(request_id     IN OUT number,
>               appl_shortname IN varchar2 default NULL,
>               program        IN varchar2 default NULL,
>               phase      OUT varchar2,
>               status     OUT varchar2,
>               dev_phase  OUT varchar2,
>               dev_status OUT varchar2,
>               message    OUT varchar2) return boolean;
>  pragma restrict_references (get_request_status, WNDS);
>
> My vb code is
>
>            cnn2.ConnectionString = "provider=MSDAORA; Data Source=" &
> gDatabase & ";user id=" & gUserId & "; password=" & gPassword
>            cnn2.Open()
>            cmd2 = New OleDbCommand
>            cmd2.CommandType = CommandType.StoredProcedure
>            cmd2.CommandText = "fnd_concurrent.get_request_status"
>            cmd2.Connection = cnn2
>
>            Dim pRetVal As OleDbParameter = cmd2.Parameters.Add("pRetVal",
> OleDbType.Boolean)
>            pRetVal.Direction = ParameterDirection.ReturnValue
>
>            Dim pRequestId As OleDbParameter =
> cmd2.Parameters.Add("requestId", OleDbType.Numeric)
>            pRequestId.Direction = ParameterDirection.InputOutput
>            pRequestId.Value = Rdr("request_id")
>
>            Dim pShortName As OleDbParameter =
> cmd2.Parameters.Add("application_short_name", OleDbType.VarChar)
>            pShortName.Direction = ParameterDirection.Input
>            pShortName.Value = Rdr("application_short_name")
>
>            Dim pPgm As OleDbParameter = cmd2.Parameters.Add("program",
> OleDbType.VarChar)
>            pPgm.Direction = ParameterDirection.Input
>            pPgm.Value = Rdr("program")
>
>            Dim pPhase As OleDbParameter = cmd2.Parameters.Add("phase",
> OleDbType.VarChar, 50)
>            pPhase.Direction = ParameterDirection.Output
>            pPhase.Value = RequestArray(Cnt).Phase
>
>            Dim pStatus As OleDbParameter = cmd2.Parameters.Add("status",
> OleDbType.VarChar, 50)
>            pStatus.Direction = ParameterDirection.Output
>            pStatus.Value = RequestArray(Cnt).Status
>
>            Dim pDevPhase As OleDbParameter =
> cmd2.Parameters.Add("dev_phase", OleDbType.VarChar, 50)
>            pDevPhase.Direction = ParameterDirection.Output
>            pDevPhase.Value = RequestArray(Cnt).DevPhase
>
>            Dim pDevStatus As OleDbParameter =
> cmd2.Parameters.Add("dev_status", OleDbType.VarChar, 50)
>            pDevStatus.Direction = ParameterDirection.Output
>            pDevStatus.Value = RequestArray(Cnt).DevStatus
>
>            Dim pMessage As OleDbParameter = cmd2.Parameters.Add("message",
> OleDbType.VarChar, 500)
>            pMessage.Direction = ParameterDirection.Output
>            pMessage.Value = RequestArray(Cnt).Message
>
>            Try
>                rc = cmd2.ExecuteNonQuery
>            Catch ex As Exception
>                MsgBox(Err.Description)
>            End Try
>
>
>            Cnt += 1
>            cmd2.Dispose()
>            cnn2.Close()
>            'lstJobs.Items.Add(RequestArray(Cnt).Name)
>            End
>        End While
>
> I get an error stating 'Expression is of wrong type' and I can't figure
> out
> why. Any help would be appreciated.
>
> Thanks in advance.

"Cowboy (Gregory A. Beamer)" wrote:

> Try returning a REF_CURSOR of values rather than multiple output paramters.
> Depending on whether you use OLEDB or OracleClient, you may have to declare
> this "parameter" (OUT REF_CURSOR) or not. Either way, you treat it as a
> DataSet when it comes out. That is the easiest way to work with Oracle.
>
> Hope this helps.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> http://gregorybeamer.spaces.live.com
>
> ********************************************
> Think outside the box!
> ********************************************
> "dlevine54" <dlevin***@discussions.microsoft.com> wrote in message
> news:E7606451-330E-49DF-A88F-CDF398D55514@microsoft.com...
> >I am calling an oracle procedure in a package. It is defined as
> >  function get_request_status(request_id     IN OUT number,
> >               appl_shortname IN varchar2 default NULL,
> >               program        IN varchar2 default NULL,
> >               phase      OUT varchar2,
> >               status     OUT varchar2,
> >               dev_phase  OUT varchar2,
> >               dev_status OUT varchar2,
> >               message    OUT varchar2) return boolean;
> >  pragma restrict_references (get_request_status, WNDS);
> >
> > My vb code is
> >
> >            cnn2.ConnectionString = "provider=MSDAORA; Data Source=" &
> > gDatabase & ";user id=" & gUserId & "; password=" & gPassword
> >            cnn2.Open()
> >            cmd2 = New OleDbCommand
> >            cmd2.CommandType = CommandType.StoredProcedure
> >            cmd2.CommandText = "fnd_concurrent.get_request_status"
> >            cmd2.Connection = cnn2
> >
> >            Dim pRetVal As OleDbParameter = cmd2.Parameters.Add("pRetVal",
> > OleDbType.Boolean)
> >            pRetVal.Direction = ParameterDirection.ReturnValue
> >
> >            Dim pRequestId As OleDbParameter =
> > cmd2.Parameters.Add("requestId", OleDbType.Numeric)
> >            pRequestId.Direction = ParameterDirection.InputOutput
> >            pRequestId.Value = Rdr("request_id")
> >
> >            Dim pShortName As OleDbParameter =
> > cmd2.Parameters.Add("application_short_name", OleDbType.VarChar)
> >            pShortName.Direction = ParameterDirection.Input
> >            pShortName.Value = Rdr("application_short_name")
> >
> >            Dim pPgm As OleDbParameter = cmd2.Parameters.Add("program",
> > OleDbType.VarChar)
> >            pPgm.Direction = ParameterDirection.Input
> >            pPgm.Value = Rdr("program")
> >
> >            Dim pPhase As OleDbParameter = cmd2.Parameters.Add("phase",
> > OleDbType.VarChar, 50)
> >            pPhase.Direction = ParameterDirection.Output
> >            pPhase.Value = RequestArray(Cnt).Phase
> >
> >            Dim pStatus As OleDbParameter = cmd2.Parameters.Add("status",
> > OleDbType.VarChar, 50)
> >            pStatus.Direction = ParameterDirection.Output
> >            pStatus.Value = RequestArray(Cnt).Status
> >
> >            Dim pDevPhase As OleDbParameter =
> > cmd2.Parameters.Add("dev_phase", OleDbType.VarChar, 50)
> >            pDevPhase.Direction = ParameterDirection.Output
> >            pDevPhase.Value = RequestArray(Cnt).DevPhase
> >
> >            Dim pDevStatus As OleDbParameter =
> > cmd2.Parameters.Add("dev_status", OleDbType.VarChar, 50)
> >            pDevStatus.Direction = ParameterDirection.Output
> >            pDevStatus.Value = RequestArray(Cnt).DevStatus
> >
> >            Dim pMessage As OleDbParameter = cmd2.Parameters.Add("message",
> > OleDbType.VarChar, 500)
> >            pMessage.Direction = ParameterDirection.Output
> >            pMessage.Value = RequestArray(Cnt).Message
> >
> >            Try
> >                rc = cmd2.ExecuteNonQuery
> >            Catch ex As Exception
> >                MsgBox(Err.Description)
> >            End Try
> >
> >
> >            Cnt += 1
> >            cmd2.Dispose()
> >            cnn2.Close()
> >            'lstJobs.Items.Add(RequestArray(Cnt).Name)
> >            End
> >        End While
> >
> > I get an error stating 'Expression is of wrong type' and I can't figure
> > out
> > why. Any help would be appreciated.
> >
> > Thanks in advance.
>

"Paul Clement" wrote:

> On Thu, 22 Feb 2007 12:18:36 -0800, dlevine54 <dlevin***@discussions.microsoft.com> wrote:
>
> ¤ I am calling an oracle procedure in a package. It is defined as
> ¤   function get_request_status(request_id     IN OUT number,
> ¤                       appl_shortname IN varchar2 default NULL,
> ¤                       program        IN varchar2 default NULL,
> ¤                       phase      OUT varchar2,
> ¤                       status     OUT varchar2,
> ¤                       dev_phase  OUT varchar2,
> ¤                       dev_status OUT varchar2,
> ¤                       message    OUT varchar2) return boolean;
> ¤   pragma restrict_references (get_request_status, WNDS);
> ¤
>
> The Oracle Boolean data type implementation is limited to the PL/SQL environment only and cannot be
> returned through the Oracle client. What you would have to do is create a wrapper Function or stored
> procedure which calls the function, and returns your output parameters and a 1 or 0 value if this
> required by the calling application.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>

"dlevine54" wrote:

> It's always something. Thanks, I'll give that a try.
>
> "Paul Clement" wrote:
>
> > On Thu, 22 Feb 2007 12:18:36 -0800, dlevine54 <dlevin***@discussions.microsoft.com> wrote:
> >
> > ¤ I am calling an oracle procedure in a package. It is defined as
> > ¤   function get_request_status(request_id     IN OUT number,
> > ¤                       appl_shortname IN varchar2 default NULL,
> > ¤                       program        IN varchar2 default NULL,
> > ¤                       phase      OUT varchar2,
> > ¤                       status     OUT varchar2,
> > ¤                       dev_phase  OUT varchar2,
> > ¤                       dev_status OUT varchar2,
> > ¤                       message    OUT varchar2) return boolean;
> > ¤   pragma restrict_references (get_request_status, WNDS);
> > ¤
> >
> > The Oracle Boolean data type implementation is limited to the PL/SQL environment only and cannot be
> > returned through the Oracle client. What you would have to do is create a wrapper Function or stored
> > procedure which calls the function, and returns your output parameters and a 1 or 0 value if this
> > required by the calling application.
> >
> >
> > Paul
> > ~~~~
> > Microsoft MVP (Visual Basic)
> >

"Paul Clement" wrote:

> On Fri, 23 Feb 2007 13:23:26 -0800, dlevine54 <dlevin***@discussions.microsoft.com> wrote:
>
> ¤ Thanks Paul, the wrapper worked but now I an getting the following error:
> ¤
> ¤ OledbException: ORA-0655: Line 1, column 31
> ¤ Encountered the symble ")" when expecting ...
> ¤
> ¤ I modified my code and created a single oracle procedure to test with.
> ¤ Everything works fine for input parameters but when I add an output parameter
> ¤ this error comes up so it's something to do with the output parameters.
> ¤
> ¤ New procedure;
> ¤ CREATE OR REPLACE PROCEDURE COSMOS.app_test(
> ¤     test in number,
> ¤     ans  out number)
> ¤ is
> ¤ v_ans number;
> ¤
> ¤ begin
> ¤ v_ans := test * 2;
> ¤ Afc_log_pkg.put_line('Atest', v_ans);
> ¤ ans := v_ans;
> ¤ end;
> ¤ /
>
> I don't see anything obvious from the PL/SQL you posted. You might want to try the .NET Oracle
> provider instead of OLEDB:
>
> http://msdn2.microsoft.com/en-us/library/ms971506.aspx
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>