bindvariables with oratcl 4.4

HE June 7 2009: I have a problem with bindvariables and oratcl 4.4. The original task is to analyse a huge textfile, search the data in an oracle xe database and add the data if the data is missing in the database. The problem is that the tcl script grows and grows.

I analysed the problem and the following reproduces the problem:

I use an oracle XE database with its standardschema hr. The machine on which it runs is a dual core 2 with windows xp sp2. Database and script are running on the same machine. I add the following table and add one row to the table:

 CREATE TABLE  TAG (
        id NUMBER, 
        k VARCHAR2(1000), 
        v VARCHAR2(1000), 
        typ VARCHAR2(20)
 );
 INSERT INTO tag (id, k, v, typ) VALUES (0, 'created_by', 'HE', 'node');

The following is the script which reproduce the huge memory use:

 package require Oratcl 4.4
 set db(db)   xe
 set db(user) hr
 set db(pass) hr
 set db(logon) [oralogon $db(user)/$db(pass)@$db(db)]

 set db(tag_select)          [oraopen $db(logon)]
 oraparse $db(tag_select) {SELECT id FROM tag WHERE k = :k AND v = :v AND typ = :typ}

 set tmp(k)   created_by
 set tmp(v)   JOSM
 set tmp(typ) node

 for {set n 0} {$n < 1000000} {incr n} {
        orabindexec $db(tag_select) :k $tmp(k) :v $tmp(v) :typ $tmp(typ)
 }

It looks like binding new values to the bindvariables of the same statementhandle consumes the memory. Is this known? A bug? Or have I missed something?

LV While this question is certainly within the scope of the wiki, you might consider asking the question on the oratcl sf.net site as well. While there are certainly readers of this wiki who use Oratcl, the maintainer does respond to bug reports, questions, etc. and so that might be a useful additional direction to consider.

HE June 18 2009: Thanks. On sf.net I found an equivalent support request. It's fixed there but till this day there is no new binary for w32 available. Perhaps I have to look if I can build it by myself.

thelfter - 2009-06-29 09:52:17

Yes this memory leak has been fixed in the source code. Sorry I am no longer able to compile the source code on windows. The last binary build was performed by ActiveState. I will ask them to generate some new binaries. (Oratcl 4.5 will be out soon.).

Here is a simple Tcl only work around.

# do not use orabindexec.
 for {set n 0} {$n < 1000000} {incr n} {
        orabind $db(tag_select) :k $tmp(k) :v $tmp(v) :typ $tmp(typ)
        oraexec $db(tag_select)
 }

the reason is that orabindexec calls orabind and oraexec in 'C' and the old code leaked memory. you can do the same thing in Tcl.