| problem with ASP and stored procedure returning sys_refcursor [message #125450] |
Sat, 25 June 2005 12:57  |
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  |
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
