Creating Temporary tables on fly [message #393466] |
Mon, 23 March 2009 00:05  |
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 #393471 is a reply to message #393468] |
Mon, 23 March 2009 00:18   |
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 #393478 is a reply to message #393473] |
Mon, 23 March 2009 00:46   |
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 #393483 is a reply to message #393479] |
Mon, 23 March 2009 01:05   |
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 #393500 is a reply to message #393494] |
Mon, 23 March 2009 02:15   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
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  |
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
|
|
|