Creating temp tables from C# application and then using them

Because of the constraints with the size VARCHAR and the number of elements allowed in a WHERE IN clause, I need to create a temp_table that I insert ids into and then within my stored procedure select from that temp_table. Currently, my stored procedure sends in an associative array of the ids, inserts the ids into a global temp table and then SELECT * FROM prod_table WHERE prod_ids IN (SELECT prod_ids FROM temp_table). I guess my question is, if I create the temp_table from within my application, will the select from the stored procedure still work? Will it still be the same "session" or whatever as long as I use the same connection? How do I create a temp table from within my C# application? Unfortunately, I have to get rid of the associate array because of an Oracle bug.
LVL 1
sfarleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
1. if I create the temp_table from within my application, will the select from the stored procedure still work? YES it will work
2. Will it still be the same "session" or whatever as long as I use the same connection? Untill you dont drop taht temp table it will remain and you can use them in that session.
3.How do I create a temp table from within my C# application? Simple create a temptable with sessionid concatinated at the end and drop it after the use is over.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
As long as the application does not disconnect from the database and reconnect (it is in the same session), then the application can insert the records in the global temporary table.

You do not need to create the global temporary table within the application, it is created in the database and when a session inserts into it, the copy for that session is created.  Nothing to do on the application side.

Be sure the global temporary table was created with on commit preserve rows so that it becomes a session level table.

I believe that a global temporary table is a better solution than creating a dropping a table within the application.  If you do it this way, you will need to have some way to pass the table name into the procedure and you would have to use dynamic SQL to get the data, which is not always best.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.