Re: [cx-oracle-users] . iterating using fetchmany

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

note that TO_CLOB (and I think also TO_CHAR) uses the temporary tablespace
to create the strings.
If that tablespace is not big enough, or getting slow when growing, that
can cause issues.
So during the query take a look at the size of the temporary tablespace for
the connected user.

2014-11-26 16:21 GMT+01:00 Rodman, Daniel <dr...@bu...>:

> Thanks for the responses...
>
> My code appears to also hang on fetchall() and when I try iterating on the
> cursor, as was suggested by Anthony:
>     >> for row in cursor_metadata:
>     >>     print row
>
> After some tinkering, I noticed that I can use fetchmany() to iterate
> through data sets from other tables without any issue. It's just this
> particular table that's giving me problems.
>
> Could unclosed cursors be an issue?
>
>
> -----Original Message-----
> From: cx-...@li... [mailto:
> cx-...@li...]
> Sent: Tuesday, November 25, 2014 10:50 PM
> To: cx-...@li...
> Subject: cx-oracle-users Digest, Vol 97, Issue 1
>
> Send cx-oracle-users mailing list submissions to
>         cx-...@li...
>
> To subscribe or unsubscribe via the World Wide Web, visit
>         https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> or, via email, send a message with subject or body 'help' to
>         cx-...@li...
>
> You can reach the person managing the list at
>         cx-...@li...
>
> When replying, please edit your Subject line so it is more specific than
> "Re: Contents of cx-oracle-users digest..."
>
>
> Today's Topics:
>
>    1. iterating using fetchmany (Rodman, Daniel)
>    2. Re: iterating using fetchmany (Fawcett, David (MNIT))
>    3. Re: iterating using fetchmany (Anthony Tuininga)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Tue, 25 Nov 2014 21:22:58 +0000
> From: "Rodman, Daniel" <dr...@bu...>
> Subject: [cx-oracle-users] iterating using fetchmany
> To: "cx-...@li..."
>         <cx-...@li...>
> Message-ID:
>         <
> A4A...@mb...>
> Content-Type: text/plain; charset="us-ascii"
>
> I'm working with python and oracle using the cx_Oracle module. I'm having
> issues getting fetchmany to iterate over my result set correctly. The table
> in the query, i2test, has over 20,000 rows, however the while loop hangs
> when it gets to about 1,800 rows. If I remove the order by clause... the
> loop hangs at about 12,000. Any advice?
>
> cursor_metadata = cx_Oracle.Cursor(i_connection)
>
> query_select = """SELECT name, code, cname, TO_CHAR(updatedate),
> TO_CHAR(downloaddate), TO_CHAR(importdate)
>                                       FROM imetadata.i2test
>                                       WHERE code like '%DIN%' AND
>                                           cd = 'N'
>                                       ORDER BY code"""
>
> cursor_metadata.execute(query_select)
>
> count = 0
> results = cursor_metadata.fetchmany(100) while results:
>     print count
>     count += 1
>     results = cursor_metadata.fetchmany(100)
>
> I realize this is probably something simple... thanks in advance.
>
>
> Dan
> -------------- next part --------------
> An HTML attachment was scrubbed...
>
> ------------------------------
>
> Message: 2
> Date: Tue, 25 Nov 2014 21:50:24 +0000
> From: "Fawcett, David (MNIT)" <Dav...@st...>
> Subject: Re: [cx-oracle-users] iterating using fetchmany
> To: "cx-...@li..."
>         <cx-...@li...>
> Message-ID:
>         <
> D64...@05...
> >
>
> Content-Type: text/plain; charset="us-ascii"
>
> I don't see an obvious issue with your code, but you may just want to try
> using .fetchall()  Depending on your machine, 20,000 rows shouldn't be a
> problem.
>
> Once you have all of the data, you can then iterate through it and do what
> you want with it.
>
> data = cursor_metadata.fetchall()
>
> for row in data:
>     print row
>
> From: Rodman, Daniel [mailto:dr...@bu...]
> Sent: Tuesday, November 25, 2014 3:23 PM
> To: cx-...@li...
> Subject: [cx-oracle-users] iterating using fetchmany
>
> I'm working with python and oracle using the cx_Oracle module. I'm having
> issues getting fetchmany to iterate over my result set correctly. The table
> in the query, i2test, has over 20,000 rows, however the while loop hangs
> when it gets to about 1,800 rows. If I remove the order by clause... the
> loop hangs at about 12,000. Any advice?
>
> cursor_metadata = cx_Oracle.Cursor(i_connection)
>
> query_select = """SELECT name, code, cname, TO_CHAR(updatedate),
> TO_CHAR(downloaddate), TO_CHAR(importdate)
>                                       FROM imetadata.i2test
>                                       WHERE code like '%DIN%' AND
>                                           cd = 'N'
>                                       ORDER BY code"""
>
> cursor_metadata.execute(query_select)
>
> count = 0
> results = cursor_metadata.fetchmany(100) while results:
>     print count
>     count += 1
>     results = cursor_metadata.fetchmany(100)
>
> I realize this is probably something simple... thanks in advance.
>
>
> Dan
> -------------- next part --------------
> An HTML attachment was scrubbed...
>
> ------------------------------
>
> Message: 3
> Date: Tue, 25 Nov 2014 20:49:30 -0700
> From: Anthony Tuininga <ant...@gm...>
> Subject: Re: [cx-oracle-users] iterating using fetchmany
> To: cx-...@li...
> Message-ID:
>         <
> CAE...@ma...>
> Content-Type: text/plain; charset="utf-8"
>
> You can also use this code:
>
> for row in cursor_metadata:
>     print row
>
> Internally this does the equivalent of fetchmany() anyway. You can
> determine how big of a buffer you want to use by using cursor.arraysize.
>
> Anthony
>
> On Tue, Nov 25, 2014 at 2:50 PM, Fawcett, David (MNIT) <
> Dav...@st...> wrote:
>
> >  I don?t see an obvious issue with your code, but you may just want to
> > try using .fetchall()  Depending on your machine, 20,000 rows
> > shouldn?t be a problem.
> >
> >
> >
> > Once you have all of the data, you can then iterate through it and do
> > what you want with it.
> >
> >
> >
> > data = cursor_metadata.fetchall()
> >
> >
> >
> > for row in data:
> >
> >     print row
> >
> >
> >
> > *From:* Rodman, Daniel [mailto:dr...@bu...]
> > *Sent:* Tuesday, November 25, 2014 3:23 PM
> > *To:* cx-...@li...
> > *Subject:* [cx-oracle-users] iterating using fetchmany
> >
> >
> >
> > I'm working with python and oracle using the cx_Oracle module. I'm
> > having issues getting fetchmany to iterate over my result set
> > correctly. The table in the query, i2test, has over 20,000 rows,
> > however the while loop hangs when it gets to about 1,800 rows. If I
> > remove the order by clause... the loop hangs at about 12,000. Any advice?
> >
> >
> >
> > cursor_metadata = cx_Oracle.Cursor(i_connection)
> >
> >
> >
> > query_select = """SELECT name, code, cname, TO_CHAR(updatedate),
> > TO_CHAR(downloaddate), TO_CHAR(importdate)
> >
> >                                       FROM imetadata.i2test
> >
> >                                       WHERE code like '%DIN%' AND
> >
> >                                           cd = 'N'
> >
> >                                       ORDER BY code"""
> >
> >
> >
> > cursor_metadata.execute(query_select)
> >
> >
> >
> > count = 0
> >
> > results = cursor_metadata.fetchmany(100)
> >
> > while results:
> >
> >     print count
> >
> >     count += 1
> >
> >     results = cursor_metadata.fetchmany(100)
> >
> >
> >
> > I realize this is probably something simple... thanks in advance.
> >
> >
> >
> >
> >
> > Dan
> >
> >
> > ----------------------------------------------------------------------
> > -------- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT
> > Server from Actuate! Instantly Supercharge Your Business Reports and
> > Dashboards with Interactivity, Sharing, Native Excel Exports, App
> > Integration & more Get technology previously reserved for
> > billion-dollar corporations, FREE
> >
> > http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.
> > clktrk _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> -------------- next part --------------
> An HTML attachment was scrubbed...
>
> ------------------------------
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server from
> Actuate! Instantly Supercharge Your Business Reports and Dashboards with
> Interactivity, Sharing, Native Excel Exports, App Integration & more Get
> technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
>
> ------------------------------
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> End of cx-oracle-users Digest, Vol 97, Issue 1
> **********************************************
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
>
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>