Re: How to bypass referential integrity

I'll defer to Mike since he's obviously familiar with Cold Fusion and I haven't a clue.

One thought from the Oracle side though - if Cold Fusion is calling a procedure which returns a result set and that's it, then put a COMMIT as the first line of the procedure and make the table 'on commit release'.

Oracle can't have two threads for one session, (unless Cold Fusion is doing something funny with multiple XA-type threads ?!) so the commit should eliminate all the rows from the previous call.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Mike A Brown wrote in message <3B211617.64668CD4_at_noaa.gov>...


>VanMessner,

>

>I am pretty sure your problem is not in the database or database related.

 I


>believe the problem is your Cold Fusion (CF) programing.  Have you CF

>developers investigate the CFLOCK tag.  I have done CF and Oracle work and

 this


>is a VERY common problem with CF programming.  The CF server/web server are

>multi-threaded and results can get screwed up, as you are seeing.  If the

 CF


>tag calling the Oracle procedure is 'surrounded' with a CFLOCK tag, the

 process


>becomes single thread and each thread is processed without confusion.  Your

 CF


>developers shoul also check out Allaires support site, www.allaire.com.  If

>they search thru the threads, I am sure they find something on this

 problem.  I


>would also have them search the Knowledge Base on the same web site; I

 think


>there are papers on this issue.  The is a common problem!  CF is a great

 tool


>for inexperienced people to create web pages, but it does not 'solve' all

 the


>known problems in building applications.  I learned the hard way.  Good

 luck!


>

>Van Messner wrote:

>

>> I should be more specific.  The developers had a ColdFusion page

 (script?,


>> code? I don't know the official name for this) which included SQL

 selects.


>> They were building a matrix for a web page using data from one of my

 Oracle


>> databases.  They were doing a select for one cell, then a select for the

>> next cell, etc.!!  Not surprisingly, things ran so slowly that it took an

>> end user two minutes to see any results.  I wrote a stored procedure

 which


>> found all the cells and popped them, along with row and column headings,

>> into a global temporary table.  Then all ColdFusion had to do was call

 the


>> procedure and present the temporary table on the web page.  Users got

 their


>> results in a second or two.

>> Where the problems began was when multiple users hit the ColdFusion page

>> simultaneously.  Since Oracle sees all the users as not only a single

 user,


>> but as a single user in the same session, the global temporary tables

 have


>> everyone's matrices mixed together and are useless.

>> I was looking for a workaround so that Oracle would see each different

 user


>> doing work as a separate session.  As you can tell I'm not a ColdFusion

>> expert, but then, apparently,  neither are the developers.

>>

>> Van

>>