Home » SQL & PL/SQL » SQL & PL/SQL » Creating Temporary tables on fly (Oracle 10g)
Creating Temporary tables on fly [message #393466] Mon, 23 March 2009 00:05 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to get some data from front-end in Java to a oracle procedure to validate the data entered and return back an array of error if there are any.
One choice is passing array from java to oracle procedure and then change it to recordset and then validate.....return the error if exists.
I have no idea of Java and even Java people are not much aware of integration of arrays with Oracle.
So second idea is to create a temporary table and inserting data from frontend to temp table and then procedure will directly validate data from Temp table and send the array of errors to Java.

Can we create Temporary tabe on fly for each session with same name
CREATE GLOBAL TEMPORARY TABLE [B]screen_1_temp [/B](  name  varchar2(50),  age  NUMBER) ON COMMIT PRESERVE ROWS;


I was planning to create the temp table, insert data from front-end and then call the procedure where the procedure will read data from temp table and return list of errors if there are any. Then the errors will be checked again and fresh data will be inserted to temp table after truncating it.
Again the procedure will be called until there are no errors.

Please advice me on this....if creating tem table is a good idea...

Regards,
Mahi
Re: Creating Temporary tables on fly [message #393468 is a reply to message #393466] Mon, 23 March 2009 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Can we create Temporary tabe on fly for each session with same name
Can you? yes
Should you? NO No no no never!
There is no need to do so.
A GTT can & should be used by multiple session without interference between session.
Re: Creating Temporary tables on fly [message #393471 is a reply to message #393468] Mon, 23 March 2009 00:18 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I am sorry but I didn't get you.
There is no need to do so.

Please tell me what you meant by saying this. Do you mean I should not create Global Temp Tables....
Or do you mean I should create a Global Temp table only once and each new session can use the same table again and again without any problem with different sessions.....
Also for a single session, I have two different forms in front-end where first form will be inserting the first 35 field values to validate in procedure....I have to come again to second form after validation...truncate the temp table for that session and insert the whole data again..... please advice if I am wrong some where.

Thanks for looking into this,

Mahi
Re: Creating Temporary tables on fly [message #393473 is a reply to message #393466] Mon, 23 March 2009 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Also for a single session, I have two different forms in front-end where first form will be inserting the first 35 field values to validate in procedure....I have to come again to second form after validation...truncate the temp table for that session and insert the whole data again

Words matter!
What is "first form" versus "second form"?
Neither of which have to do with Oracle RDBMS.

With "WEB Forms", no inherent connection exists between forms.
Re: Creating Temporary tables on fly [message #393478 is a reply to message #393473] Mon, 23 March 2009 00:46 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
There will be two front end forms in Java that will be passing data to Oracle procedure for validation of data.

So one option is creating Temp table, insert data into it and then validate in procedure....then truncate the table for that session...again the form2 will insert fresh data for the same session and then call the procedure to validate the data...!!

Second option would be that Java would pass the data in array to the procedure and I need to validate it.
But second option is new to me.... not sure how to do this.... as the same procedure would be used where a parking table needs to be read and validated for multiple records at a time. So a multiple dimention array need to be used in this case.

And I am not sure which option would be better in terms of performance.
Please help me on this.... as which option I should take as there would be many records to process when the procedure would read the parking table for validation. The procedure has to be made generic for both interface data as well as parking data.
The data would be same from both.....but its only that from interface only one record would be read at one time and millions from parking table.

I am sorry if I am confusing you. I will try to explain you if I confused you.

Regards,
Mahi
Re: Creating Temporary tables on fly [message #393479 is a reply to message #393466] Mon, 23 March 2009 00:50 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Creating Temporary tables on fly [message #393483 is a reply to message #393479] Mon, 23 March 2009 01:05 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
The table create statement is too big to post here...64 columns.
I am sorry that I am asking two questions in one post. But please suggest me which option would be better as per performance so that I can start thinking from that point of view.

Passing arrays from java to Procedure or inserting the data from java to temp table....
Re: Creating Temporary tables on fly [message #393486 is a reply to message #393483] Mon, 23 March 2009 01:32 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Look for JAVA STORED PROCEDURES.

Creating Temporary tables or passing array [message #393491 is a reply to message #393486] Mon, 23 March 2009 01:43 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
My question is whether creating temporary tables or passing arrays...which would be better....
I got a link on this.....
http://www.zdnetasia.com/techguide/database-management/0,3800010795,39233418,00.htm

Please suggest me which would be better in terms of performance when there will be million of records to be processed.

Mahi

[Updated on: Mon, 23 March 2009 01:45]

Report message to a moderator

Re: Creating Temporary tables on fly [message #393494 is a reply to message #393466] Mon, 23 March 2009 01:47 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Please suggest me which would be better in terms of performance when there will be million of records to be processed.


Benchmark the alternatives & use the best.
Re: Creating Temporary tables on fly [message #393500 is a reply to message #393494] Mon, 23 March 2009 02:15 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Java stored procedures are the object that are stored in Oracle database and can be used between any applications.

In your case, creating a JSP (Java Stored Procedure) in oracle database can let you to pass you array to oracle procedure directly in the database to implement business logic and return the output as Array. Which your JAVA program can easily read.

The documentation is given for the same.

Java Stored Procedure

Thanks
Trivendra
Re: Creating Temporary tables on fly [message #393508 is a reply to message #393494] Mon, 23 March 2009 02:36 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks for looking into my posts BlackSwan, but I didn't got the answer still.
I was looking for an answer as which option would be better as a suggestion and possibly with some clarification as why.

Please help me if you have such experience of using temp tables over arrays when number of records are quite high as the link I gave says that in case of many records, temp tables work better.

Quote:
The biggest drawback of using arrays is that it rapidly fills up the SQL buffer with raw data and generates extra network traffic by passing arguments and SQL syntax for each value.

Another method is to use a global temporary table to pass the array of values. In this method, you can use a Recordset object to post the data into the temporary table. (The anonymous block used to construct the PL/SQL table from the temporary table has a fixed size.)


Please throw some light on this....

[Updated on: Mon, 23 March 2009 03:06] by Moderator

Report message to a moderator

Previous Topic: Can foreign key take role of primary key?
Next Topic: ora-01001 invalid cursor
Goto Forum:
  


Current Time: Sat Dec 03 18:00:35 CST 2016

Total time taken to generate the page: 0.09632 seconds