RE: Oracle

[Schreck, Tom]

Daniel- Thanks for the feedback.  The problem was scoping and I had to change preserve rows.  It works now.   Thanks   Tom Schreck 972-361-9943 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Daniel Elmore Sent: Wednesday, April 06, 2005 2:30 PM To: list@dfwcfug.org Subject: RE: Oracle   I'm guessing there is something with the scope. I haven't used 8i, only read about it. If you only need to return one recordset you could call the proc from <cfquery> and place your create temp table code outside of the proc definition, at the top of the cfquery tag. It's connection or session scoped, so it should locate it fine.   Another thought: JDBC drivers may be having problems returning data from: on commit delete rows;    try using:  on commit preserve rows;   Let me know, I'm curious too now.   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Schreck, Tom Sent: Wednesday, April 06, 2005 2:06 PM To: list@dfwcfug.org Subject: OT: Oracle Has anyone used temporary tables with ORACLE 8i?  My goal is to create a temporary table and populate it from a select statement.  I then want to manipulate data and return it as a recordset.  I cannot get the stored procedure created as the delivery method for the code I've written.   PSEUDO CODE:   --create temporary table Create global temporary table dbatest( field ,field , ... ) on commit delete rows;   -- populate temporary table from a select statement INSERT INTO dbatest (field,field,...) SELECT field,field,... FROM table WHERE something = something   --special processing of data in temp table SELECT field FROM dbatest START WITH field = 0 CONNECT BY field = PRIOR field     The above code works just fine when I process it.  I need this code to be inside a stored procedure so I can deliver the data to CF.   When I execute the create stored proc statement, it bombs on dbatest.  It says dbatest must be declared.  Why and how do I declare a temporary table?   Thanks   Tom Schreck Applications Developer Dresser, Inc. 15455 Dallas Parkway, Suite 1100 Addison, TX  75001-4690 972-361-9943 [EMAIL PROTECTED]

<<attachment: image001.jpg>>