ASP.NET & Oracle Stored Procedure Error PLS-00306: wrong number or typ 1

[StuckInTheMiddle]

I'm moving an application from ASP and Oracle 8i to ASP.NET and Oracle 8i and have when using ASP.NET and the
OLEDB Data Provider for Oracle and calling stored procedures i know work which return resultsets, i get a nasty 'ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PRC_GET_NAMES' ORA-06550: line 1, column 7: PL/SQL: Statement ignored '
message.

I know the stored procedure has only 2 returned parameters, 1 record count and one resultset. I have the correct no. of types, and the resulset i have setup as a type OleDbType.VarChar, maybe this is the problem? I cant see any other option that would be suitable (tried all of them to no avail). It is a resultset of varchar2s that are being returned (user names).

Does anybody have any ideas to solve this problem? Or should i look at another method of connecting to Oracle?

Any help appreciated, thanks

 

[Custom24]

Hi
I don't know if I can help - I also use Oracle 8i and ASP.net thru oledb. Can you post the signature of the Oracle procedure (where the parameters are declared) and the code you use to call the proc from ASP.net, from where you declare the command object to the execute.

Mark

 

[StuckInTheMiddle]

Hi Mark,

Here is the package header i'm using, any advice you may have appreciated. Let me know if you require more info.

Thanks

--Package Header
CREATE OR REPLACE PACKAGE pck_search
AS
TYPE tbl_last IS TABLE of orgs.lastname%type
INDEX BY BINARY_INTEGER;


PROCEDURE prc_fprs_get_names
(
o_lastname OUT tbl_last,
o_sql_err_code OUT NUMBER,
o_sql_err_text OUT VARCHAR2
);

End pck_search; --Package Header
/


And the ASP.NET code to call this is as follows (there are no input parameters, just 3 outputs)



Dim cmdNames As OleDbCommand = New OleDbCommand("pck_search.prc_fprs_get_names",OleDbConnection1)

Dim dpParameter As OleDbParameter

cmdNames.CommandType = CommandType.StoredProcedure

dpParameter = New OleDbParameter("o_lastname", OleDbType.VarChar, 20)
dpParameter.Direction = ParameterDirection.Output
cmdNames.Parameters.Add(dpParameter)

dpParameter = New OleDbParameter("o_sql_err_code", OleDbType.Numeric)
dpParameter.Direction = ParameterDirection.Output
cmdNames.Parameters.Add(dpParameter)

dpParameter = New OleDbParameter("o_sql_err_text", OleDbType.VarChar, 100)
dpParameter.Direction = ParameterDirection.Output
cmdNames.Parameters.Add(dpParameter)


OleDbConnection1.Open()
cmdNames.ExecuteNonQuery()
OleDbConnection1.Close()

 

[Custom24]

Hello again
It seems pretty obvious that your o_lastname parameter is causing the problem, since Oracle is outputting a table and ASP.net is expecting a regular VarChar or string.

Plus, I am not sure how this would work even in Oracle, because the tbl_last type is local to this package.

To be honest, I've not yet managed to transfer table or recordset types between Oracle and VB in either direction, even with VB6 or regular asp. The approach I've taken to overcome this is to concatenate these types into regular strings using a delimiter, and to split the elements out in either VB or PL/SQL, depending on which way the parameters are being passed.

Is this an option for you?

You say you've managed to get this working before in regular asp - how did it work then? (ie how were your params declared in asp and Oracle in that case?)

Thanks Mark

 

[StuckInTheMiddle]

Mark,

Thanks for the advice, unfortunately comma delimited list isnt really an option, But i;m not giving up on this yet. FYI, here is how this works in traditional ASP

Set cmdStoredProc = Server.CreateObject("ADODB.Command&quot
Set cmdStoredProc.ActiveConnection = cnnOracle

'Retrieve all records.
cmdStoredProc.CommandText = "{call pck_search.prc_fprs_get_names({resultset 2000, o_orgs_last})}"

cmdStoredProc.CommandType = adCmdText

Set rsResults = Server.CreateObject("ADODB.Recordset&quot
rsResults.CursorType = adOpenStatic

Set rsResults.Source = cmdStoredProc

rsResults.Open
%>

And then loop through the resultset (into a html table or whatever), much better then using a comma delimited string, however, this approach of calling a stored procedure as above i cant get working in .NET. When i do, i'll be sure to let you know how i got it.

 

[Custom24]

Hello
Thanks for your post. I've had a look, and I found an example of how to return a REF CURSOR parameter from a PL/SQL stored proc to VB.net, but it uses the MS .net managed provider for Oracle. I looked at using a similar approach using oledb, but the problem is that there does not seems to be an appropriate parameter type in oledb - when you look at the help for OleDbType Enumeration, none of the types listed mention anything useful.

I've posted the code below which I found for the .net Oracle provider - if you do not have it yet you can download it free from MSDN

Hope this helps you. PS - I've not tried out the example!

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connString As New String("Data Source=Oracle8i;Integrated Security=yes&quot

Dim conn As New OracleConnection(connString)

conn.Open()

Dim cmd As New OracleCommand()

cmd.Connection = conn
cmd.CommandText = "CURSPKG.OPEN_ONE_CURSOR"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("N_EMPNO", OracleType.Number)).Value = 7369
cmd.Parameters.Add(New OracleParameter("IO_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output

Dim rdr As OracleDataReader

rdr = cmd.ExecuteReader()

While (rdr.Read())
REM do something with the values
End While

rdr.Close()
conn.Close()

End Sub

Mark

 

[StuckInTheMiddle]

Mark,

Thanks for that update, that was just what i needed. I can now retrieve the resultset from my modified package in .NET. I used a dataset rather then a reader and bound it to the grid, it's nice when things finally work

I just hope the rest of my project goes a little smoother