Oracle Global Temporary Table Trouble

Author Message
 Posted 1/26/2011 6:56:28 AM
StrataFrame Beginner

StrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame Beginner

Group: Forum Members
Last Login: 7/16/2020 11:27:12 AM
Posts: 41, Visits: 424
I have a BO that uses a FillByStoredProcedure.  The underlying Oracle procedure uses an Oracle Global Temporary Table (GTT) with the option of "ON COMMIT DELETE ROWS".  The Oracle proc is performing OK, but no rows are returned to the BO.

If I change to use the GTT option of "ON COMMIT PRESERVE ROWS", the FillByStoredProcedure works as expected.  (I'm not issuing any COMMITs or DDL expressions in my code.)

I've seen the same problem with queries that use Oracle's "subquery_factoring_clause" (i.e., a "WITH qname AS (subquery)").  If the Oracle optimizer chooses to materialize the subquery (i.e., create a GTT for the subquery), then no results are returned to the BO.  However, in this case, there is no way to set the subquery's GTT to use "ON COMMIT PRESERVE ROWS".

Any help is appreciated.
Post #29450
Add to Twitter Add to Facebook
 Posted 1/27/2011 12:22:45 AM
StrataFrame Developer

StrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame Developer

Group: StrataFrame Developers
Last Login: 11/20/2019 8:57:25 AM
Posts: 6,610, Visits: 6,776
Instead of using the FillByStoredProcedure, use the FillDataTable command and set the command type on DbCommand.  I will use the SqlCommand since I am not sure what Oracle object you are using:

SqlCommand cmd = new SqlCommand("dbo.MySproc");

//-- This will ensure that the command is treated as a SPROC
cmd.CommandType = CommandType.StoredProcedure;

//-- Set any parms
cmd.Parameters.Add("@Parm1", SqlDbType.Integer).Value = MyValue;

//-- Execute the query
this.FillDataTable(cmd);
Post #29459
Add to Twitter Add to Facebook
 Posted 1/27/2011 12:50:57 AM
StrataFrame Beginner

StrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame Beginner

Group: Forum Members
Last Login: 7/16/2020 11:27:12 AM
Posts: 41, Visits: 424
Thanks, Trent.  But I get the same behavior.
Post #29461
Add to Twitter Add to Facebook


Similar Topics

Expand / Collapse

Reading This Topic

Expand / Collapse

Site Map - Home - My Account - Forum - About Us - Contact Us - Try It - Buy It

Microsoft, Visual Studio, and the Visual Studio logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.