Re: stored procedure and recordset in ASP

On 18 May 2006 08:58:19 -0700, "Alen Cappelletti" <cocker@xxxxxxxxx>
wrote:

Hi all,
this is my first experience with SP.

The problem is 'cause I must create a recorset in the client browser.
For one only records like log-in autentication working, but no always
have an error.

This is my Procedure
--------
CREATE OR REPLACE Procedure SempliciSelect(
strAzione IN varchar2,
intID OUT number,
strVoce OUT varchar2
)
AS

CURSOR CUR_Aree IS
SELECT
IDAREA,
VOCE
FROM AREE
WHERE (VISIBILE = 1 AND STATO = 1)
ORDER BY ORDINAMENTO;
Val_CUR_Aree CUR_Aree%ROWTYPE;

BEGIN
IF strAzione = 'Aree' Then
OPEN CUR_Aree;
LOOP
FETCH CUR_Aree INTO Val_CUR_Aree;
EXIT WHEN CUR_Aree%NOTFOUND;
intID := Val_CUR_Aree.IDAREA;
strVoce := Val_CUR_Aree.VOCE;
END LOOP;
CLOSE CUR_Aree;
END IF;
END;


++++++++++++++++++++++++++++++++
and this is my ASP codes
++++++++++++++++++++++++++++++++

...
Dim RS,SQL
SQL = "SempliciSelect ('Aree')"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, ObjConn
Do While Not RS.EOF
response.write RS("intID")&"<br>"
response.write RS("strVoce")&"<br>"
RS.movenext
Loop
RS.Close
...



+++++++++++++
This is the error
+++++++++++++
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Oracle][ODBC][Ora]ORA-00900: invalid SQL statement
/MVS/lOG-IN/test.asp, line 17

Line 17 is: RS.Open SQL, ObjConn

Thanks Alen, Italy


for ref cursors in ASP see
http://asktom.oracle.com/~tkyte/ResultSets/index.html

to address the ora-900 you would need to isolate the sql statement and
run that through (i*)sqlplus.

Recommendation: get rid of the Microsoft OLE DB provider. It is slower
and has compatibility issues with Oracle.

Hint: most people here treat this and other forums as a replacement
for searching the Internet, Usenet, and the plethora of resources that
is already available.
You seem to be no exception to your colleagues, looking at the nature
of the questions you have been posting the last few weeks.
*PLEASE* read those manuals *PRIOR* to posting, or accept to be
ignored.

--
Sybrand Bakker, Senior Oracle DBA
.