Template::Plugin::DBI, Oracle and cursors

holli has asked for the wisdom of the Perl Monks concerning the following question:

Monks, I can't say how frustrated I am. I spent nearly the whole day to get Oracle running, and migrating a table from Access to said database, because I ran into a "cannot open more tables" error in my application that uses the Template-Toolkit and it's DBI plugin.

I have a template that basically looks like

[%
  USE oracleDB = DBI
  (
    "dbi:Oracle:SOMEDB", "someuser", "somepassword"
  )
-%]

<vdx_data>

[%- FOREACH kasse = oracleDB.query( 'SELECT DISTINCT foo, bar FROM myt
+able ORDER BY foo, bar' ) %]
  
  <vknr V="[% kasse.FOO %]">
      <zp_kv V="[% kasse.BAR %]"/>
  </vknr>
            
  <vorgang_liste>
  [%- FOREACH vorgang = oracleDB.query( "SELECT * FROM mytable WHERE F
+OO = $kasse.FOO AND BAR = $kasse.BAR" ) %]

    <vorgang>
        <vdx_abrechnungsbereich V="[% vorgang.ABC %]"/>
        <gkv_konto V="[% vorgang.DEF %]"/>
    </vorgang>

  [%- END %]
  </vorgang_liste>

[%- END %]
</vdx_data>
[download]

As you can see it first selects and groups some key fields from the table, outputs them and then makes a subquery for those keys and outputs more details for them in a inner loop. This runs smooth and very fast until Oracle reaches some kind of inner limit and chokes with an error:

DBI error - DBI prepare failed: ORA-01000: maximum open cursors exceed
+ed (DBD ERROR: OCIStmtExecute/Describe)
[download]

(The actual error message in english may differ. I translated it from german.)

So how can I close/reuse a cursor? Is there another way to achieve this?

P.S. This example is simplified. In reality there are more than one inner loop and they are nested.

holli, /regexed monk/

Comment on Template::Plugin::DBI, Oracle and cursors Select or Download Code

Replies are listed 'Best First'.
Re: Template::Plugin::DBI, Oracle and cursors by Anonymous Monk on Aug 30, 2005 at 12:36 UTC
Maybe one of the following will help: Closing Oracle cursors DBI - cursors left open in Oracle Oracle Cursors Exceeded Oracle DBI & Reference Cursors DBD::Oracle - Cursor in Select Oracle DBD cursors	[reply]
Re^2: Template::Plugin::DBI, Oracle and cursors by holli (Abbot) on Aug 30, 2005 at 12:51 UTC
Thank you. As advised in Oracle Cursors Exceeded, I changed my code to use a prepared query and placeholders. I changed my code to: `[% query = oracleDB.prepare('"SELECT * FROM mytable WHERE FOO = ? AND +BAR = ?"') %] #... [% FOREACH vorgang = query.execute(kasse.FOO, kasse.BAR) %]` [download] But then I get `undef error - Bad hash at C:/Perl/site/lib/Template/Plugin/DBI.pm line + 366.` [download] :( As you may have spotted, there is a stupid copy & paste typo in the code above (2 quote marks). This is corrected now and the program runs like a charm. Thanks again for the hint. :)) The correct code: `[% query = oracleDB.prepare('SELECT * FROM mytable WHERE FOO = ? AND B +AR = ?') %] #... [% FOREACH vorgang = query.execute(kasse.FOO, kasse.BAR) %]` [download] holli, /regexed monk/	[reply] [d/l] [select]
Re: Template::Plugin::DBI, Oracle and cursors by tbone1 (Monsignor) on Aug 30, 2005 at 12:42 UTC
You can define a cursor, then in a loop: WHILE SOME_CONDITION LOOP OPEN YOUR_CUR(IN_VALUE) . . . CLOSE YOUR_CUR; END LOOP; We do this all the time in our PL/SQL code. -- tbone1, YAPS (Yet Another Perl Schlub) And remember, if he succeeds, so what. - Chick McGee	[reply]
Re^2: Template::Plugin::DBI, Oracle and cursors by holli (Abbot) on Aug 30, 2005 at 12:53 UTC
It seems I am not able to grok what that has to do with my problem. I am not using PL/SQL nor is this possible with the Template Toolkit. holli, /regexed monk/	[reply] [d/l]
Re^3: Template::Plugin::DBI, Oracle and cursors by hakkr (Chaplain) on Aug 30, 2005 at 14:24 UTC
Every time you do a query Oracle internally uses a cursor to retrieve your data. If you open lots of statement handles you will get a cursor for each one. It looks like you are doing the query inside a loop so it is opening a new cursor each time around for each query. Generally to avoid this you should just prepare your query once outside the loop and close your statement handles after use. If you don't close your statement handle you will end up with a lot of open cursors hanging around Of course if you have sys access you can also just raise the open cursor limit if you have the memory;).	[reply]
Re: Template::Plugin::DBI, Oracle and cursors by mvandenb (Beadle) on Aug 30, 2005 at 19:12 UTC
First thing to check is the MAX_OPEN_CURSORS parameter in your init<SID>.ora (the database configuration file). If you don't have this parameter, Oracle is using a default, which is way too low for most serious applications. Set it to a value of 10000 or so, and restart your database.	[reply]
Re^2: Template::Plugin::DBI, Oracle and cursors by holli (Abbot) on Aug 30, 2005 at 22:04 UTC
Thanks for that valuable info (no, that was no irony). I have already solved my problem using prepared statements, but if I run into that error again despite of this, your info will prove useful. I'm an Oracle n00b. holli, /regexed monk/	[reply]
Re: Template::Plugin::DBI, Oracle and cursors by Anonymous Monk on Aug 30, 2005 at 14:31 UTC
I sometimes select and group into a temp table and then select, grouping again if nexessary) against the temp table. You can eliminate the inner loop often using this technique and it is usually more efficient. I am using the template toolkit for various tasks as well and have really found I have to mix it up. I have produced a lot of page from XML by preprocessing the db data and generating XML files that are actually used by the template to make things faster though the data is not actually "live" anymore.	[reply]