Help calling an Oracle procedure from vb.net

[Guest]

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\)]

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!
********************************************

 

[Guest]

Thanks but the problem is that the package is part of Oracle Apps and I can't
modify it.

 

[Paul Clement]

¤ 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)

 

[Guest]

It's always something. Thanks, I'll give that a try.

 

[Guest]

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;
/

Called by:
Dim pRequestId As OleDbParameter =
Cmd.Parameters.Add("p_request_id", OleDbType.Integer)
pRequestId.Direction = ParameterDirection.Input
pRequestId.Value = 155

Dim pans As OleDbParameter = Cmd.Parameters.Add("ans",
OleDbType.Integer)
pRequestId.Direction = ParameterDirection.Output

Try
rc = Cmd.ExecuteNonQuery
Catch ex As Exception
MsgBox(Err.GetException.ToString)
MsgBox(Err.Description)
End Try


Any ideas?
Thanks.

 

[Paul Clement]

¤ 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)

 

[Guest]

Actuall last night I deleted all the parameter code and rewrote it and now
everything is working fine. I don't know if there was a non printing
character somewhere or if it's just the computer remining me who really runs
things. Anyway it's fixed. Thanks for all your help.