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_Conne ctionString =
"Provider=OraOL EDB.Oracle;Pass word=XXXXX;User ID=XXXXX;Data
Source=XXXXXX;P LSQLRSet=1;OLED B.NET=false"
ASP Code
------------
Function b_GetWorkOrders ()
on error resume next
dim cmd
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open cst_Tasks_Conne ctionString
conn.cursorloca tion = adUseClient
set cmd = server.CreateOb ject("ADODB.Com mand")
Set cmd.ActiveConne ction = conn
cmd.CommandText = "PRJMGMT.pkgWor kOrders.procGet WorkOrders"
set rsWorkOrders = Server.CreateOb ject("ADODB.Rec ordset")
rsWorkOrders.Cu rsorLocation = adUseclient
rsWorkOrders.lo cktype = adLockReadOnly
rsWorkOrders.cu rsortype = adOpenForwardOn ly
set rsWorkOrders = cmd.Execute
if err.number = 0 then
if not(rsWorkOrder s.BOF and rsWorkOrders.EO F) 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.ActiveConne ction = nothing
set rsWorkOrders.ac tiveconnection = nothing
conn.close
End function
Package
----------------------------------
CREATE OR REPLACE PACKAGE PRJMGMT.pkgWork Orders AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE procGetWorkOrde rs(RESULT_SET_1 OUT CURSOR_TYPE);
END pkgWorkOrders;
/
CREATE OR REPLACE PACKAGE BODY PRJMGMT.pkgWork Orders AS
PROCEDURE procGetWorkOrde rs(RESULT_SET_2 OUT CURSOR_TYPE)
AS
BEGIN
OPEN RESULT_SET_2 FOR
SELECT wo.work_order_i d, wo.work_order_t ype_cd,
wo.creation_dat etime, wo.project_id, p.project_name,
wo.technical_co ntact, wo.requested_co mpletion_date,
wo.production_d ate, wo.actual_start _date,
wo.estimated_co mpletion_date, wo.actual_compl etion_date,
wo.priority_id, pr.priority_nam e, wo.service_leve l_id,
sl.service_leve l_name, wo.work_type_id , wt.work_type_na me,
wo.status_id, s.status_name, c.customer_name
FROM PRJMGMT.WORK_OR DERS wo, PRJMGMT.PROJECT S p,
PRJMGMT.PRIORIT IES pr, PRJMGMT.SERVICE _LEVELS sl, PRJMGMT.WORK_TY PES
wt, PRJMGMT.STATUS s, PRJMGMT.CUSTOME RS c
WHERE p.project_id = wo.project_id and pr.priority_id =
wo.priority_id and sl.service_leve l_id = wo.service_leve l_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 procGetWorkOrde rs;
END pkgWorkOrders;
/
GRANT EXECUTE ON PRJMGMT.pkgWork Orders TO PRJMGMT;
/
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_Conne ctionString =
"Provider=OraOL EDB.Oracle;Pass word=XXXXX;User ID=XXXXX;Data
Source=XXXXXX;P LSQLRSet=1;OLED B.NET=false"
ASP Code
------------
Function b_GetWorkOrders ()
on error resume next
dim cmd
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open cst_Tasks_Conne ctionString
conn.cursorloca tion = adUseClient
set cmd = server.CreateOb ject("ADODB.Com mand")
Set cmd.ActiveConne ction = conn
cmd.CommandText = "PRJMGMT.pkgWor kOrders.procGet WorkOrders"
set rsWorkOrders = Server.CreateOb ject("ADODB.Rec ordset")
rsWorkOrders.Cu rsorLocation = adUseclient
rsWorkOrders.lo cktype = adLockReadOnly
rsWorkOrders.cu rsortype = adOpenForwardOn ly
set rsWorkOrders = cmd.Execute
if err.number = 0 then
if not(rsWorkOrder s.BOF and rsWorkOrders.EO F) 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.ActiveConne ction = nothing
set rsWorkOrders.ac tiveconnection = nothing
conn.close
End function
Package
----------------------------------
CREATE OR REPLACE PACKAGE PRJMGMT.pkgWork Orders AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE procGetWorkOrde rs(RESULT_SET_1 OUT CURSOR_TYPE);
END pkgWorkOrders;
/
CREATE OR REPLACE PACKAGE BODY PRJMGMT.pkgWork Orders AS
PROCEDURE procGetWorkOrde rs(RESULT_SET_2 OUT CURSOR_TYPE)
AS
BEGIN
OPEN RESULT_SET_2 FOR
SELECT wo.work_order_i d, wo.work_order_t ype_cd,
wo.creation_dat etime, wo.project_id, p.project_name,
wo.technical_co ntact, wo.requested_co mpletion_date,
wo.production_d ate, wo.actual_start _date,
wo.estimated_co mpletion_date, wo.actual_compl etion_date,
wo.priority_id, pr.priority_nam e, wo.service_leve l_id,
sl.service_leve l_name, wo.work_type_id , wt.work_type_na me,
wo.status_id, s.status_name, c.customer_name
FROM PRJMGMT.WORK_OR DERS wo, PRJMGMT.PROJECT S p,
PRJMGMT.PRIORIT IES pr, PRJMGMT.SERVICE _LEVELS sl, PRJMGMT.WORK_TY PES
wt, PRJMGMT.STATUS s, PRJMGMT.CUSTOME RS c
WHERE p.project_id = wo.project_id and pr.priority_id =
wo.priority_id and sl.service_leve l_id = wo.service_leve l_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 procGetWorkOrde rs;
END pkgWorkOrders;
/
GRANT EXECUTE ON PRJMGMT.pkgWork Orders TO PRJMGMT;
/
Comment