By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,877 Members | 1,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,877 IT Pros & Developers. It's quick & easy.

Oracle/ASP hanging

P: n/a
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;
/
Jul 19 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Ross -

I did this now I get:

Unspecified error
that error's even LESS helpful....uuggghhhh! Thank you though...
Jul 19 '05 #2

P: n/a
I also tried cmd.commandType = adCmdText, that gives the Invalid SQl
statement error as well.
Jul 19 '05 #3

P: n/a
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

Jul 19 '05 #4

P: n/a
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...)
Jul 19 '05 #5

P: n/a
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

Jul 19 '05 #6

P: n/a
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

Jul 19 '05 #7

P: n/a
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...

Jul 19 '05 #8

P: n/a
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?
Jul 19 '05 #9

P: n/a
Never mind...To_Char(creation_datetime) works....

Thanks for all your help!
Jul 19 '05 #10

P: n/a
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

Jul 19 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.