Oracle/ASP hanging

[s_gemberling]

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

[s_gemberling]

Ross -

I did this now I get:

Unspecified error
that error's even LESS helpful....uuggghhhh! Thank you though...

[s_gemberling]

I also tried cmd.commandType = adCmdText, that gives the Invalid SQl
statement error as well.

[Ross McKay]

On 26 Aug 2003 06:33:41 -0700, s_**********@yahoo.com (s_gemberling)
wrote:

I did this now I get:

Unspecified error

that error's even LESS helpful....uuggghhhh! Thank you though...

Um, ok, then first:

When I said "Set ... to" I meant: cmd.CommandType = adCmdStoredProc

Secondly, why not use a debugger to see which line you get the error on
now? Or alternatively, comment out "on error resume next" so that IIS
dumps the error to the output.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello

[s_gemberling]

Hi Ross -

Thanks again. (I knew what you meant about that "Set" thing...) I
commented out the on error resume next and I get the "Invalid SQL
statement" error on the cmd.Execute line. I put an exception handler
in my package and it doesn't return anything. I ran the package in
SQL Plus and it runs fine. I am completely baffled! I even changed the
package to a more simple select (Select * from PRJMGMT.work_orders;)
It still doesn't work. The confusing part is that other thatn the
table/package/procedure names, I have several other functions that do
the same exact thing and they all work fine!!! (Scratching head...)

[Ross McKay]

On 25 Aug 2003 08:00:22 -0700, s_**********@yahoo.com (s_gemberling)
wrote:

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)

Nah, it couldn't be that easy, could it? Check out the names of the
result sets. I didn't think they necessarily had to match, but...
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello

[Ross McKay]

On 25 Aug 2003 08:00:22 -0700, s_**********@yahoo.com (s_gemberling)
wrote:

set rsWorkOrders = Server.CreateObject("ADODB.Recordset")
rsWorkOrders.CursorLocation = adUseclient
rsWorkOrders.locktype = adLockReadOnly
rsWorkOrders.cursortype = adOpenForwardOnly

set rsWorkOrders = cmd.Execute

A subtle point, not connected to your problem, but realise that the last
line above negates all the other lines above by totally replacing the
object held by rsWorkOrders. Thus, you can lose those lines totally,
keeping just the last line.

If you did want to create a recordset object, finesse the properties,
and call the stored procedure with results into this recordset, replace
the last line with:

rsWordOrders.Open cmd

Still looking at your code....
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello

[s_gemberling]

This was a typo on my part, they are actually the same in my code...so
that's no the problem....hmmmmm

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)

Nah, it couldn't be that easy, could it? Check out the names of the
result sets. I didn't think they necessarily had to match, but...

[s_gemberling]

OK...I figured it out. One of my fields (creation_datetime) is of
type TIMESTAMP(0) in the table. once I removed that field from the
select and order by clauses, it worked. Any ideas on how to return a
value from an Oracle timestamp field?

[s_gemberling]

Never mind...To_Char(creation_datetime) works....

Thanks for all your help!

[Ross McKay]

On 27 Aug 2003 08:07:13 -0700, s_**********@yahoo.com (s_gemberling)
wrote:

Never mind...To_Char(creation_datetime) works....

Thanks for clearing that up. I was starting to wonder if my eyes were
working, because I couldn't see any problem!

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello