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;
/ 10 6313
Ross -
I did this now I get:
Unspecified error
that error's even LESS helpful....uuggghhhh! Thank you though...
I also tried cmd.commandType = adCmdText, that gives the Invalid SQl
statement error as well.
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
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...)
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
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
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...
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?
Never mind...To_Char(creation_datetime) works....
Thanks for all your help!
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Pardhasaradhy |
last post by:
Hello,
I have a linked server to oracle 7.1 from SQL 2000. when I try to
execute simple select statement which returns one row of data using
openquery is not fetching the data. After 30 minutes...
|
by: Harlan Messinger |
last post by:
A publication style guide indicates that for a table heading like the
following,
Table 3. Wheat and rye harvest in European countries in years that end in 3
or 7 or when a new prime minister...
|
by: Shannon Jacobs |
last post by:
Maybe there is a simple trick here, and I'm not spotting it... Is there a
guru of CSS hanging around here who can help out?
The page in question has a multi-column table with a list of links in...
|
by: Mark A |
last post by:
Here is Mark Townsend's (Oracle Product Manager) explanation and
justification for posting in the DB2 newsgroup, as posted on the Oracle
newsgroup, and my response:
"Mark Townsend"...
|
by: Nathan |
last post by:
Hi
We are having an application that uses ASP.Net / Oracle DB 9.2.0.7 /
ODP for .net with connection pooling on
We are using ODP .Net 9.2.0.4.01.
We have set the parameters as below in the...
|
by: =?iso-8859-1?q?Jean-Fran=E7ois_Michaud?= |
last post by:
Hello guys,
I was wondering if anybody here had implemented a solution where
Tables are aligned according to what the hanging indent tells us when
there is a potential for the table overflowing...
|
by: techquest |
last post by:
Hi,
I want to connect into oracle database and export the table data into a flat file using UNIX shell scripts. I cant use other GUI tools to do this, as the dataload will be in millions. hence if...
|
by: s_gemberling |
last post by:
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...
|
by: vquanski |
last post by:
How to program around ora-02068 error
On DB2 side, newbie to oracle platform. . .HELP !!
Oracle procedure, db link to DB2, gets 02068 rpc disconnect errors inconsistently/randomly, each require...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |