| Author |
Message
|
| nmaddisetti |
 Posted: Mon Nov 17, 2008 9:24 am Post subject: About Bulk Insert ( Urgent) |
 |
|
Centurion
Joined: 06 Oct 2004
Posts: 145
|
Hi All,
I want to call a DB store procedure with the below kind of data(see My ESQL code below)
Is there a way that I can bind an array from MB ESQL to Oracle SQL data type (VARCHAR array) .
We tried below code and getting following error:
Text:CHARACTER:A non scalar parameter passed to Stored Procedure
My Oracle Data type and Procedure are :
---------------------------------------------
SQL> create or replace type STR_ARRAY as table of VARCHAR2(500);
2 /
Type created.
SQL> create or replace
2 procedure give_me_an_array( p_array in str_array )
3 as
4 begin
5 for i in 1 .. p_array.count
6 loop
7 dbms_output.put_line( p_array(i) );
8 end loop;
9 end;
10 /
Procedure created.
SQL>
My ESQL code is:
---------------------
SET Environment.Variables.mySQLStmts[1] = 'INSERT INTO table1(my_id, my_name) VALUES(''1'', ''Name1'')';
SET Environment.Variables.mySQLStmts[2] = 'INSERT INTO table1(my_id, my_name) VALUES(''2'', ''Name2'')';
SET Environment.Variables.mySQLStmts[3] = 'INSERT INTO table1(my_id, my_name) VALUES(''3'', ''Name3'')';
SET Environment.Variables.mySQLStmts[4] = 'INSERT INTO table1(my_id, my_name) VALUES(''4'', ''Name4'')';
SET Environment.Variables.mySQLStmts[5] = 'INSERT INTO table1(my_id, my_name) VALUES(''5'', ''Name5'')';
CALL myProcedure(Environment.Variables.mySQLStmts[]);
Thanks & Regards,
Venkat. |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Mon Nov 17, 2008 9:50 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi nmaddisetti,
You cannot send an array.
Why do you not put the procedure call in a loop, and call it recursively with new values every time (using a reference)...
Regards. |
|
| Back to top |
|
|
| nmaddisetti |
| Posted: Mon Nov 17, 2008 10:31 am Post subject: |
|
|
Centurion
Joined: 06 Oct 2004
Posts: 145
|
Hi elvis_gn,
We are planning to avoid database trips to the possible extent so If we can send an array then we are planning to do bulk insert in the Database storeprocess.
currently our application is taking 2 hours to process 29 MB file( file parsing and database inserts --- inserts are around 100 thousand inserts for this 29 MB file. it is XML file)
Any other better Idea to reduce the no of database calls.
Thanks & Regards,
Venkat. |
|
| Back to top |
|
|
| sridhsri |
| Posted: Mon Nov 17, 2008 12:59 pm Post subject: |
|
|
Master
Joined: 19 Jun 2008
Posts: 297
|
| I don't think you can. You could try using a PASSTHRU and create a string with all the insert statements. Perhaps broker would an array of results - I would be pleasantly surprised if it did. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Nov 17, 2008 8:17 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi nmaddisetti,
Try to look at MQ communication with databases...it exists for Db2 for sure, don't know about the others...You could do direct dispatch of xmls to the database, and databases support xmls these days.
Regards. |
|
| Back to top |
|
|
| sandeepdaggupati |
| Posted: Fri Nov 28, 2008 10:43 am Post subject: |
|
|
Novice
Joined: 29 Aug 2008
Posts: 11
|
| Did you find the solution? |
|
| Back to top |
|
|
| mymq |
| Posted: Sat Nov 29, 2008 11:54 am Post subject: |
|
|
Centurion
Joined: 01 Mar 2007
Posts: 101
Location: US-Greenwille
|
what is your Oracle DB version?
_________________
--SRK-- |
|
| Back to top |
|
|
| JULLRICH |
| Posted: Tue Dec 29, 2009 3:49 am Post subject: |
|
|
Apprentice
Joined: 11 Aug 2005
Posts: 42
Location: München
|
| Is there no solution for this? |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Dec 29, 2009 5:52 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| JULLRICH wrote: |
| Is there no solution for this? |
Aside from the 2 methods elvis_gn outlined a year ago, no.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| nmaddisetti |
| Posted: Tue Dec 29, 2009 8:01 am Post subject: |
|
|
Centurion
Joined: 06 Oct 2004
Posts: 145
|
Hi JULRICH,
I saw your private message and thought of posting reply here....
| Quote: |
Hi,
you posted at 17.11.2008 the follwoing. You found any solution????
I do have nearly the same situation - Im running out of open cursors (in Oracle) and admin does not wants to increase this.
Regards
Jens
|
We are using bulk insert and we do faced the same problem of running out of open cursuors but our DB team increased the no of threads.
Thanks & Regards,
Venkat. |
|
| Back to top |
|
|
| JULLRICH |
| Posted: Tue Dec 29, 2009 10:10 am Post subject: |
|
|
Apprentice
Joined: 11 Aug 2005
Posts: 42
Location: München
|
Hi mates,
I don't believe, that increasing the number of threads is helping to solve the issue with the max open cursors!
And testing this will say the same.
Well, I generated a global transaction with many small transactions. Each small transaction will do some part of the DBProcessing. (Use status flags  )
Each small transaction will be rolledback, if there is an error somewhere during the Global transaction.
For sure: such rollbacks must been processed manually and carefully. 
Regards
Jens |
|
| Back to top |
|
|
| nmaddisetti |
| Posted: Tue Dec 29, 2009 11:00 am Post subject: |
|
|
Centurion
Joined: 06 Oct 2004
Posts: 145
|
Hi JULLRICH,
I spoke to DB team to give you more details.
It looks like it is not that we increased no of connections.
It is something to do with open_cursors parameter and the problem we faced is :
ORA-01000: maximum open cursors exceeded...
And actual reason for this is one query is running in loop and after avoiding loop it seems like we did not see that open cursors exceed problem.
Thanks & Regards,
Venkat. |
|
| Back to top |
|
|
| JULLRICH |
| Posted: Tue Dec 29, 2009 11:35 am Post subject: |
|
|
Apprentice
Joined: 11 Aug 2005
Posts: 42
Location: München
|
Hi,
that's true ... with increasing the parameter max_open_cursors you can handle this issue - but it is not solved for every case.
I had a discussion with an experienced OracleSpecialist in the mean time.
He told me, that the solution with the GlobalTransaction is the best for my problem. Reason: the max open cursors can be reached always in my case. Even if the value of this parameter is very high.
Well, luckily a rollback is not difficult for my small transactions. I just need to use one flag for identification.
Regards
Jens |
|
| Back to top |
|
|
|
|
