StrataFrame Forum

Oracle Global Temporary Table Trouble

http://forum.strataframe.net/Topic29450.aspx

By Kirk M Sherhart - 1/26/2011

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
By Trent L. Taylor - 1/27/2011

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);
By Kirk M Sherhart - 1/27/2011

Thanks, Trent.  But I get the same behavior.