Oracle/ASP hanging

From: s_gemberling <s_gemberling_at_yahoo.com>
Date: 25 Aug 2003 08:00:22 -0700
Message-ID: <1e64ee35.0308250700.45de42a7_at_posting.google.com>

I am connecting to Oracle 9i via an ASP page using the ORacle OLEdB driver (OraOLEDB). When I try to execute the following vbscript code in ASP my page hangs(no timeouts, no errors, etc.) It just looks like it is still retreiving the page. I have run the SQL statement directly in SQL plus and it runs run. I executed the package from sql plus...it too runs fine. There is only 1 record in the table so too much data is not an issue. When I use this exact same format for any other table that I select data from, this work. The only difference is that I am selecting data from more than 1 table in the sql statement. Why just this table, this package, or this ASP vbscript function?

Connection String

---

Public Const cst_Tasks_ConnectionString = "Provider=OraOLEDB.Oracle;Password=XXXXX;User ID=XXXXX;Data Source=XXXXXX;PLSQLRSet=1;OLEDB.NET=false"

ASP Code

---

Function b_GetWorkOrders()

        on error resume next         

        dim cmd         

	Set conn = Server.CreateObject("ADODB.Connection")
	conn.Open cst_Tasks_ConnectionString
	conn.cursorlocation = adUseClient
		
	set cmd = server.CreateObject("ADODB.Command")
	Set cmd.ActiveConnection = conn
	cmd.CommandText = "PRJMGMT.pkgWorkOrders.procGetWorkOrders"
	
	set rsWorkOrders = Server.CreateObject("ADODB.Recordset")
	rsWorkOrders.CursorLocation = adUseclient
	rsWorkOrders.locktype = adLockReadOnly
	rsWorkOrders.cursortype = adOpenForwardOnly
	
	set rsWorkOrders = cmd.Execute
		
	if err.number = 0 then
		if not(rsWorkOrders.BOF and rsWorkOrders.EOF) then
		       b_GetWorkOrders = true
		else
			b_GetWorkOrders = false
		end if
	else
		Response.Write err.Description
		Response.end
		b_GetWorkOrders = false
	end if
		
	'Cleanup
	set cmd.ActiveConnection = nothing
	set rsWorkOrders.activeconnection = nothing
	conn.close
	

End function

Package

---

CREATE OR REPLACE PACKAGE PRJMGMT.pkgWorkOrders AS

	TYPE  CURSOR_TYPE IS REF CURSOR;
	PROCEDURE procGetWorkOrders(RESULT_SET_1 OUT CURSOR_TYPE);

END pkgWorkOrders;
/

CREATE OR REPLACE PACKAGE BODY PRJMGMT.pkgWorkOrders AS

    PROCEDURE procGetWorkOrders(RESULT_SET_2 OUT CURSOR_TYPE)     AS
    BEGIN

      OPEN RESULT_SET_2 FOR
      SELECT wo.work_order_id, wo.work_order_type_cd,
wo.creation_datetime,  wo.project_id, p.project_name,
wo.technical_contact, wo.requested_completion_date,
wo.production_date, wo.actual_start_date,
wo.estimated_completion_date, wo.actual_completion_date,
wo.priority_id, pr.priority_name, wo.service_level_id,

sl.service_level_name, wo.work_type_id, wt.work_type_name, wo.status_id, s.status_name, c.customer_name

       FROM PRJMGMT.WORK_ORDERS wo, PRJMGMT.PROJECTS p, PRJMGMT.PRIORITIES pr, PRJMGMT.SERVICE_LEVELS sl, PRJMGMT.WORK_TYPES wt, PRJMGMT.STATUS s, PRJMGMT.CUSTOMERS c

       WHERE p.project_id = wo.project_id and pr.priority_id = wo.priority_id and sl.service_level_id = wo.service_level_id and wt.work_type_id = wo.work_type_id and s.status_id = wo.status_id and c.customer_id = p.customer_id

   END procGetWorkOrders;

END pkgWorkOrders;

/

GRANT EXECUTE ON PRJMGMT.pkgWorkOrders TO PRJMGMT;
/
Received on Mon Aug 25 2003 - 17:00:22 CEST