StrataFrame Forum
Back
Login
Login
Home
»
StrataFrame Application Framework - V1
»
Issues
»
Oracle Global Temporary Table Trouble
Oracle Global Temporary Table Trouble
Post Reply
Like
2
Oracle Global Temporary Table Trouble
View
Flat Ascending
Flat Descending
Threaded
Options
Subscribe to topic
Print This Topic
RSS Feed
Goto Topics Forum
Author
Message
Kirk M Sherhart
Kirk M Sherhart
posted 13 Years Ago
ANSWER
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
Trent Taylor
Trent Taylor
posted 13 Years Ago
ANSWER
Post Details
Share Post
Group: StrataFrame Developers
Posts: 6.6K,
Visits: 6.9K
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);
Reply
Like
2
Kirk M Sherhart
Kirk M Sherhart
posted 13 Years Ago
ANSWER
Post Details
Share Post
Group: Forum Members
Posts: 41,
Visits: 259
Thanks, Trent. But I get the same behavior.
Reply
Like
2
GO
Merge Selected
Merge into selected topic...
Merge into merge target...
Merge into a specific topic ID...
Open Merge
Post Reply
Like
2
Similar Topics
Post Quoted Reply
Reading This Topic
Login
Login
Remember Me
Reset Password
Resend Validation Email
Login
Explore
Messages
Mentions
Search