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;
/
