cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

Re: [cx-oracle-users] cx_Oracle - Django ORM - Reference Count Increase

[Amaury F. d'A. <ama...@gm...>]

2014-12-27 22:23 GMT+01:00 Anurag Chourasia <anu...@gm...>:
>
> I have a Long Running Python Process that uses Django ORM against Oracle
> database.
>
> The size of the process keeps on increasing steadily.
>
> I was profiling this process using mem_top and i find that the reference
> count of one particular data type <list> increases continuously with
> iterations.
>
> Datatype is <type 'list'> [{u'time': u'0.004', u'sql': u'QUERY = u\'SELECT
> "RANGE_STATUS"."I
>
> References increased from 534 to 53295
>

Hi,

This list looks very much like this one:
https://docs.djangoproject.com/en/1.7/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running

I don't know why there should be many references to it, but it's definitely
a Django thing.

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] cx_Oracle - Django ORM - Reference Count Increase

[Anurag C. <anu...@gm...>]

All,

I have a Long Running Python Process that uses Django ORM against Oracle
database.

The size of the process keeps on increasing steadily.

I was profiling this process using mem_top and i find that the reference
count of one particular data type <list> increases continuously with
iterations.

Datatype is <type 'list'> [{u'time': u'0.004', u'sql': u'QUERY = u\'SELECT
"RANGE_STATUS"."I

References increased from 534 to 53295

This list seems to have almost all the queries that were executed using the
ORM

Does this mean that there is a memory leak in the cx_Oracle module or
somewhere in Django ORM?

Here is the memtop result between two iterations 10 minutes apart.

*WARNING : 27/12/2014 05:45:46 PM : *

> refs:
> 9500 <type 'dict'> {'TAPE_DRIVE_FORMAT': -1610612736, 'SLE_ERROR': 1,
> 'IMAGE_REL_I386_SEC
> 8410 <type 'list'> [('200', '343045', 1321), ('200', '343046', 1322),
> ('200', '343047', 1
> 1578 <type 'dict'> {'logging.atexit': None, 'django.core.files.errno':
> None, 'django.test
> 1567 <type 'list'> ['mem_top', 'mem_top', 'datetime', 'datetime', 'sys',
> 'string', 'os',
> 688 <type 'dict'> {'FILE_SYSTEM_ATTR': 2, 'GetDriveTypeW': <built-in
> function GetDriveTy
> 688 <type 'dict'> {'FILE_SYSTEM_ATTR': 2, 'GetDiskFreeSpaceEx': <built-in
> function GetDi
> 534 <type 'list'> [{u'time': u'0.004', u'sql': u'QUERY = u\'SELECT
> "RANGE_STATUS"."I
> 510 <type 'dict'> {'GetDiskFreeSpaceEx': <built-in function
> GetDiskFreeSpaceEx>, 'SetThr
> 510 <type 'dict'> {'GetDiskFreeSpaceEx': <built-in function
> GetDiskFreeSpaceEx>, 'SetThr
> 370 <type 'list'> [<weakref at 01CF5030; to 'type' at 1E228030 (type)>,
> <weakref at 01CF
> types:
> 8625 <type 'function'>
> 3778 <type 'tuple'>
> 3128 <type 'dict'>
> 1672 <type 'list'>
> 1661 <type 'cell'>
> 1440 <type 'weakref'>
> 1351 <type 'wrapper_descriptor'>
> 1103 <type 'builtin_function_or_method'>
> 888 <type 'type'>
> 734 <type 'getset_descriptor'>



*WARNING : 27/12/2014 05:54:37 PM : *

> refs:
> 53295 <type 'list'> [{u'time': u'0.004', u'sql': u'QUERY = u\'SELECT
> "RANGE_STATUS"."I
> 9500 <type 'dict'> {'TAPE_DRIVE_FORMAT': -1610612736, 'SLE_ERROR': 1,
> 'IMAGE_REL_I386_SEC
> 8410 <type 'list'> [('200', '343045', 1321), ('200', '343046', 1322),
> ('200', '343047', 1
> 1578 <type 'dict'> {'logging.atexit': None, 'django.core.files.errno':
> None, 'django.test
> 1567 <type 'list'> ['mem_top', 'mem_top', 'datetime', 'datetime', 'sys',
> 'string', 'os',
> 749 <type 'list'> ['A. HISTORY OF THE SOFTWARE',
> '==========================', '', 'Pyth
> 688 <type 'dict'> {'FILE_SYSTEM_ATTR': 2, 'GetDriveTypeW': <built-in
> function GetDriveTy
> 688 <type 'dict'> {'FILE_SYSTEM_ATTR': 2, 'GetDiskFreeSpaceEx': <built-in
> function GetDi
> 510 <type 'dict'> {'GetDiskFreeSpaceEx': <built-in function
> GetDiskFreeSpaceEx>, 'SetThr
> 510 <type 'dict'> {'GetDiskFreeSpaceEx': <built-in function
> GetDiskFreeSpaceEx>, 'SetThr
> types:
> 8625 <type 'function'>
> 3778 <type 'tuple'>
> 3130 <type 'dict'>
> 1675 <type 'list'>
> 1661 <type 'cell'>
> 1440 <type 'weakref'>
> 1351 <type 'wrapper_descriptor'>
> 1103 <type 'builtin_function_or_method'>
> 888 <type 'type'>
> 734 <type 'getset_descriptor'>


Please guide.

Regards,
Guddu

Re: [cx-oracle-users] Error creating pluggable db on 12c

[Gaius H. <ga...@ga...>]

------------------------------------------------------------------------------
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=164703151&iu=/4140/ostg.clktrk

[cx-oracle-users] Error creating pluggable db on 12c

[Simon C. <sim...@gm...>]

I'm trying to create an Oracle 12c pluggable database with the following
code ...

ora_dsn = cx_Oracle.makedsn('192.168.1.1', 1521, service_name='cdb')
connection = cx_Oracle.connect('sys/oracle', dsn=ora_dsn,
                                   mode=cx_Oracle.SYSDBA)
cursor = connection.cursor()
cursor.execute("create pluggable database pdb admin user pdbadmin
identified by password;")
connection.close()

.... and got this error:

cursor.execute("create pluggable database pdb admin user pdbadmin
identified by password;")
cx_Oracle.DatabaseError: ORA-00922: missing or invalid option

I tested the "create pluggable database" statement in sqlplus and it
worked.  Is "create pluggable database" supposed to work on cx_Oracle
5.1.3?  If so, can anyone advise what I'm doing wrong?

Thanks!

Simon

Re: [cx-oracle-users] big varchars

[Vladimir R. <gre...@gm...>]

Could you please provide your code? What exact Oracle column type is used?
How many rows returns your request? What kind of issues do you have?

2014-12-01 20:23 GMT+03:00 Rodman, Daniel <dr...@bu...>:

>  I’m having issues iterating through an entire data set with big
> varchars, varchar(2000). I can use the fetchmany in a loop but it’ll just
> hang after a while. Any advice? (By the way… I iterate fine through data
> sets that don’t have such large data types.)
>
>
>
> Thanks,
>
> -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
>
>

[cx-oracle-users] big varchars

[Rodman, D. <dr...@bu...>]

I'm having issues iterating through an entire data set with big varchars, varchar(2000). I can use the fetchmany in a loop but it'll just hang after a while. Any advice? (By the way... I iterate fine through data sets that don't have such large data types.)

Thanks,
-Dan

Re: [cx-oracle-users] iterating using fetchmany (size)

[Rodman, D. <dr...@bu...>]

I think I've narrowed the issue to size. When I try to retrieve certain columns eg. varchar2(2000), I can't iterate through all the rows of the data set. How do I deal with larger row sizes?

-----Original Message-----
From: cx-...@li... [mailto:cx-...@li...] 
Sent: Wednesday, November 26, 2014 10:41 AM
To: cx-...@li...
Subject: cx-oracle-users Digest, Vol 97, Issue 2

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. Re: . iterating using fetchmany (Rodman, Daniel)
   2. Re: . iterating using fetchmany (Joram Agten)


----------------------------------------------------------------------

Message: 1
Date: Wed, 26 Nov 2014 15:21:56 +0000
From: "Rodman, Daniel" <dr...@bu...>
Subject: Re: [cx-oracle-users] . iterating using fetchmany
To: "cx-...@li..."
	<cx-...@li...>
Message-ID:
	<A4A...@mb...>
Content-Type: text/plain; charset="us-ascii"

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
**********************************************



------------------------------

Message: 2
Date: Wed, 26 Nov 2014 16:41:18 +0100
From: Joram Agten <jor...@gm...>
Subject: Re: [cx-oracle-users] . iterating using fetchmany
To: cx-...@li...
Message-ID:
	<CAA...@ma...>
Content-Type: text/plain; charset="utf-8"

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...ft
> .net
> >
>
> 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
>
-------------- 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 2
**********************************************

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

[Joram A. <jor...@gm...>]

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
>

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

[Rodman, D. <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
**********************************************

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

[Anthony T. <ant...@gm...>]

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
>
>

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

[Fawcett, D. (MNIT) <Dav...@st...>]

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

[cx-oracle-users] iterating using fetchmany

[Rodman, D. <dr...@bu...>]

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

Re: [cx-oracle-users] cx_Oracle cursor.var Example

[Solomon, S. <Sco...@sr...>]

Thanks a lot for your input, Doug.  That was very helpful.

-----------
Scott S.


-----Original Message-----
From: Doug Henderson [mailto:djn...@gm...] 
Sent: Thursday, October 23, 2014 6:18 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] cx_Oracle cursor.var Example

On 23 October 2014 14:31, Solomon, Scott \ wrote:
> Ok, getting closer.  Causes another error.  I think I still need to 
> convert variable 'line' to string somehow:
>
>
>   File "C:\Users\{redacted}\gensim\models\lsimodel.py", line 513, in 
> show_topics
>     if i < len(self.projection.s):
>

Your problem is more a python one than a cx_Oracle problem, but i will try to set you on the right path.

Open a python console, and follow along. I am using python3, python2 with "from __future__ import print_function" also works, or just translate the print function calls on the fly.

>>> result = [ ( 'proj 1 title' , ) , ( 'proj 2 title' , ) , ( 'proj 3 
>>> title' , ) , ] result
[('proj 1 title',),('proj 2 title',),('proj 3 title',),]

This is sort of what the result set of the cursor looks like, if you used a statement like

>>> result = cur.fetchall ( )

after executing your cursor. Here, you get back a list of rows, where each row is a tuple of column values. when you use the cursor in the for statement, you are getting back a generator that will give you the next row tuple on each iteration. The actual type of the column values may be cx_Oracle defined types that are automatically converted to regular python strings and number when you use them.

The result of a list comprehension is a generator object. It is almost the same as the list object, in practice. (Not PEP8, to make brackets and parenthesis easier to see.)

>>> print ( row for row in result )
<generator object <genexpr> at 0x0000000002319DC8>
>>> print ( [ row for row in result ] )
[('a',), ('b',), ('c',)]

Notice how I get the column values:
>>> print ( [ row [ 0 ] for row in result ] )
['a', 'b', 'c']
>>> print ( [ row [ 0 ] . upper ( ) for row in result ] )
['A', 'B', 'C']
>>> print ( row [ 0 ] . upper ( ) for row in result )
<generator object <genexpr> at 0x0000000002319D80>

So your corpora.Dictionary() constructor is getting passed a generator object. If it expects a list() type object it may do the wrong thing.
Just add the square brackets to pass a list object instead of the generator object. Likewise, if down in the the guts of that code, something is expecting a string and chokes on the actual type passed back from cx_Oracle, you can convert the value using something like:

>>> print ( [ str ( row [ 0 ] ) . upper ( ) for row in result ] )
['A', 'B', 'C']

Doug

--
Doug Henderson, Calgary, Alberta, Canada

------------------------------------------------------------------------------
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] cx_Oracle cursor.var Example

[Doug H. <djn...@gm...>]

On 23 October 2014 14:31, Solomon, Scott \ wrote:
> Ok, getting closer.  Causes another error.  I think I still need to convert
> variable 'line' to string somehow:
>
>
>   File "C:\Users\{redacted}\gensim\models\lsimodel.py", line 513, in
> show_topics
>     if i < len(self.projection.s):
>

Your problem is more a python one than a cx_Oracle problem, but i will
try to set you on the right path.

Open a python console, and follow along. I am using python3, python2
with "from __future__ import print_function" also works, or just
translate the print function calls on the fly.

>>> result = [ ( 'proj 1 title' , ) , ( 'proj 2 title' , ) , ( 'proj 3 title' , ) , ]
>>> result
[('proj 1 title',),('proj 2 title',),('proj 3 title',),]

This is sort of what the result set of the cursor looks like, if you
used a statement like

>>> result = cur.fetchall ( )

after executing your cursor. Here, you get back a list of rows, where
each row is a tuple of column values. when you use the cursor in the
for statement, you are getting back a generator that will give you the
next row tuple on each iteration. The actual type of the column values
may be cx_Oracle defined types that are automatically converted to
regular python strings and number when you use them.

The result of a list comprehension is a generator object. It is almost
the same as the list object, in practice. (Not PEP8, to make brackets
and parenthesis easier to see.)

>>> print ( row for row in result )
<generator object <genexpr> at 0x0000000002319DC8>
>>> print ( [ row for row in result ] )
[('a',), ('b',), ('c',)]

Notice how I get the column values:
>>> print ( [ row [ 0 ] for row in result ] )
['a', 'b', 'c']
>>> print ( [ row [ 0 ] . upper ( ) for row in result ] )
['A', 'B', 'C']
>>> print ( row [ 0 ] . upper ( ) for row in result )
<generator object <genexpr> at 0x0000000002319D80>

So your corpora.Dictionary() constructor is getting passed a generator
object. If it expects a list() type object it may do the wrong thing.
Just add the square brackets to pass a list object instead of the
generator object. Likewise, if down in the the guts of that code,
something is expecting a string and chokes on the actual type passed
back from cx_Oracle, you can convert the value using something like:

>>> print ( [ str ( row [ 0 ] ) . upper ( ) for row in result ] )
['A', 'B', 'C']

Doug

-- 
Doug Henderson, Calgary, Alberta, Canada

Re: [cx-oracle-users] cx_Oracle cursor.var Example

[Solomon, S. <Sco...@sr...>]

Ok, getting closer.  Causes another error.  I think I still need to convert variable 'line' to string somehow:

  File "C:\Users\{redacted}\gensim\models\lsimodel.py", line 513, in show_topics
    if i < len(self.projection.s):



TypeError: object of type 'NoneType' has no len()

----------
Scott S.

________________________________
From: Anthony Tuininga [ant...@gm...]
Sent: Thursday, October 23, 2014 3:57 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] cx_Oracle cursor.var Example

You need to use this

line.lower().split() for line, in cur

Note the comma following the line variable. cx_Oracle returns a tuple and you need to unpack that tuple. That should solve it for you. You don't need to build a variable generally.

On Thu, Oct 23, 2014 at 1:53 PM, Solomon, Scott <Sco...@sr...<mailto:Sco...@sr...>> wrote:

Anthony Tuininga, et al,



I am trying to run the following code (with DB connection information intentionally redacted:

import logging, gensim, bz2, cx_Oracle

logging.basicConfig(format='%(asctime)s : %(levelname)s : %(message)s', level=logging.INFO)

from gensim import corpora, models, similarities


dsnStr = cx_Oracle.makedsn("{redacted}", "{redacted}", "{redacted}")

con = cx_Oracle.connect(user="{redacted}", password="{redacted}", dsn=dsnStr)

cur = con.cursor()

cur.execute('select project_title from doj_collab_award_dim where project_title is not null and rownum < 101')


stoplist = set('for a of the and to in is'.split())


dictionarywiki = corpora.Dictionary(line.lower().split() for line in cur)

stop_ids = [dictionarywiki.token2id[stopword] for stopword in stoplist if stopword in dictionarywiki.token2id]

once_ids = [tokenid for tokenid, docfreq in dictionarywiki.dfs.iteritems() if docfreq == 1]

dictionarywiki.filter_tokens(stop_ids + once_ids)

dictionarywiki.compactify()

print(dictionarywiki)


print(dictionarywiki.token2id)

which returns the error: AttributeError: 'tuple' object has no attribute 'lower' in bolded, yellow highlighted line ('dictionarywiki...)

I am trying to convert the variable line to string using Cursor.var(dataType[, size, arraysize, inconverter, outconverter, typename]) from http://cx-oracle.readthedocs.org/en/latest/cursor.html



I tried:

import logging, gensim, bz2, cx_Oracle

logging.basicConfig(format='%(asctime)s : %(levelname)s : %(message)s', level=logging.INFO)

from gensim import corpora, models, similarities


dsnStr = cx_Oracle.makedsn("{redacted}", "{redacted}", "{redacted}")

con = cx_Oracle.connect(user="{redacted}", password="{redacted}", dsn=dsnStr)

cur = con.cursor()

cur.execute('select project_title from doj_collab_award_dim where project_title is not null and rownum < 101')


stoplist = set('for a of the and to in is'.split())


dictionarywiki = corpora.Dictionary(line.lower().split() for line.cursor.var(string) in cur)

stop_ids = [dictionarywiki.token2id[stopword] for stopword in stoplist if stopword in dictionarywiki.token2id]

once_ids = [tokenid for tokenid, docfreq in dictionarywiki.dfs.iteritems() if docfreq == 1]

dictionarywiki.filter_tokens(stop_ids + once_ids)

dictionarywiki.compactify()

print(dictionarywiki)


print(dictionarywiki.token2id)

I guess I am doing it wrong.  Can someone provide me an example of how to change my variable to a string?

Maybe is it this:
dictionarywiki = corpora.Dictionary(line.lower().split() for cursor.line(string) in cur)
?

----------
Scott S.

------------------------------------------------------------------------------

_______________________________________________
cx-oracle-users mailing list
cx-...@li...<mailto:cx-...@li...>
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] cx_Oracle cursor.var Example

[Anthony T. <ant...@gm...>]

You need to use this

line.lower().split() for line, in cur

Note the comma following the line variable. cx_Oracle returns a tuple and
you need to unpack that tuple. That should solve it for you. You don't need
to build a variable generally.

On Thu, Oct 23, 2014 at 1:53 PM, Solomon, Scott <Sco...@sr...>
wrote:

>  Anthony Tuininga, et al,
>
>
>
> I am trying to run the following code (with DB connection information
> intentionally redacted:
>
> import logging, gensim, bz2, cx_Oracle
>
> logging.basicConfig(format='%(asctime)s : %(levelname)s : %(message)s',
> level=logging.INFO)
>
> from gensim import corpora, models, similarities
>
>
>  dsnStr = cx_Oracle.makedsn("{redacted}", "{redacted}", "{redacted}")
>
> con = cx_Oracle.connect(user="{redacted}", password="{redacted}",
> dsn=dsnStr)
>
> cur = con.cursor()
>
> cur.execute('select project_title from doj_collab_award_dim where
> project_title is not null and rownum < 101')
>
>
>  stoplist = set('for a of the and to in is'.split())
>
>
>  *dictionarywiki = corpora.Dictionary(line.lower().split() for line in
> cur)*
>
> stop_ids = [dictionarywiki.token2id[stopword] for stopword in stoplist if
> stopword in dictionarywiki.token2id]
>
> once_ids = [tokenid for tokenid, docfreq in dictionarywiki.dfs.iteritems()
> if docfreq == 1]
>
> dictionarywiki.filter_tokens(stop_ids + once_ids)
>
> dictionarywiki.compactify()
>
> print(dictionarywiki)
>
>
>  print(dictionarywiki.token2id)
>
> which returns the error: AttributeError: 'tuple' object has no attribute
> 'lower' in bolded, yellow highlighted line ('dictionarywiki...)
>
> I am trying to convert the variable line to string using
> *Cursor.var(dataType*[, *size*, *arraysize*, *inconverter*, *outconverter*
> , *typename*]) from http://cx-oracle.readthedocs.org/en/latest/cursor.html
>
>
>
> I tried:
>
> import logging, gensim, bz2, cx_Oracle
>
> logging.basicConfig(format='%(asctime)s : %(levelname)s : %(message)s',
> level=logging.INFO)
>
> from gensim import corpora, models, similarities
>
>
>  dsnStr = cx_Oracle.makedsn("{redacted}", "{redacted}", "{redacted}")
>
> con = cx_Oracle.connect(user="{redacted}", password="{redacted}",
> dsn=dsnStr)
>
> cur = con.cursor()
>
> cur.execute('select project_title from doj_collab_award_dim where
> project_title is not null and rownum < 101')
>
>
>  stoplist = set('for a of the and to in is'.split())
>
>
>  *dictionarywiki = corpora.Dictionary(line.lower().split()
> for line.cursor.var(string) in cur)*
>
> stop_ids = [dictionarywiki.token2id[stopword] for stopword in stoplist if
> stopword in dictionarywiki.token2id]
>
> once_ids = [tokenid for tokenid, docfreq in dictionarywiki.dfs.iteritems()
> if docfreq == 1]
>
> dictionarywiki.filter_tokens(stop_ids + once_ids)
>
> dictionarywiki.compactify()
>
> print(dictionarywiki)
>
>
>  print(dictionarywiki.token2id)
>
> I guess I am doing it wrong.  Can someone provide me an example of how to
> change my variable to a string?
>
> Maybe is it this:
>
> *dictionarywiki = corpora.Dictionary(line.lower().split()
> for cursor.line(string) in cur)*
>
> ?
>
> ----------
> Scott S.
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] cx_Oracle cursor.var Example

[Solomon, S. <Sco...@sr...>]

Anthony Tuininga, et al,



I am trying to run the following code (with DB connection information intentionally redacted:

import logging, gensim, bz2, cx_Oracle

logging.basicConfig(format='%(asctime)s : %(levelname)s : %(message)s', level=logging.INFO)

from gensim import corpora, models, similarities


dsnStr = cx_Oracle.makedsn("{redacted}", "{redacted}", "{redacted}")

con = cx_Oracle.connect(user="{redacted}", password="{redacted}", dsn=dsnStr)

cur = con.cursor()

cur.execute('select project_title from doj_collab_award_dim where project_title is not null and rownum < 101')


stoplist = set('for a of the and to in is'.split())


dictionarywiki = corpora.Dictionary(line.lower().split() for line in cur)

stop_ids = [dictionarywiki.token2id[stopword] for stopword in stoplist if stopword in dictionarywiki.token2id]

once_ids = [tokenid for tokenid, docfreq in dictionarywiki.dfs.iteritems() if docfreq == 1]

dictionarywiki.filter_tokens(stop_ids + once_ids)

dictionarywiki.compactify()

print(dictionarywiki)


print(dictionarywiki.token2id)

which returns the error: AttributeError: 'tuple' object has no attribute 'lower' in bolded, yellow highlighted line ('dictionarywiki...)

I am trying to convert the variable line to string using Cursor.var(dataType[, size, arraysize, inconverter, outconverter, typename]) from http://cx-oracle.readthedocs.org/en/latest/cursor.html



I tried:

import logging, gensim, bz2, cx_Oracle

logging.basicConfig(format='%(asctime)s : %(levelname)s : %(message)s', level=logging.INFO)

from gensim import corpora, models, similarities


dsnStr = cx_Oracle.makedsn("{redacted}", "{redacted}", "{redacted}")

con = cx_Oracle.connect(user="{redacted}", password="{redacted}", dsn=dsnStr)

cur = con.cursor()

cur.execute('select project_title from doj_collab_award_dim where project_title is not null and rownum < 101')


stoplist = set('for a of the and to in is'.split())


dictionarywiki = corpora.Dictionary(line.lower().split() for line.cursor.var(string) in cur)

stop_ids = [dictionarywiki.token2id[stopword] for stopword in stoplist if stopword in dictionarywiki.token2id]

once_ids = [tokenid for tokenid, docfreq in dictionarywiki.dfs.iteritems() if docfreq == 1]

dictionarywiki.filter_tokens(stop_ids + once_ids)

dictionarywiki.compactify()

print(dictionarywiki)


print(dictionarywiki.token2id)

I guess I am doing it wrong.  Can someone provide me an example of how to change my variable to a string?

Maybe is it this:
dictionarywiki = corpora.Dictionary(line.lower().split() for cursor.line(string) in cur)
?

----------
Scott S.

[cx-oracle-users] oracle9i

[Mehdi <mes...@gm...>]

Hi
I have a seriuos problem. my python/pyramid app has to connect to an 
oracle9i database(i know that's pretty old).
Now i can't figure out how to do this with python 3.3 on windows. so far 
with oracle client 11g and prebuild cx_oracle 5.1.3.11g i'v got this error:
"cx_Oracle.InterfaceError: Unable to acquire Oracle environment handle".
I've already set PATH and ORACLE_HOME to oracle client 11g folder. but 
before installing python i'v installed oracle9i on my winxp sp3 machine.
Do i have to compile cx_oracle from the source? Can i connect to 
oracle9i server with oracle client 11g?
please help me.
Thanks.

Re: [cx-oracle-users] "expecting string, unicode or buffer object" on executemany

[Avinash P. N. <avi...@or...>]

Hello Riccardo, 


For the 26 nd column " Competenzascarti " different data types have been bound in the first and second iteration. 
In the first iteration value is ''50899.74" which is a string and second iteration value is 0.0 which is a number. 
If using executemany() the binds have to be of the same data type across iterations else inconsistent results may occur. 


Thus if you try to execute with cursor.execute() it will work correctly. Also if you remove the quotes and send as numbers it will run correctly. 


Best Regards, 
Avinash 

----- Original Message ----- 
From: ol...@ca... 
To: cx-...@li... 
Cc: avi...@or... 
Sent: Tuesday, 23 September, 2014 7:42:47 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi 
Subject: RE: [cx-oracle-users] "expecting string, unicode or buffer object" on executemany 





Hi, we are using: 

- python: 2.6.6 

- cx_Oracle: 5.1.2 



The problem seems to be caused by an incorrect definition of numeric fields: I try to insert a record with a numeric values but wrote as a string (surrounded by double quotes). 

If I use the instruction “execute” in a cycle I obtain an “invalid number” error from oracle ( ORA-01722), but if I use the “executemany” the error is: 

“ expecting string, unicode or buffer object ” 



When I use my code into my program it works correctly, and the records are inserted in table without any errors, but if I extract the part for the attached example I receive the error code 1722… 



To replicate the problem, I send you a code example (see attached). 



Thanks a lot. 

Riccardo 



Here you can find the “create table” scripts: 



CREATE TABLE "A_TEST" ( 

" Compagnia " NUMBER, 

" Datavalutazione " DATE, 

"Descr.compagnia" VARCHAR2(4000 BYTE), 

" Tipovalutazione " VARCHAR2(4000 BYTE), 

" Ramo " VARCHAR2(4000 BYTE), 

" Struttura " VARCHAR2(4000 BYTE), 

" Portafoglio " VARCHAR2(4000 BYTE), 

"Descr.portafoglio" VARCHAR2(4000 BYTE), 

" Prodotto " NUMBER, 

"Descr.prodotto" VARCHAR2(4000 BYTE), 

" Sottoprodotto " VARCHAR2(4000 BYTE), 

" Descrsottoprodotto " VARCHAR2(4000 BYTE), 

" Titolocodiceinterno " NUMBER, 

"ISIN" VARCHAR2(4000 BYTE), 

"Descr.titolo" VARCHAR2(4000 BYTE), 

" Quantita " NUMBER, 

" Valorecarico " NUMBER, 

" Prezzocarico " NUMBER, 

" Valoremercato " NUMBER, 

" Prezzomercato " NUMBER, 

" Plusmercato " NUMBER, 

" Minusmercato " NUMBER, 

"Plus_minusnegoziazione" NUMBER, 

" Competenzadividendi " NUMBER, 

" Competenzacedolare " NUMBER, 

" Competenzascarti " NUMBER, 

" Capitalemedio " NUMBER, 

" Rendimentodiperiodo " NUMBER, 

"Duration" NUMBER, 

" Durationmodificata " NUMBER, 

" Rendimentoeffettivolordo " NUMBER, 

" Vitaresidua " NUMBER, 

"RipresediValore" NUMBER, 

" Minusresidue " NUMBER, 

" Nominaleinizioperiodo " NUMBER, 

"Pat.nettoannocorrente" NUMBER, 

" Gruppotitolo " VARCHAR2(4000 BYTE), 

" Categoriadibilancio " VARCHAR2(4000 BYTE), 

"Descr.categoriadibilancio" VARCHAR2(4000 BYTE), 

"Catt.ST.P.CL.1" VARCHAR2(4000 BYTE), 

"Cod.Bloomberg" VARCHAR2(4000 BYTE), 

"YellowKeyBloomberg" VARCHAR2(4000 BYTE), 

"Index-linked" VARCHAR2(4000 BYTE), 

" Tipoquotazione " VARCHAR2(4000 BYTE), 

" Tipofondo " VARCHAR2(4000 BYTE), 

" Fondo " VARCHAR2(4000 BYTE), 

" Tipostrutturato " VARCHAR2(4000 BYTE), 

"FondoArmonizzato" VARCHAR2(4000 BYTE), 

" Dataemissione " DATE, 

" Datascadenza " DATE, 

" Prezzoemissione " NUMBER, 

" Datarimborso " DATE, 

" Prezzorimborso " NUMBER, 

" Paeseemissione " VARCHAR2(4000 BYTE), 

"AreaGeografica" VARCHAR2(4000 BYTE), 

"ZonaA" VARCHAR2(4000 BYTE), 

" Mnemonicoemittente " VARCHAR2(4000 BYTE), 

" Settoreemittente " VARCHAR2(4000 BYTE), 

"Descr.emittente" VARCHAR2(4000 BYTE), 

" Capogruppoemittente " VARCHAR2(4000 BYTE), 

"RatingMOODYS" VARCHAR2(4000 BYTE), 

"RatingS&P" VARCHAR2(4000 BYTE), 

"RatingFITCH" VARCHAR2(4000 BYTE), 

" Ratingcalc ." VARCHAR2(4000 BYTE), 

"RatingMOODYSI.E." VARCHAR2(4000 BYTE), 

"RatingSPIE" VARCHAR2(4000 BYTE), 

"RatingFITCHIE" VARCHAR2(4000 BYTE), 

"Ratingcal.cIE" VARCHAR2(4000 BYTE), 

" Tipotasso " VARCHAR2(4000 BYTE), 

" Periodicitacedola " VARCHAR2(4000 BYTE), 

" Cedolacorrente " VARCHAR2(4000 BYTE), 

" Tassocedola " NUMBER, 

"Floor" NUMBER, 

"Cap" NUMBER, 

" Subordinato " VARCHAR2(4000 BYTE), 

" Mercatodiquotazione " VARCHAR2(4000 BYTE), 

" Divisaemissione " VARCHAR2(4000 BYTE), 

"Descr.calcolorateo" VARCHAR2(4000 BYTE), 

" Datagodimento " DATE, 

" Tipopaese " VARCHAR2(4000 BYTE), 

"Valorestoricodiv.ist." NUMBER, 

"Valorestoricodiv.emi." NUMBER, 

" Cambiodicarico " NUMBER, 

" Capitalesociale " NUMBER, 

" Collegamentoistituto " VARCHAR2(4000 BYTE), 

"FondoAperto_Chiuso" VARCHAR2(4000 BYTE), 

"FondoRiservato" VARCHAR2(4000 BYTE), 

"ValoreNominalefineperiodo" NUMBER 

) 











From: avinash nandakumar [mailto:avi...@or...] 
Sent: martedì 23 settembre 2014 14:29 
To: cx-...@li... 
Cc: Oliosi Riccardo 
Subject: Re: [cx-oracle-users] "expecting string, unicode or buffer object" on executemany 



Hello Ricardo, 

Can you send a sample insert statement (unicode) similar to one you are using. 
Also can you inform which python/ cx-oracle and oracle version you are using. 

Best Regards, 
Avinash 


On 9/23/2014 5:32 PM, Oliosi Riccardo wrote: 



Hello everybody, 

I have a strange error: 



When I call: 

cursor.executemany( self.__sql, self.__buffer ) 



where 

self.__sql is an insert statement (unicode) and self.__buffer is a list of tuple, the execution throws an exception with the following message: 

“expecting string, unicode or buffer object” 




But if I substitute the executemany with a cycle: 



for item in self.__buffer: 

cursor.execute(self.__sql, item) 



the script works without any problems. 



Can anyone help me? 

Thanks in advance. 



Riccardo 

______________________ 



Riccardo Oliosi 

SIDFramework 

Gruppo CAD IT 

Tel. +39.045.82.11.111 

Fax +39.045.82.11.110 

ol...@ca... 

www.cadit.it 



CAD IT S.p.A. 

Sede legale: Via Torricelli, 44/A - 37136 Verona 

C.S. € 4.669.600,00 i.v. 

C.F./P.IVA e n. iscr. R.I. di VR 01992770238 

R.E.A. di VR n. 210441 

______________________ 



Questo messaggio, con gli eventuali allegati, contiene informazioni riservate o confidenziali. Chiunque lo ricevesse pur non essendone il destinatario è pregato di segnalarlo immediatamente al mittente, di cancellarlo dal proprio sistema e di non copiarlo, diffonderne il contenuto o utilizzarlo in alcun modo. 



This e-mail and any files transmitted with it are confidential or privileged. If you are not the intended recipient of this e-mail, please notify the sender and delete it from your system immediately: you should not copy, disclose or use either it or its attachments in any way whatsoever. 






------------------------------------------------------------------------------ Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk 




_______________________________________________ cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users 

[cx-oracle-users] cx_oracle: Large LOBs and LOB prefetch

[Krishna M. IV <kri...@or...>]

Hello all,
  planning to enhance cx_Oracle driver with a couple of LOB related 
features. Can you please comment on the API that is proposed below?

thanks,
krishna


    LOB Prefetch and Large LOB support


    1.LOB Prefetch
    This helps in prefetching the LOB contents, chunk size and actual
    size along with the statement execution, without doing additional
    round trips. This will be beneficial especially if LOB sizes are small.


      1.1.APIs/Classes


        1.1.1.Connection.lobprefetchsize


        This read-write attribute specifies the LOB prefetch size in
        bytes and indication to prefetch metadata such as chunk size and
        length of LOB. The default value is -1 bytes.It means all the
        three are off by default. If the value is 0, it will prefetch
        metadata information such as chunk size and length. For values
        greater than 0, the metadata as well as the LOB data of size
        specified is prefetched.

/Lob Prefetch/

If set to value greater than 0 it helps in prefetching the LOB contents 
along with the statement execution/fetch. The metadata also is 
prefetched. The LOB.read()tries to get the data from this prefetch 
buffer before making a round trip to the database server. It boosts 
performance if a large number of small LOB objects are fetched from the 
database, as the number of round-trips made per LOB are reduced.

/Lob Metadata/

The metadata such as chunk size and length of Lob can be queried using 
LOB.getchunksize()and LOB.size()without round trips if the value of the 
parameter is set to values greater than or equal to 0.

This attribute value will be applicable for all the CLOB, BLOB and BFILE 
objects fetched through the connection on which the value is set.

/Note /

This attribute is an extension to the DB API definition.//

/Example/

import cx_oracle

conn = cx_Oracle.Connection("scott/tiger@inst1")

*conn.lobprefetchsize = 40000*# Prefetch 40000 bytes and metadata

cursor = conn.cursor()

cursor.execute("SELECT b FROM v_lobs WHERE b IS NOT NULL")

result = cursor.fetchone()

mylob = result[0]

chunksize = mylob.getchunksize()*# No RoundTrip, prefetched already*

loblength = mylob.size()*# No RoundTrip, prefetched already*

print ‘Lob Length is ’ + loblength

lob_data = mylob.read(chunksize) *# No Roundtrip ,if size < than 40000*

lobcur.close();

conn.close();

/See Also/

Prefetching of LOB Data 
<http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci07lob.htm#CHDDHFAB>


        1.2.Compatibility

OCI 11.1 or higher is required on the client and server.


    2.Large LOBs

Support for Lobs larger than 4GB was added in Oracle 10g. Add changes to 
support them in cx_oracle driver. No change in API.


      2.1.APIs/Classes

Some of the functions in LOB type will be affected which include 
LOB.size(), LOB.trim(), LOB.read() and LOB.write() to support the bigger 
size. There is no API change.

/See Also/

Using LOBs of Size Greater than 4 GB 
<http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci07lob.htm#i444306>


      2.2.Compatibility

OCI 10.1 or higher is required on the client and server.

Re: [cx-oracle-users] callproc dbms_metadata.add_file

[Werner M. <wer...@gm...>]

Hi,

I was mistaken that the conversion error was caused by the filesize parameter, it is caused by the value for filetype.

Taking the constant (3 for logfile) works. 

Still wondering how to write the oracle constant so that it works - I definitely missed the DBMS_DATAPUMP here but it does not work anyway.

Anything else I need works fine (exporting only a view tables and selecting the rows with a subquery) - nice!

thanks,

Werner


 
 

Gesendet: Mittwoch, 24. September 2014 um 21:18 Uhr
Von: "Werner Moser" <wer...@gm...>
An: cx-...@li...
Betreff: [cx-oracle-users] callproc dbms_metadata.add_file

Hi,
 
I'm writing a little script which exports some data from my database and I want to use dbms_datapump.
 
It almost works, I am stuck with the logfile though. To create one, you have to use the function dbms_datapump.add_file:
 
DBMS_DATAPUMP.ADD_FILE (
   handle     IN NUMBER,
   filename   IN VARCHAR2,
   directory  IN VARCHAR2,
   filesize   IN VARCHAR2 DEFAULT NULL,
   filetype   IN NUMBER DEFAULT DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE),
   reusefile  IN NUMBER DEFAULT NULL;

 
when I call it with the first five parameters, I always get an ORA-6502 - for the size of the file (which is not needed for the logfile ...)
 

cur.callproc('dbms_datapump.add_file',[handle,'test.log','EXP_IMP',102400,'KU$_FILE_TYPE_LOG_FILE'])
cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
 
As far as I have found out only positional parameters can be used, i.e. a dict or key/value is not supported - if it was I could use only the needed parameters here (handle, filename, directory and filetype).
 
so, how can I get this to work? the parameter filesize is optional but how can I write that?
 
br,
 
Werner
 ------------------------------------------------------------------------------ Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk_______________________________________________ cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users[https://lists.sourceforge.net/lists/listinfo/cx-oracle-users]

Re: [cx-oracle-users] callproc dbms_metadata.add_file

[Doug H. <djn...@gm...>]

On 24 September 2014 13:18, Werner Moser wrote:


> DBMS_DATAPUMP.ADD_FILE (
>    handle     IN NUMBER,
>    filename   IN VARCHAR2,
>    directory  IN VARCHAR2,
>    filesize   IN VARCHAR2 DEFAULT NULL,
>    filetype   IN NUMBER DEFAULT DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE),
>    reusefile  IN NUMBER DEFAULT NULL;

Note the misplaced ")" from the original Oracle document.

> cur.callproc('dbms_datapump.add_file', [handle,'test.log','EXP_IMP',102400,'KU$_FILE_TYPE_LOG_FILE'])

Try replacing 'KU$_FILE_TYPE_LOG_FILE' with the constant value defined
in the DBMS_DATAPUMP package. If that works, you could try extracting
that value by fetching the result of "select
DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE from dual". Off the top of my
head, I don't know an easier way to get a constant out of a package
from python.

BTW, in my tests, failures while defining the data pump job would
result in a situation where I could not define a new job with the same
name. I did not discover a way of clearing a job in the defining state
other than a database stop/start cycle. You could not attach to the
job because it was not running and you could not open it because it
already existed. Without a handle, you could not

Doug


-- 
Doug Henderson, Calgary, Alberta, Canada

Re: [cx-oracle-users] callproc dbms_metadata.add_file

[Chris G. <chr...@to...>]

What does the rest of your script look like?  Have you called
DBMS_DATAPUMP.OPEN() to generate the handle you're using in the proc you're
having trouble with?


On 24 September 2014 20:18, Werner Moser <wer...@gm...> wrote:

> Hi,
>
> I'm writing a little script which exports some data from my database and I
> want to use dbms_datapump.
>
> It almost works, I am stuck with the logfile though. To create one, you
> have to use the function dbms_datapump.add_file:
>
>
> DBMS_DATAPUMP.ADD_FILE (
>    handle     IN NUMBER,
>    filename   IN VARCHAR2,
>    directory  IN VARCHAR2,
>    filesize   IN VARCHAR2 DEFAULT NULL,
>    filetype   IN NUMBER DEFAULT DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE),
>    reusefile  IN NUMBER DEFAULT NULL;
>
>
> when I call it with the first five parameters, I always get an ORA-6502 -
> for the size of the file (which is not needed for the logfile ...)
>
>
> cur.callproc('dbms_datapump.add_file',[handle,'test.log','EXP_IMP',102400,'KU$_FILE_TYPE_LOG_FILE'])
> cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error:
> character to number conversion error
>
> As far as I have found out only positional parameters can be used, i.e. a
> dict or key/value is not supported - if it was I could use only the needed
> parameters here (handle, filename, directory and filetype).
>
> so, how can I get this to work? the parameter filesize is optional but how
> can I write that?
>
> br,
>
> Werner
>
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
>
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] callproc dbms_metadata.add_file

[Werner M. <wer...@gm...>]

<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi,</div>

<div>&nbsp;</div>

<div>I&#39;m writing a little script which exports some data from my database and I want to use dbms_datapump.</div>

<div>&nbsp;</div>

<div>It almost works, I am stuck with the logfile though. To create one, you have to use the function dbms_datapump.add_file:</div>

<div>&nbsp;</div>

<div>
<pre class="oac_no_warn">DBMS_DATAPUMP.ADD_FILE (
   handle     IN NUMBER,
   filename   IN VARCHAR2,
   directory  IN VARCHAR2,
   filesize   IN VARCHAR2 DEFAULT NULL,
   filetype   IN NUMBER DEFAULT DBMS_DATAPUMP.KU&#36;_FILE_TYPE_DUMP_FILE),
   reusefile  IN NUMBER DEFAULT NULL;</pre>

<div>&nbsp;</div>

<div>when I call it with the first five parameters, I always get an ORA-6502 - for the size of the file (which is not needed for the logfile ...)</div>

<div>&nbsp;</div>

<div>
<div>cur.callproc(&#39;dbms_datapump.add_file&#39;,[handle,&#39;test.log&#39;,&#39;EXP_IMP&#39;,102400,&#39;KU&#36;_FILE_TYPE_LOG_FILE&#39;])<br/>
cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error</div>

<div>&nbsp;</div>

<div>As far as I have found out only positional parameters can be used, i.e. a dict or key/value is not supported - if it was I could use only the needed parameters here (handle, filename, directory and filetype).</div>

<div>&nbsp;</div>

<div>so, how can I get this to work? the parameter filesize is optional but how can I write that?</div>

<div>&nbsp;</div>

<div>br,</div>

<div>&nbsp;</div>

<div>Werner</div>

<div>&nbsp;</div>
</div>
</div></div></body></html>

[cx-oracle-users] Wheel distribution for cx_Oracle?

[<edb...@gm...>]

Hello,

I was wondering how feasible it would be to get Wheel distributions for
cx_Oracle uploaded to PyPI, and I found this thread from last year:

http://sourceforge.net/p/cx-oracle/mailman/message/31100757/

Did anything come of this?  What would need to be done at this point to
finish the work?

(Also, please let me know if there's a better list for this conversation.)

Thank you,
-Ed Brannin
edb...@gm...
cell: 585-261-0279