StrataFrame Forum » StrataFrame Application Framework - V1 » Issues » Oracle Global Temporary Table Trouble

Oracle Global Temporary Table Trouble

Kirk M Sherhart — Thu, 27 Jan 2011 06:50:57 GMT

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.

TIA

RE: Oracle Global Temporary Table Trouble

Kirk M Sherhart — Thu, 27 Jan 2011 06:50:57 GMT

Thanks, Trent. But I get the same behavior.

RE: Oracle Global Temporary Table Trouble

Trent L. Taylor — Thu, 27 Jan 2011 06:22:45 GMT

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:

[codesnippet]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);[/codesnippet]