Oracle Global Temporary Table Trouble

Author	Message
Kirk M Sherhart	Kirk M Sherhart posted 13 Years Ago Topic Details Share Topic
Group: Forum Members Posts: 41, Visits: 259	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 Tags global temporary table gtt Oracle
	Reply Like 2

Merge into selected topic...

Merge into merge target...

Merge into a specific topic ID...

Kirk M Sherhart - 13 Years Ago

Instead of using the FillByStoredProcedure, use the FillDataTable...

Trent L. Taylor - 13 Years Ago

Thanks, Trent. But I get the same behavior.

Kirk M Sherhart - 13 Years Ago

Login
Remember Me Reset Password Resend Validation Email Login