Re: stored procedure and recordset in ASP

On 18 May 2006 08:58:19 -0700, "Alen Cappelletti" <cocker_at_libero.it> 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