Home » Other » General » problem with ASP and stored procedure returning sys_refcursor
problem with ASP and stored procedure returning sys_refcursor [message #125450] Sat, 25 June 2005 12:57 Go to next message
gdeconto
Messages: 41
Registered: June 2005
Location: Vancouver, British Columb...
Member
Wasnt sure where to post this so here it went.

I am trying to figure out how to get get an ASP page to get and display a resultset from a simple stored procedure. I am getting this error:

Error Type:
OraOLEDB (0x80040E14)
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GETEMP' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
/ORACLETEST3.ASP, line 15

I have seen many requests for help on this error in other forums and newsgroups but havent seen anything that has helped me. It looks like my problem is something really simple (aka dumb).

I have tried to figure this out on my own but I am now officially stumped...

Any help would be appreciated.

My stored procedure is in the Scott schema and looks like this (note that I am using oracle9i and that executing this in sqlplus works fine):

CREATE OR REPLACE PROCEDURE SCOTT.GETEMP(
p_empno IN NUMBER DEFAULT NULL,
genericcursor OUT sys_refcursor)
IS
BEGIN
OPEN genericcursor FOR
SELECT *
FROM emp
WHERE (p_empno = 0
OR empno = p_empno)
ORDER BY ename;
END;
/

My test ASP page looks like this:

<!--#include virtual="/include/adovbs.asp"-->
<%
ConnStr = "Provider=OraOLEDB.Oracle; Data Source=ORACLE9I; USER ID=scott; PASSWORD=tiger;"

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connStr

set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConn
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "getemp"
objCommand.Parameters.Append objCommand.CreateParameter("param1", adInteger, adParamInput, 10, 0)

set rs = Server.CreateObject("ADODB.Recordset")
Set rs = objCommand.Execute

%>

<html>
<head>
<title>Untitled</title>
</head>

<body>

<% if NOT rs is nothing then %>
<% do while NOT rs.EOF %>
<%=rs("empno") & "-" & rs("ename")%><BR>
<% rs.movenext %>
<% loop %>
<% end if %>

</body>
</html>


Can't get much simpler than that. I don't see whatever obvious error is in there.

Can anyone help me out here???
Re: problem with ASP and stored procedure returning sys_refcursor [message #125459 is a reply to message #125450] Sat, 25 June 2005 19:02 Go to previous messageGo to next message
gdeconto
Messages: 41
Registered: June 2005
Location: Vancouver, British Columb...
Member
seems that when I switched to using either the ado driver or the DSN connection that things worked fine.

does anyone know why the others wouldnt work??

ie why these two work:
1) ConnStr = "DSN=ORACLE9I; User ID=scott; Password=tiger"
2) ConnStr = "Provider=MSDAORA.1; Data Source=ORACLE9I; User ID=scott; Password=tiger"

and these two dont:
3) ConnStr = "DRIVER={Microsoft ODBC for Oracle}; SERVER=ORACLE9I; UID=scott; PWD=tiger"
4) ConnStr = "Provider=OraOLEDB.Oracle; Data Source=ORACLE9I; USER ID=scott; PASSWORD=tiger;"

any help appreciated

[Updated on: Sun, 26 June 2005 11:07]

Report message to a moderator

Re: problem with ASP and stored procedure returning sys_refcursor [message #566395 is a reply to message #125459] Thu, 13 September 2012 22:22 Go to previous messageGo to next message
abadke
Messages: 1
Registered: September 2012
Junior Member
Hi guys, this response is for all the developers who still have to maintain legacy asp applications in companies that hate to upgrade their apps.
The answer to this question is here - its a FEATURE of the oraOLEDB dataprovider (thanks oracle).

(http) docs.oracle.com/cd/E11882_01/win.112/e17726/using.htm#autoId23

PLSQLRSet

This property is similar to the PLSQLRSet connection string attribute.

The property specifies whether OraOLEDB must return a rowset from the PL/SQL stored procedure. If the stored procedure, provided by the consumer, returns a rowset, PLSQLRSet must be set to TRUE (enabled). This property should be set to FALSE after the command has been run. By default, the property is set to FALSE (disabled).

objCmd.Properties("PLSQLRSet") = TRUE

Hope this helps somebody to save the 6 hours i just spent finding this.
Cheers
Re: problem with ASP and stored procedure returning sys_refcursor [message #566454 is a reply to message #566395] Fri, 14 September 2012 13:10 Go to previous message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Thanks for mentioning the "feature". Another cool one is BindByName for passing named parameters instead of ordered by position
Previous Topic: Capacity Planing & IO Requirement.
Next Topic: Forms and database licence (3 merged)
Goto Forum:
  


Current Time: Thu Mar 28 08:49:07 CDT 2024