Unable to get Rows from Cursor when using ASP?

[smspms2627]

I have a large SQL query using Cursors that runs fine in SQL Dvlp. I now need to move it over to ASP. If I do a simple query the ASP page displays fine but once I add the DECLARE Variables and I even stripped down my Curson to something very simple it nevers Fetches any rows - always returns 0 rows???Is there something diff about using Cursors in SQL Dvlp vs. ASP??

sSQL = "DECLARE" & vbCr & _
"range_star t DATE;" & vbCr & _
"range_end DATE;" & vbCr & _
"range_day_ end DATE;" & vbCr & _
"sStart VARCHAR2(20);" & vbCr & _
"sEND VARCHAR2(20);" & vbCr & _
"clic_date DATE;" & vbCr & _
"cdef_cnt NUMBER := 0;" & vbCr & _
"cqc_cnt NUMBER := 0;" & vbCr & _
"cbpt_cnt NUMBER := 0;" & vbCr & _
"f_lic_date DATE;" & vbCr & _
"f_def_cnt NUMBER := 0;" & vbCr & _
"f_qc_cnt NUMBER := 0;" & vbCr & _
"f_bpt_cnt NUMBER := 0;" & vbCr & _
"total_def_ cnt NUMBER := 0;" & vbCr & _
"total_qc_c nt NUMBER := 0;" & vbCr & _
"total_bpt_ cnt NUMBER := 0;" & vbCr & _
"CURSOR c1 IS SELECT lic_date INTO clic_date " & vbCr & _
"FROM cust_aggregated _license ; " & vbCr & _
"BEGIN " & vbCr & _
"OPEN c1; " & vbCr & _
" LOOP " & vbCr & _
" FETCH c1 INTO f_lic_date; " & vbCr & _
" EXIT WHEN c1 % NOTFOUND; " & vbCr & _
" END LOOP; " & vbCr & _
"CLOSE c1; " & vbCr & _
"END;"

oRS.CursorLocat ion= 3 'adUseClient
oRS.CursorType = 3
Set oSQL.ActiveConn ection = oConn
oSQL.CommandTex t = sSQL

oRS.Open oSQL ' this code works with simple select stmt

If oRS.State = 0 Then
Response.Write "<H2></td><td><font face=Verdana color=black size=-2>|&nbsp; ERROR </H2>" & vbCrLf
Else
oRS.MoveFirst
EnD if
Thanks!

[jhardman]

In a desktop application it makes sense to hold your place in a recordset with a cursor, but I don't think that would work with ASP, unless you declared your db connection to be session-level, and that's very non-standard. the problem is that the asp script re-creates the db connection every time it executes, so you can't hold your place with a cursor. At least that's how I always thought it worked. Does that make sense?

Jared

[smspms2627]

A DBA told me I probably need to call a Stored Procedure as ASP will not read a complex PL/SQL query... trying that route

[jhardman]

most-likely true. If you can do everything with a single table result (a recordset) then ASP can do it, otherwise ASP is going to be pretty awkward. That said, SPs are almost always good ideas.

Jared

[smspms2627]

Still having issue so created a Very Simple Stored Proc:
create or replace PROCEDURE test
AS
i NUMBER := 0;
myD DATE;
BEGIN
select lic_date into myD from cust_aggregated _license where to_char(lic_dat e,'mm/dd/yyyy hh24:mi:ss') >= '03/24/2010 00:00:00';
DBMS_OUTPUT.PUT _LINE('Date:' || ' ' || myD );
END;


Then over on ASP am calling like this:
Set oSQL.ActiveConn ection = oConn
oSQL.CommandTex t = "test"
oSQL.commandtyp e = 4 'stored proc

oSQL.Execute

How do I get my Response.write value?
Do i need to use output in .Parameters.App end Method?
Any help or example? Much appreicated!!!!

[jhardman]

does this just return one field value? then this might work:

response.write oSQL.Execute