PDA

View Full Version : ORA-01000: maximum open cursors exceeded



shassan2
04-18-2006, 12:18 AM
Attachment: ORA-01000.JPG (http://forums.pentaho.org/archived_att/kettle/ORA-01000.JPG) I have downloaded the latest kettle files and i have still this error.

The transformation is shown in the picture attached.


I extract data from SQL server 2000 Db and i insert each row in an excel file (the next step is to insert into table).



For that purprose, i use 'Execute SQl statements' and i select 'Execute fo each row?'.



Can anyone help me?



Thanks



Samatar

MattCasters
04-18-2006, 12:56 AM
Hi Samatar,

Thanks for clearing things up although this is probably one of the weirdest transformations I have ever seen ;-) I guess you're doing some custom stuff.

I'll do some tests with the SQL step and get back to you.

Thanks for the feedback!

Matt

shassan2
04-18-2006, 01:34 AM
In theory, It's simple (and clear :-) )
I just want to extract data from one Db (SQL server 2000) and insert into another Db (Oracle 10) with New key like : 1000 + i where i is incremented (that's why i use the very useful 'add sequence' step).

The step 'Execute SQL script' will allow the insert (fo each row).

But for testing, i use it with select..

select
? +? ID,
'?' COM_CODE,
'?' CC1,
'?' PER_CODE,
'?' PAIEMENT_MEAN,
'?' SERVICE,
'?' LABEL,
to_date(to_char(sysdate,'dd/mm/yyyy') ||' 08:00:00','dd/mm/yyyy hh24:mi:ss') DATE_INSERT
from dual;

It works good until reaching about 300 rows..


Samatar

Thanks a lot

MattCasters
04-18-2006, 01:49 AM
OK, this is completely not making sense to me.
So you use the SQL scripting step with a SELECT statement just to add 2 numbers and calculate a date?

How about just adding a calculator step: A + B will give you the result.

For the sysdate, you can use a small piece of javascript.

But even if DO like to keep the solution, a Table Input step makes much more sense in this case.

HTH,
Matt

shassan2
04-18-2006, 03:14 AM
Ok,

1-First the 'table input' step extract data fom SQL server 2000 Db :

Customer No | Analytical code | travelle No | ....

2- I add a key to rows (with increment number . from 1 to 999 for ex)

Key | Customer No | Analytical code | travelle No | ....

and i also add some info (using lookup fonctions) from Oracle Db

3- I insert the result into Db

------

The step "Execute SQl" allow me to do that step 2 & 3) , but it raise oracle SQL (pb of cursor).

I think i can use calculator step and output table step. But i will have more steps with more processing time.

I am trying your advices..


Regards

MattCasters
04-18-2006, 03:16 AM
>I think i can use calculator step and output table step.
> But i will have more steps with more processing time.

Of-course not! Round-trips to the database are always expensive performance wise.

Matt

shassan2
04-18-2006, 05:03 AM
Attachment: Sans titre.JPG (http://forums.pentaho.org/archived_att/kettle/Sans titre.JPG) Ok,
It works now (see attached picture).

shassan2
04-18-2006, 05:04 AM
Attachment: Sans titre2.JPG (http://forums.pentaho.org/archived_att/kettle/Sans titre2.JPG) But i am wondering why i have an oracle cursor error for the following trans (plase see attached file):

1-Extract data from SQL server 2000 Db (Step "Table input")

2- Execute SQL script

Regrds

MattCasters
04-18-2006, 05:18 AM
Wel, I can safely answer that it is because you're running the SQL script on Oracle.

It's completely useless to run select statements in an SQL step, but in case you do, it causes a problem. I'll further investigate this.

Give me a break OK?

shassan2
04-18-2006, 07:12 AM
Take your time man

:-)