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

Re: [cx-oracle-users] More than one type returned for a single column

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

On Sun, Sep 30, 2012 at 12:43 PM, Anssi Kääriäinen <aka...@gm...> wrote:
> On Sun, Sep 30, 2012 at 8:11 PM, Jani Tiainen <re...@gm...> wrote:
>>
>> That is exactly correct. You didn't provided initial precision nor scale
>> so it's adapting to whatever data is stored there.
>>
>> So when you save data Oracle stores data "as is". But I did some code
>> diving [1] to cx_oracle and it seems to do some analysis to returned data
>> and figures out return type.
>>
>
> Yes, it is adapting to the data. The problem I have is that my expectation
> is that each SQL column has a single type. This is not reflected in the
> Python return types.
>
> Maybe my expectation isn't valid... DBAPI2 doesn't seem to require single
> type for single column, the only requirement I see is that
> cursor.description tells the type of the column, and one type available is
> NUMBER. How NUMBER is then represented in Python is left unspecified.
>
>  - Anssi

Which type is returned is dependent on the precision and/or scale as
defined by the column being returned. If the number of digits after
the decimal point is 0 then an integer is returned; otherwise, a float
is returned. You can completely bypass this algorithm and do your own
thing by using an outputtypehandler method. See the documentation for
the details of what this method expects to receive and what is
expected to be returned. If you need further help, though, let me know
and I can provide an example. The ReturnUnicode example should be a
good start, though.

Anthony

Re: [cx-oracle-users] More than one type returned for a single column

[Anssi K. <aka...@gm...>]

On Sun, Sep 30, 2012 at 8:11 PM, Jani Tiainen <re...@gm...> wrote:

> That is exactly correct. You didn't provided initial precision nor scale
> so it's adapting to whatever data is stored there.
>
> So when you save data Oracle stores data "as is". But I did some code
> diving [1] to cx_oracle and it seems to do some analysis to returned data
> and figures out return type.
>
>
Yes, it is adapting to the data. The problem I have is that my expectation
is that each SQL column has a single type. This is not reflected in the
Python return types.

Maybe my expectation isn't valid... DBAPI2 doesn't seem to require single
type for single column, the only requirement I see is that
cursor.description tells the type of the column, and one type available is
NUMBER. How NUMBER is then represented in Python is left unspecified.

 - Anssi

Re: [cx-oracle-users] More than one type returned for a single column

[Jani T. <re...@gm...>]

On Sun, Sep 30, 2012 at 6:54 PM, Anssi Kääriäinen <aka...@gm...>wrote:

> Same problem when using a table, too:
>
> import cx_Oracle
> connection = cx_Oracle.connect('user/passwd@' +
>
>                                cx_Oracle.makedsn('localhost', 1521, 'xe'))
> cur = connection.cursor()
> cur.execute("create table testt(col1 number, col2 number(5, 2))")
> cur.execute("insert into testt values(1, 1)")
> cur.execute("insert into testt values(1.1, 1.1)")
> cur.execute("select col1, col2 from testt")
> rows = list(cur.fetchall())
> col1_vals = [row[0] for row in rows]
> col2_vals = [row[1] for row in rows]
> print col1_vals
> print col2_vals
> print set([type(v) for v in col1_vals])
> print set([type(v) for v in col2_vals])
>
> OUT:
> [1, 1.1]
> [1.0, 1.1]
>
> set([<type 'float'>, <type 'int'>])
> set([<type 'float'>])
>
> Describe of the table tells the type of col1 is plain NUMBER.
>
>
That is exactly correct. You didn't provided initial precision nor scale so
it's adapting to whatever data is stored there.

So when you save data Oracle stores data "as is". But I did some code
diving [1] to cx_oracle and it seems to do some analysis to returned data
and figures out return type.


[1] http://sourceforge.net/p/cx-oracle/code/368/tree/trunk/NumberVar.c
-- 
Jani Tiainen

- Well planned is half done, and a half done has been sufficient before...

Re: [cx-oracle-users] More than one type returned for a single column

[Anssi K. <aka...@gm...>]

Same problem when using a table, too:

import cx_Oracle
connection = cx_Oracle.connect('user/passwd@' +
                               cx_Oracle.makedsn('localhost', 1521, 'xe'))
cur = connection.cursor()
cur.execute("create table testt(col1 number, col2 number(5, 2))")
cur.execute("insert into testt values(1, 1)")
cur.execute("insert into testt values(1.1, 1.1)")
cur.execute("select col1, col2 from testt")
rows = list(cur.fetchall())
col1_vals = [row[0] for row in rows]
col2_vals = [row[1] for row in rows]
print col1_vals
print col2_vals
print set([type(v) for v in col1_vals])
print set([type(v) for v in col2_vals])

OUT:
[1, 1.1]
[1.0, 1.1]
set([<type 'float'>, <type 'int'>])
set([<type 'float'>])

Describe of the table tells the type of col1 is plain NUMBER.

 - Anssi


On Sun, Sep 30, 2012 at 5:51 PM, Dwayne King
<dw...@kr...>wrote:

> It *is* optional in oracle to supply the precision and scale when
> declaring a data type (in which case it will use the maximums for the
> type), so perhaps this is what happens.
>
> Can you do a describe on the table in question from sqlplus and show us?
>
> Create a table like:
> Create table test( col1 number, col2 number(5,2))
>
> Then see if the first column gives you back what you're seeing.  Sorry -
> not close to a computer right now
>
> Sent from my iPad
>
> On 2012-09-29, at 8:42 PM, Anssi Kääriäinen <aka...@gm...> wrote:
>
>
> On Sun, Sep 30, 2012 at 3:12 AM, Chris Gould <chr...@to...>wrote:
>
>> Arent they really both type NUMBER ?
>>
>>
> It have an (incorrect?) expectation that one column should have one result
> type in Python. While float and int are close to each other in Python, they
> do have differences, for example the '/' operator.
>
> The situation came up in trying to use outputtypefactory in Django, then
> optimizing the case where the type of the result is guessed, see
> https://github.com/shaib/django/blob/d10eaf28c864816e9bb8b0d1467e90b79291792a/django/db/backends/oracle/base.py#L772- the problem there is that half of the numbers are guessed as Decimal,
> half as int. This isn't a nice situation, the APIs for Decimal and int are
> very different.
>
> I don't know Oracle too well - is the situation such that the result is
> NUMBER in Oracle, and there is no more information about the column
> available from the DB?
>
>  - Anssi
>
>
>
>>
>> On Sunday, September 30, 2012, Anssi Kääriäinen wrote:
>>
>>> It seems it is possible to have more than one type returned for a single
>>> column. Observe this test case:
>>>
>>> import cx_Oracle
>>> print cx_Oracle.version
>>> connection = cx_Oracle.connect('user/
>>> passwd@' + cx_Oracle.makedsn('localhost', 1521, 'xe'))
>>> cur = connection.cursor()
>>> cur.execute("select case when dbms_random.random > 0.5 then 0.1 else 0
>>> end from testtable")
>>> vals = []
>>> vals.extend(row[0] for row in cur.fetchall())
>>> print set([type(v) for v in vals])
>>>
>>> The output is:
>>> 5.1.2
>>> set([<type 'float'>, <type 'int'>])
>>>
>>> To me this seems surprising - shouldn't a column have the same type
>>> across all rows in a single resultset?
>>>
>>>  - Anssi
>>>
>>
>>
>> --
>> Sent from Gmail Mobile
>>
>>
>> ------------------------------------------------------------------------------
>> Everyone hates slow websites. So do we.
>> Make your web apps faster with AppDynamics
>> Download AppDynamics Lite for free today:
>> http://ad.doubleclick.net/clk;258768047;13503038;j?
>> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://ad.doubleclick.net/clk;258768047;13503038;j?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://ad.doubleclick.net/clk;258768047;13503038;j?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] More than one type returned for a single column

[Dwayne K. <dw...@kr...>]

It *is* optional in oracle to supply the precision and scale when declaring a data type (in which case it will use the maximums for the type), so perhaps this is what happens.  

Can you do a describe on the table in question from sqlplus and show us?

Create a table like:
Create table test( col1 number, col2 number(5,2))

Then see if the first column gives you back what you're seeing.  Sorry - not close to a computer right now

Sent from my iPad

On 2012-09-29, at 8:42 PM, Anssi Kääriäinen <aka...@gm...> wrote:

> 
> On Sun, Sep 30, 2012 at 3:12 AM, Chris Gould <chr...@to...> wrote:
> Arent they really both type NUMBER ? 
> 
> 
> It have an (incorrect?) expectation that one column should have one result type in Python. While float and int are close to each other in Python, they do have differences, for example the '/' operator.
> 
> The situation came up in trying to use outputtypefactory in Django, then optimizing the case where the type of the result is guessed, see https://github.com/shaib/django/blob/d10eaf28c864816e9bb8b0d1467e90b79291792a/django/db/backends/oracle/base.py#L772 - the problem there is that half of the numbers are guessed as Decimal, half as int. This isn't a nice situation, the APIs for Decimal and int are very different.
> 
> I don't know Oracle too well - is the situation such that the result is NUMBER in Oracle, and there is no more information about the column available from the DB?
> 
>  - Anssi
> 
>  
> 
> On Sunday, September 30, 2012, Anssi Kääriäinen wrote:
> It seems it is possible to have more than one type returned for a single column. Observe this test case:
> 
> import cx_Oracle
> print cx_Oracle.version
> connection = cx_Oracle.connect('user/
> passwd@' + cx_Oracle.makedsn('localhost', 1521, 'xe'))
> cur = connection.cursor()
> cur.execute("select case when dbms_random.random > 0.5 then 0.1 else 0 end from testtable")
> vals = []
> vals.extend(row[0] for row in cur.fetchall())
> print set([type(v) for v in vals])
> 
> The output is:
> 5.1.2
> set([<type 'float'>, <type 'int'>])
> 
> To me this seems surprising - shouldn't a column have the same type across all rows in a single resultset?
> 
>  - Anssi
> 
> 
> -- 
> Sent from Gmail Mobile
> 
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://ad.doubleclick.net/clk;258768047;13503038;j?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 
> 
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://ad.doubleclick.net/clk;258768047;13503038;j?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] More than one type returned for a single column

[Jani T. <re...@gm...>]

Oracle does have three different numeric datatypes: NUMBER and BINARY_FLOAT
and BINARY_DOUBLE.

NUMBER is most commonly used one and while defining column you can define
percision, scale if needed. But it doesn't exatly tell is number integer or
not. By setting scale to 0 (or not defining at all) you can only store
integer numbers to a column.

By looking source code of cx_Oracle it does make decision is output
variable int, float or string based on NUMBER type returned (scale,
precision taken into account) so I think it's purely possible to have to
different output types - specially if you use code that creates columns on
the fly. Like that sample query from dual.



On Sun, Sep 30, 2012 at 3:42 AM, Anssi Kääriäinen <aka...@gm...>wrote:

>
> On Sun, Sep 30, 2012 at 3:12 AM, Chris Gould <chr...@to...>wrote:
>
>> Arent they really both type NUMBER ?
>>
>>
> It have an (incorrect?) expectation that one column should have one result
> type in Python. While float and int are close to each other in Python, they
> do have differences, for example the '/' operator.
>
> The situation came up in trying to use outputtypefactory in Django, then
> optimizing the case where the type of the result is guessed, see
> https://github.com/shaib/django/blob/d10eaf28c864816e9bb8b0d1467e90b79291792a/django/db/backends/oracle/base.py#L772- the problem there is that half of the numbers are guessed as Decimal,
> half as int. This isn't a nice situation, the APIs for Decimal and int are
> very different.
>
> I don't know Oracle too well - is the situation such that the result is
> NUMBER in Oracle, and there is no more information about the column
> available from the DB?
>
>  - Anssi
>
>
>
>>
>> On Sunday, September 30, 2012, Anssi Kääriäinen wrote:
>>
>>> It seems it is possible to have more than one type returned for a single
>>> column. Observe this test case:
>>>
>>> import cx_Oracle
>>> print cx_Oracle.version
>>> connection = cx_Oracle.connect('user/
>>> passwd@' + cx_Oracle.makedsn('localhost', 1521, 'xe'))
>>> cur = connection.cursor()
>>> cur.execute("select case when dbms_random.random > 0.5 then 0.1 else 0
>>> end from testtable")
>>> vals = []
>>> vals.extend(row[0] for row in cur.fetchall())
>>> print set([type(v) for v in vals])
>>>
>>> The output is:
>>> 5.1.2
>>> set([<type 'float'>, <type 'int'>])
>>>
>>> To me this seems surprising - shouldn't a column have the same type
>>> across all rows in a single resultset?
>>>
>>>  - Anssi
>>>
>>
>>
>> --
>> Sent from Gmail Mobile
>>
>>
>> ------------------------------------------------------------------------------
>> Everyone hates slow websites. So do we.
>> Make your web apps faster with AppDynamics
>> Download AppDynamics Lite for free today:
>> http://ad.doubleclick.net/clk;258768047;13503038;j?
>> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://ad.doubleclick.net/clk;258768047;13503038;j?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>


-- 
Jani Tiainen

- Well planned is half done, and a half done has been sufficient before...

Re: [cx-oracle-users] More than one type returned for a single column

[Anssi K. <aka...@gm...>]

On Sun, Sep 30, 2012 at 3:12 AM, Chris Gould <chr...@to...>wrote:

> Arent they really both type NUMBER ?
>
>
It have an (incorrect?) expectation that one column should have one result
type in Python. While float and int are close to each other in Python, they
do have differences, for example the '/' operator.

The situation came up in trying to use outputtypefactory in Django, then
optimizing the case where the type of the result is guessed, see
https://github.com/shaib/django/blob/d10eaf28c864816e9bb8b0d1467e90b79291792a/django/db/backends/oracle/base.py#L772-
the problem there is that half of the numbers are guessed as Decimal,
half as int. This isn't a nice situation, the APIs for Decimal and int are
very different.

I don't know Oracle too well - is the situation such that the result is
NUMBER in Oracle, and there is no more information about the column
available from the DB?

 - Anssi



>
> On Sunday, September 30, 2012, Anssi Kääriäinen wrote:
>
>> It seems it is possible to have more than one type returned for a single
>> column. Observe this test case:
>>
>> import cx_Oracle
>> print cx_Oracle.version
>> connection = cx_Oracle.connect('user/
>> passwd@' + cx_Oracle.makedsn('localhost', 1521, 'xe'))
>> cur = connection.cursor()
>> cur.execute("select case when dbms_random.random > 0.5 then 0.1 else 0
>> end from testtable")
>> vals = []
>> vals.extend(row[0] for row in cur.fetchall())
>> print set([type(v) for v in vals])
>>
>> The output is:
>> 5.1.2
>> set([<type 'float'>, <type 'int'>])
>>
>> To me this seems surprising - shouldn't a column have the same type
>> across all rows in a single resultset?
>>
>>  - Anssi
>>
>
>
> --
> Sent from Gmail Mobile
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://ad.doubleclick.net/clk;258768047;13503038;j?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] More than one type returned for a single column

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

Arent they really both type NUMBER ?

On Sunday, September 30, 2012, Anssi Kääriäinen wrote:

> It seems it is possible to have more than one type returned for a single
> column. Observe this test case:
>
> import cx_Oracle
> print cx_Oracle.version
> connection = cx_Oracle.connect('user/
> passwd@' + cx_Oracle.makedsn('localhost', 1521, 'xe'))
> cur = connection.cursor()
> cur.execute("select case when dbms_random.random > 0.5 then 0.1 else 0 end
> from testtable")
> vals = []
> vals.extend(row[0] for row in cur.fetchall())
> print set([type(v) for v in vals])
>
> The output is:
> 5.1.2
> set([<type 'float'>, <type 'int'>])
>
> To me this seems surprising - shouldn't a column have the same type across
> all rows in a single resultset?
>
>  - Anssi
>


-- 
Sent from Gmail Mobile

[cx-oracle-users] More than one type returned for a single column

[Anssi K. <aka...@gm...>]

It seems it is possible to have more than one type returned for a single
column. Observe this test case:

import cx_Oracle
print cx_Oracle.version
connection = cx_Oracle.connect('user/
passwd@' + cx_Oracle.makedsn('localhost', 1521, 'xe'))
cur = connection.cursor()
cur.execute("select case when dbms_random.random > 0.5 then 0.1 else 0 end
from testtable")
vals = []
vals.extend(row[0] for row in cur.fetchall())
print set([type(v) for v in vals])

The output is:
5.1.2
set([<type 'float'>, <type 'int'>])

To me this seems surprising - shouldn't a column have the same type across
all rows in a single resultset?

 - Anssi

Re: [cx-oracle-users] LOB in temporary table

[Meyer M. <Mat...@ge...>]

Hi Chris,

Thanks for your reply. Indeed I am using an 11.2.0.2 database and the deferred segment creation is on.
So this seems to be the problem.

Kind regards,

Matthias Meyer

________________________________
Von: Chris Gould [mailto:chr...@to...]
Gesendet: Donnerstag, 20. September 2012 17:52
An: cx-...@li...
Betreff: Re: [cx-oracle-users] LOB in temporary table

What version of Oracle are you using?  In 11 a change was introduced called "deferred segment creation" which means that when create statement is executed, segments are not actually created - this is deferred until the time when the first INSERT (or MERGE) statement tries to create rows.  It's caused problems for me in the past (not specifically with Python).  But you can turn this option off or override it when you create objects. It maybe that if you're expecting the segment to exist after the create statement has run, but before you've added any data, this is causing the problem.



On 20 September 2012 13:09, Meyer Matthias <Mat...@ge...<mailto:Mat...@ge...>> wrote:
Hi all,

I'm having difficulties describing a temporary table, which contains a CLOB column.
The storage segment of the lob is not found and the export fails with "Unable to locate LOB segment"

I dug around the oracle docs and found this:
"Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created"
(http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i16096)

This can be verified, as SYS.DBA_SEGMENT does not contain a line for the SEGMENT_NAME of the LOB in SYS.DBA_LOBS.

My guess is, we need a new class in Object.py that represents LOBs without storage segments and is created in the _RetrieveColumns()-method of the Table-class if the table is temporary. But I'm not sure about this...


You can reproduce this as following:

Create the temporary table:

CREATE GLOBAL TEMPORARY TABLE TEST_TEMP_CLOB (
  key1 NUMBER,
  clobcol CLOB
)
ON COMMIT PRESERVE ROWS
RESULT_CACHE ( MODE DEFAULT)
NOCACHE;



Check the sys-views:
SELECT *
  FROM SYS.DBA_SEGMENTS
 WHERE SEGMENT_NAME = (SELECT SEGMENT_NAME
                         FROM SYS.DBA_LOBS
                        WHERE COLUMN_NAME = 'CLOBCOL');


no rows selected



Export the object using cx_OracleTools:
$ > python DescribeObject.py --schema=DWH/<mypw>@DWHT.WORLD -t TEST_TEMP_CLOB
14:00:39.242 Python exception encountered:
14:00:39.242     Message: Unable to locate LOB segment DWH.CLOBCOL
14:00:39.242     Template Id: 0
14:00:39.242     Arguments:
14:00:39.242         name => 'CLOBCOL'
14:00:39.242         owner => 'DWH'
14:00:39.242     Traceback:
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699, in __RetrieveSegment
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667, in __init__
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962, in __RetrieveColumns
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891, in __init__
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line 123, in ObjectByType
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line 263, in RetrieveAndExportObject
14:00:39.243         file DescribeObject.py, line 59, in <module>
14:00:39.243     Details:
14:00:39.243         Local Variables:
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699, in __RetrieveSegment
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           isPrepared -> None
14:00:39.243           row -> None
14:00:39.243           self -> <cx_OracleObject.Object.Lob object at 0xb73d636c>
14:00:39.243           statement -> "\n                    select\n                      tablespace_name,\n                      initial_extent,\n                      next_extent,\n                      min_extents,\n                      max_extents,\n                      pct_increase\nfrom user_segments\nwhere segment_type = 'LOBSEGMENT' and segment_name = :segmentName"
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667, in __init__
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           name -> 'CLOBCOL'
14:00:39.243           owner -> 'DWH'
14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB', 'SYS_LOB0003332346C00002$$', 'YES')
14:00:39.243           self -> <cx_OracleObject.Object.Lob object at 0xb73d636c>
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243           args -> ()
14:00:39.243           classFactory -> <class 'cx_OracleObject.Object.Lob'>
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           keywordArgs -> {'owner': 'DWH', 'name': 'TEST_TEMP_CLOB'}
14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB', 'SYS_LOB0003332346C00002$$', 'YES')
14:00:39.243           statement -> '\n        select\n          o.owner,\n          o.column_name,\n          o.table_name,\n          o.segment_name,\n          o.in_row\n        from %(p_ViewPrefix)s_lobs o\n        %(p_WhereClause)s\n        order by o.column_name'
14:00:39.243           tag -> 'Lobs'
14:00:39.243           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962, in __RetrieveColumns
14:00:39.243           c -> ('CLOBCOL', 'CLOB', 'Y', None, None, 4000, None)
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           isPrepared -> None
14:00:39.243           lobColumns -> ['CLOBCOL']
14:00:39.243           self -> <cx_OracleObject.Object.Table object at 0xb73d622c>
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891, in __init__
14:00:39.243           duration -> 'SYS$SESSION'
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           iotType -> None
14:00:39.243           name -> 'TEST_TEMP_CLOB'
14:00:39.243           owner -> 'DWH'
14:00:39.243           partitioned -> 'NO'
14:00:39.243           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None, None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
14:00:39.243           self -> <cx_OracleObject.Object.Table object at 0xb73d622c>
14:00:39.243           temporary -> 'Y'
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243           args -> ()
14:00:39.244           classFactory -> <class 'cx_OracleObject.Object.Table'>
14:00:39.244           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.244           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.244           keywordArgs -> {'owner': 'DWH', 'name': 'TEST_TEMP_CLOB'}
14:00:39.244           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None, None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
14:00:39.261           statement -> "\n        select\n          o.owner,\n          o.table_name,\n          o.tablespace_name,\n          o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n          o.max_extents,\n          o.pct_increase,\n          o.temporary,\n          o.partitioned,\n          o.duration,\n          o.iot_type\n        from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and secondary = 'N'\n        order by o.owner, o.table_name"
14:00:39.261           tag -> 'Default_TABLE'
14:00:39.261           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.261         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line 123, in ObjectByType
14:00:39.261           name -> 'TEST_TEMP_CLOB'
14:00:39.261           objectFunction -> <class 'cx_OracleObject.Object.Table'>
14:00:39.261           owner -> 'DWH'
14:00:39.261           self -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.261           statement -> "\n        select\n          o.owner,\n          o.table_name,\n          o.tablespace_name,\n          o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n          o.max_extents,\n          o.pct_increase,\n          o.temporary,\n          o.partitioned,\n          o.duration,\n          o.iot_type\n        from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and secondary = 'N'\n        order by o.owner, o.table_name"
14:00:39.261           type -> 'TABLE'
14:00:39.261           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.261         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line 263, in RetrieveAndExportObject
14:00:39.261           objectName -> 'TEST_TEMP_CLOB'
14:00:39.261           objectOwner -> 'DWH'
14:00:39.262           objectType -> 'TABLE'
14:00:39.262           self -> <cx_OracleObject.Describer.Describer object at 0xb73d61cc>
14:00:39.262         file DescribeObject.py, line 59, in <module>
14:00:39.262           Options -> <module 'Options' from '/home/matze/gehe/cx_OracleTools-8.0/Options.pyc'>
14:00:39.262           connection -> <cx_OracleEx.Connection to DWH@DWHT.WORLD>
14:00:39.262           cx_LoggingOptions -> <module 'cx_LoggingOptions' from '/usr/local/lib/python2.7/dist-packages/cx_LoggingOptions.pyc'>
14:00:39.262           cx_OptionParser -> <module 'cx_OptionParser' from '/usr/local/lib/python2.7/dist-packages/cx_OptionParser.pyc'>
14:00:39.262           cx_OracleObject -> <module 'cx_OracleObject' from '/usr/local/lib/python2.7/dist-packages/cx_OracleObject/__init__.pyc'>
14:00:39.262           cx_OracleUtils -> <module 'cx_OracleUtils' from '/usr/local/lib/python2.7/dist-packages/cx_OracleUtils.pyc'>
14:00:39.262           describer -> <cx_OracleObject.Describer.Describer object at 0xb73d61cc>
14:00:39.262           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.262           objectName -> 'TEST_TEMP_CLOB'
14:00:39.262           objectOwner -> 'DWH'
14:00:39.262           objectType -> 'TABLE'
14:00:39.262           options -> <Values at 0xb73d60cc: {'help': None, 'asOfScn': None, 'logLevel': 'error', 'wantStorage': True, 'useDbaViews': False, 'wantComments': True, 'wantGrants': True, 'wantTablespace': True, 'showBanner': None, 'wantViewColumns': False, 'wantSynonyms': False, 'wantRelated': True, 'version': None, 'mergeGrants': True, 'schema': 'DWH/<mypw>@DWHT.WORLD', 'objectName': 'TEST_TEMP_CLOB', 'maxLongSize': 131072, 'fileName': None, 'wantTriggers': True, 'logPrefix': '%t', 'asOfTimestamp': None, 'traceback...
14:00:39.262           outFile -> <open file '<stdout>', mode 'w' at 0xb777b078>
14:00:39.262           parser -> <cx_OptionParser.OptionParser instance at 0xb73d4c2c>
14:00:39.262           sys -> <module 'sys' (built-in)>


Thanks and kind regards,

Matthias Meyer

GEHE Informatik Services GmbH & Co. KG, Stuttgart, AG Stuttgart HRA 12167
Neckartalstr. 155, D-70376 Stuttgart
Geschäftsführer: Rainer Baumgärtner, Till Rausch, Erwin Schiller
persönlich haftende Gesellschafterin: GEHE Informatik Services-Verwaltungs GmbH, Stuttgart, AG Stuttgart 15843
Weiterer Gesellschafter: GEHE Pharma Handel GmbH, Stuttgart, AG Stuttgart HRB 14591
USt-IdNr.: DE 811 655 252

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://ad.doubleclick.net/clk;258768047;13503038;j?
http://info.appdynamics.com/FreeJavaPerformanceDownload.html
_______________________________________________
cx-oracle-users mailing list
cx-...@li...<mailto:cx-...@li...>
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

________________________________
GEHE Informatik Services GmbH & Co. KG, Stuttgart, AG Stuttgart HRA 12167
Neckartalstr. 155, D-70376 Stuttgart
Geschäftsführer: Rainer Baumgärtner, Till Rausch, Erwin Schiller
persönlich haftende Gesellschafterin: GEHE Informatik Services-Verwaltungs GmbH, Stuttgart, AG Stuttgart 15843
Weiterer Gesellschafter: GEHE Pharma Handel GmbH, Stuttgart, AG Stuttgart HRB 14591
USt-IdNr.: DE 811 655 252

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

Re: [cx-oracle-users] LOB in temporary table

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

What version of Oracle are you using?  In 11 a change was introduced called
"deferred segment creation" which means that when create statement is
executed, segments are not actually created - this is deferred until the
time when the first INSERT (or MERGE) statement tries to create rows.  It's
caused problems for me in the past (not specifically with Python).  But you
can turn this option off or override it when you create objects. It maybe
that if you're expecting the segment to exist after the create statement
has run, but before you've added any data, this is causing the problem.




On 20 September 2012 13:09, Meyer Matthias <Mat...@ge...> wrote:

> Hi all,
>
> I’m having difficulties describing a temporary table, which contains a
> CLOB column.
> The storage segment of the lob is not found and the export fails with
> "Unable to locate LOB segment"
>
> I dug around the oracle docs and found this:
> "Temporary tables use temporary segments. Unlike permanent tables,
> temporary tables and their indexes do not automatically allocate a segment
> when they are created"
> (http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i16096)
>
> This can be verified, as SYS.DBA_SEGMENT does not contain a line for the
> SEGMENT_NAME of the LOB in SYS.DBA_LOBS.
>
> My guess is, we need a new class in Object.py that represents LOBs without
> storage segments and is created in the _RetrieveColumns()-method of the
> Table-class if the table is temporary. But I'm not sure about this...
>
>
> You can reproduce this as following:
>
> Create the temporary table:
>
> CREATE GLOBAL TEMPORARY TABLE TEST_TEMP_CLOB (
>   key1 NUMBER,
>   clobcol CLOB
> )
> ON COMMIT PRESERVE ROWS
> RESULT_CACHE ( MODE DEFAULT)
> NOCACHE;
>
>
>
> Check the sys-views:
> SELECT *
>   FROM SYS.DBA_SEGMENTS
>  WHERE SEGMENT_NAME = (SELECT SEGMENT_NAME
>                          FROM SYS.DBA_LOBS
>                         WHERE COLUMN_NAME = 'CLOBCOL');
>
>
> no rows selected
>
>
>
> Export the object using cx_OracleTools:
> $ > python DescribeObject.py --schema=DWH/<mypw>@DWHT.WORLD -t
> TEST_TEMP_CLOB
> 14:00:39.242 Python exception encountered:
> 14:00:39.242     Message: Unable to locate LOB segment DWH.CLOBCOL
> 14:00:39.242     Template Id: 0
> 14:00:39.242     Arguments:
> 14:00:39.242         name => 'CLOBCOL'
> 14:00:39.242         owner => 'DWH'
> 14:00:39.242     Traceback:
> 14:00:39.242         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699,
> in __RetrieveSegment
> 14:00:39.242         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667,
> in __init__
> 14:00:39.242         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line
> 1185, in ObjectIterator
> 14:00:39.242         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962,
> in __RetrieveColumns
> 14:00:39.242         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891,
> in __init__
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line
> 1185, in ObjectIterator
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line
> 123, in ObjectByType
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line
> 263, in RetrieveAndExportObject
> 14:00:39.243         file DescribeObject.py, line 59, in <module>
> 14:00:39.243     Details:
> 14:00:39.243         Local Variables:
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699,
> in __RetrieveSegment
> 14:00:39.243           cursor -> <cx_OracleEx.Cursor on
> <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
> 14:00:39.243           isPrepared -> None
> 14:00:39.243           row -> None
> 14:00:39.243           self -> <cx_OracleObject.Object.Lob object at
> 0xb73d636c>
> 14:00:39.243           statement -> "\n                    select\n
>                tablespace_name,\n                      initial_extent,\n
>                    next_extent,\n                      min_extents,\n
>                max_extents,\n                      pct_increase\nfrom
> user_segments\nwhere segment_type = 'LOBSEGMENT' and segment_name =
> :segmentName"
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667,
> in __init__
> 14:00:39.243           environment ->
> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
> 14:00:39.243           name -> 'CLOBCOL'
> 14:00:39.243           owner -> 'DWH'
> 14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB',
> 'SYS_LOB0003332346C00002$$', 'YES')
> 14:00:39.243           self -> <cx_OracleObject.Object.Lob object at
> 0xb73d636c>
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line
> 1185, in ObjectIterator
> 14:00:39.243           args -> ()
> 14:00:39.243           classFactory -> <class 'cx_OracleObject.Object.Lob'>
> 14:00:39.243           cursor -> <cx_OracleEx.Cursor on
> <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
> 14:00:39.243           environment ->
> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
> 14:00:39.243           keywordArgs -> {'owner': 'DWH', 'name':
> 'TEST_TEMP_CLOB'}
> 14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB',
> 'SYS_LOB0003332346C00002$$', 'YES')
> 14:00:39.243           statement -> '\n        select\n
>  o.owner,\n          o.column_name,\n          o.table_name,\n
>  o.segment_name,\n          o.in_row\n        from %(p_ViewPrefix)s_lobs
> o\n        %(p_WhereClause)s\n        order by o.column_name'
> 14:00:39.243           tag -> 'Lobs'
> 14:00:39.243           whereClause -> 'where o.owner = :owner and
> o.table_name = :name'
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962,
> in __RetrieveColumns
> 14:00:39.243           c -> ('CLOBCOL', 'CLOB', 'Y', None, None, 4000,
> None)
> 14:00:39.243           cursor -> <cx_OracleEx.Cursor on
> <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
> 14:00:39.243           isPrepared -> None
> 14:00:39.243           lobColumns -> ['CLOBCOL']
> 14:00:39.243           self -> <cx_OracleObject.Object.Table object at
> 0xb73d622c>
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891,
> in __init__
> 14:00:39.243           duration -> 'SYS$SESSION'
> 14:00:39.243           environment ->
> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
> 14:00:39.243           iotType -> None
> 14:00:39.243           name -> 'TEST_TEMP_CLOB'
> 14:00:39.243           owner -> 'DWH'
> 14:00:39.243           partitioned -> 'NO'
> 14:00:39.243           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None,
> None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
> 14:00:39.243           self -> <cx_OracleObject.Object.Table object at
> 0xb73d622c>
> 14:00:39.243           temporary -> 'Y'
> 14:00:39.243         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line
> 1185, in ObjectIterator
> 14:00:39.243           args -> ()
> 14:00:39.244           classFactory -> <class
> 'cx_OracleObject.Object.Table'>
> 14:00:39.244           cursor -> <cx_OracleEx.Cursor on
> <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
> 14:00:39.244           environment ->
> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
> 14:00:39.244           keywordArgs -> {'owner': 'DWH', 'name':
> 'TEST_TEMP_CLOB'}
> 14:00:39.244           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None,
> None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
> 14:00:39.261           statement -> "\n        select\n
>  o.owner,\n          o.table_name,\n          o.tablespace_name,\n
>  o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n
>        o.max_extents,\n          o.pct_increase,\n          o.temporary,\n
>          o.partitioned,\n          o.duration,\n          o.iot_type\n
>    from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and
> secondary = 'N'\n        order by o.owner, o.table_name"
> 14:00:39.261           tag -> 'Default_TABLE'
> 14:00:39.261           whereClause -> 'where o.owner = :owner and
> o.table_name = :name'
> 14:00:39.261         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line
> 123, in ObjectByType
> 14:00:39.261           name -> 'TEST_TEMP_CLOB'
> 14:00:39.261           objectFunction -> <class
> 'cx_OracleObject.Object.Table'>
> 14:00:39.261           owner -> 'DWH'
> 14:00:39.261           self -> <cx_OracleObject.Environment.Environment
> object at 0xb73d618c>
> 14:00:39.261           statement -> "\n        select\n
>  o.owner,\n          o.table_name,\n          o.tablespace_name,\n
>  o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n
>        o.max_extents,\n          o.pct_increase,\n          o.temporary,\n
>          o.partitioned,\n          o.duration,\n          o.iot_type\n
>    from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and
> secondary = 'N'\n        order by o.owner, o.table_name"
> 14:00:39.261           type -> 'TABLE'
> 14:00:39.261           whereClause -> 'where o.owner = :owner and
> o.table_name = :name'
> 14:00:39.261         file
> /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line
> 263, in RetrieveAndExportObject
> 14:00:39.261           objectName -> 'TEST_TEMP_CLOB'
> 14:00:39.261           objectOwner -> 'DWH'
> 14:00:39.262           objectType -> 'TABLE'
> 14:00:39.262           self -> <cx_OracleObject.Describer.Describer object
> at 0xb73d61cc>
> 14:00:39.262         file DescribeObject.py, line 59, in <module>
> 14:00:39.262           Options -> <module 'Options' from
> '/home/matze/gehe/cx_OracleTools-8.0/Options.pyc'>
> 14:00:39.262           connection -> <cx_OracleEx.Connection to
> DWH@DWHT.WORLD>
> 14:00:39.262           cx_LoggingOptions -> <module 'cx_LoggingOptions'
> from '/usr/local/lib/python2.7/dist-packages/cx_LoggingOptions.pyc'>
> 14:00:39.262           cx_OptionParser -> <module 'cx_OptionParser' from
> '/usr/local/lib/python2.7/dist-packages/cx_OptionParser.pyc'>
> 14:00:39.262           cx_OracleObject -> <module 'cx_OracleObject' from
> '/usr/local/lib/python2.7/dist-packages/cx_OracleObject/__init__.pyc'>
> 14:00:39.262           cx_OracleUtils -> <module 'cx_OracleUtils' from
> '/usr/local/lib/python2.7/dist-packages/cx_OracleUtils.pyc'>
> 14:00:39.262           describer -> <cx_OracleObject.Describer.Describer
> object at 0xb73d61cc>
> 14:00:39.262           environment ->
> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
> 14:00:39.262           objectName -> 'TEST_TEMP_CLOB'
> 14:00:39.262           objectOwner -> 'DWH'
> 14:00:39.262           objectType -> 'TABLE'
> 14:00:39.262           options -> <Values at 0xb73d60cc: {'help': None,
> 'asOfScn': None, 'logLevel': 'error', 'wantStorage': True, 'useDbaViews':
> False, 'wantComments': True, 'wantGrants': True, 'wantTablespace': True,
> 'showBanner': None, 'wantViewColumns': False, 'wantSynonyms': False,
> 'wantRelated': True, 'version': None, 'mergeGrants': True, 'schema':
> 'DWH/<mypw>@DWHT.WORLD', 'objectName': 'TEST_TEMP_CLOB', 'maxLongSize':
> 131072, 'fileName': None, 'wantTriggers': True, 'logPrefix': '%t',
> 'asOfTimestamp': None, 'traceback...
> 14:00:39.262           outFile -> <open file '<stdout>', mode 'w' at
> 0xb777b078>
> 14:00:39.262           parser -> <cx_OptionParser.OptionParser instance at
> 0xb73d4c2c>
> 14:00:39.262           sys -> <module 'sys' (built-in)>
>
>
> Thanks and kind regards,
>
> Matthias Meyer
>
> GEHE Informatik Services GmbH & Co. KG, Stuttgart, AG Stuttgart HRA 12167
> Neckartalstr. 155, D-70376 Stuttgart
> Geschäftsführer: Rainer Baumgärtner, Till Rausch, Erwin Schiller
> persönlich haftende Gesellschafterin: GEHE Informatik Services-Verwaltungs
> GmbH, Stuttgart, AG Stuttgart 15843
> Weiterer Gesellschafter: GEHE Pharma Handel GmbH, Stuttgart, AG Stuttgart
> HRB 14591
> USt-IdNr.: DE 811 655 252
>
> ______________________________________________________________________
> This email has been scanned by the Symantec Email Security.cloud service.
> For more information please visit http://www.symanteccloud.com
> ______________________________________________________________________
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://ad.doubleclick.net/clk;258768047;13503038;j?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] LOB in temporary table

[Meyer M. <Mat...@ge...>]

Hi all,

I’m having difficulties describing a temporary table, which contains a CLOB column.
The storage segment of the lob is not found and the export fails with "Unable to locate LOB segment"

I dug around the oracle docs and found this:
"Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created"
(http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i16096)

This can be verified, as SYS.DBA_SEGMENT does not contain a line for the SEGMENT_NAME of the LOB in SYS.DBA_LOBS.

My guess is, we need a new class in Object.py that represents LOBs without storage segments and is created in the _RetrieveColumns()-method of the Table-class if the table is temporary. But I'm not sure about this...


You can reproduce this as following:

Create the temporary table:

CREATE GLOBAL TEMPORARY TABLE TEST_TEMP_CLOB (
  key1 NUMBER,
  clobcol CLOB
)
ON COMMIT PRESERVE ROWS
RESULT_CACHE ( MODE DEFAULT)
NOCACHE;



Check the sys-views:
SELECT *
  FROM SYS.DBA_SEGMENTS
 WHERE SEGMENT_NAME = (SELECT SEGMENT_NAME
                         FROM SYS.DBA_LOBS
                        WHERE COLUMN_NAME = 'CLOBCOL');


no rows selected



Export the object using cx_OracleTools:
$ > python DescribeObject.py --schema=DWH/<mypw>@DWHT.WORLD -t TEST_TEMP_CLOB
14:00:39.242 Python exception encountered:
14:00:39.242     Message: Unable to locate LOB segment DWH.CLOBCOL
14:00:39.242     Template Id: 0
14:00:39.242     Arguments:
14:00:39.242         name => 'CLOBCOL'
14:00:39.242         owner => 'DWH'
14:00:39.242     Traceback:
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699, in __RetrieveSegment
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667, in __init__
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962, in __RetrieveColumns
14:00:39.242         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891, in __init__
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line 123, in ObjectByType
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line 263, in RetrieveAndExportObject
14:00:39.243         file DescribeObject.py, line 59, in <module>
14:00:39.243     Details:
14:00:39.243         Local Variables:
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 699, in __RetrieveSegment
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           isPrepared -> None
14:00:39.243           row -> None
14:00:39.243           self -> <cx_OracleObject.Object.Lob object at 0xb73d636c>
14:00:39.243           statement -> "\n                    select\n                      tablespace_name,\n                      initial_extent,\n                      next_extent,\n                      min_extents,\n                      max_extents,\n                      pct_increase\nfrom user_segments\nwhere segment_type = 'LOBSEGMENT' and segment_name = :segmentName"
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 667, in __init__
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           name -> 'CLOBCOL'
14:00:39.243           owner -> 'DWH'
14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB', 'SYS_LOB0003332346C00002$$', 'YES')
14:00:39.243           self -> <cx_OracleObject.Object.Lob object at 0xb73d636c>
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243           args -> ()
14:00:39.243           classFactory -> <class 'cx_OracleObject.Object.Lob'>
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           keywordArgs -> {'owner': 'DWH', 'name': 'TEST_TEMP_CLOB'}
14:00:39.243           row -> ('DWH', 'CLOBCOL', 'TEST_TEMP_CLOB', 'SYS_LOB0003332346C00002$$', 'YES')
14:00:39.243           statement -> '\n        select\n          o.owner,\n          o.column_name,\n          o.table_name,\n          o.segment_name,\n          o.in_row\n        from %(p_ViewPrefix)s_lobs o\n        %(p_WhereClause)s\n        order by o.column_name'
14:00:39.243           tag -> 'Lobs'
14:00:39.243           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 962, in __RetrieveColumns
14:00:39.243           c -> ('CLOBCOL', 'CLOB', 'Y', None, None, 4000, None)
14:00:39.243           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.243           isPrepared -> None
14:00:39.243           lobColumns -> ['CLOBCOL']
14:00:39.243           self -> <cx_OracleObject.Object.Table object at 0xb73d622c>
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 891, in __init__
14:00:39.243           duration -> 'SYS$SESSION'
14:00:39.243           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.243           iotType -> None
14:00:39.243           name -> 'TEST_TEMP_CLOB'
14:00:39.243           owner -> 'DWH'
14:00:39.243           partitioned -> 'NO'
14:00:39.243           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None, None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
14:00:39.243           self -> <cx_OracleObject.Object.Table object at 0xb73d622c>
14:00:39.243           temporary -> 'Y'
14:00:39.243         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Object.py, line 1185, in ObjectIterator
14:00:39.243           args -> ()
14:00:39.244           classFactory -> <class 'cx_OracleObject.Object.Table'>
14:00:39.244           cursor -> <cx_OracleEx.Cursor on <cx_OracleEx.Connection to DWH@DWHT.WORLD>>
14:00:39.244           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.244           keywordArgs -> {'owner': 'DWH', 'name': 'TEST_TEMP_CLOB'}
14:00:39.244           row -> ('DWH', 'TEST_TEMP_CLOB', None, None, None, None, None, None, 'Y', 'NO', 'SYS$SESSION', None)
14:00:39.261           statement -> "\n        select\n          o.owner,\n          o.table_name,\n          o.tablespace_name,\n          o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n          o.max_extents,\n          o.pct_increase,\n          o.temporary,\n          o.partitioned,\n          o.duration,\n          o.iot_type\n        from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and secondary = 'N'\n        order by o.owner, o.table_name"
14:00:39.261           tag -> 'Default_TABLE'
14:00:39.261           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.261         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Environment.py, line 123, in ObjectByType
14:00:39.261           name -> 'TEST_TEMP_CLOB'
14:00:39.261           objectFunction -> <class 'cx_OracleObject.Object.Table'>
14:00:39.261           owner -> 'DWH'
14:00:39.261           self -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.261           statement -> "\n        select\n          o.owner,\n          o.table_name,\n          o.tablespace_name,\n          o.initial_extent,\n          o.next_extent,\n          o.min_extents,\n          o.max_extents,\n          o.pct_increase,\n          o.temporary,\n          o.partitioned,\n          o.duration,\n          o.iot_type\n        from %(p_ViewPrefix)s_tables o\n        %(p_WhereClause)s\n          and secondary = 'N'\n        order by o.owner, o.table_name"
14:00:39.261           type -> 'TABLE'
14:00:39.261           whereClause -> 'where o.owner = :owner and o.table_name = :name'
14:00:39.261         file /usr/local/lib/python2.7/dist-packages/cx_OracleObject/Describer.py, line 263, in RetrieveAndExportObject
14:00:39.261           objectName -> 'TEST_TEMP_CLOB'
14:00:39.261           objectOwner -> 'DWH'
14:00:39.262           objectType -> 'TABLE'
14:00:39.262           self -> <cx_OracleObject.Describer.Describer object at 0xb73d61cc>
14:00:39.262         file DescribeObject.py, line 59, in <module>
14:00:39.262           Options -> <module 'Options' from '/home/matze/gehe/cx_OracleTools-8.0/Options.pyc'>
14:00:39.262           connection -> <cx_OracleEx.Connection to DWH@DWHT.WORLD>
14:00:39.262           cx_LoggingOptions -> <module 'cx_LoggingOptions' from '/usr/local/lib/python2.7/dist-packages/cx_LoggingOptions.pyc'>
14:00:39.262           cx_OptionParser -> <module 'cx_OptionParser' from '/usr/local/lib/python2.7/dist-packages/cx_OptionParser.pyc'>
14:00:39.262           cx_OracleObject -> <module 'cx_OracleObject' from '/usr/local/lib/python2.7/dist-packages/cx_OracleObject/__init__.pyc'>
14:00:39.262           cx_OracleUtils -> <module 'cx_OracleUtils' from '/usr/local/lib/python2.7/dist-packages/cx_OracleUtils.pyc'>
14:00:39.262           describer -> <cx_OracleObject.Describer.Describer object at 0xb73d61cc>
14:00:39.262           environment -> <cx_OracleObject.Environment.Environment object at 0xb73d618c>
14:00:39.262           objectName -> 'TEST_TEMP_CLOB'
14:00:39.262           objectOwner -> 'DWH'
14:00:39.262           objectType -> 'TABLE'
14:00:39.262           options -> <Values at 0xb73d60cc: {'help': None, 'asOfScn': None, 'logLevel': 'error', 'wantStorage': True, 'useDbaViews': False, 'wantComments': True, 'wantGrants': True, 'wantTablespace': True, 'showBanner': None, 'wantViewColumns': False, 'wantSynonyms': False, 'wantRelated': True, 'version': None, 'mergeGrants': True, 'schema': 'DWH/<mypw>@DWHT.WORLD', 'objectName': 'TEST_TEMP_CLOB', 'maxLongSize': 131072, 'fileName': None, 'wantTriggers': True, 'logPrefix': '%t', 'asOfTimestamp': None, 'traceback...
14:00:39.262           outFile -> <open file '<stdout>', mode 'w' at 0xb777b078>
14:00:39.262           parser -> <cx_OptionParser.OptionParser instance at 0xb73d4c2c>
14:00:39.262           sys -> <module 'sys' (built-in)>


Thanks and kind regards,

Matthias Meyer

GEHE Informatik Services GmbH & Co. KG, Stuttgart, AG Stuttgart HRA 12167
Neckartalstr. 155, D-70376 Stuttgart
Geschäftsführer: Rainer Baumgärtner, Till Rausch, Erwin Schiller
persönlich haftende Gesellschafterin: GEHE Informatik Services-Verwaltungs GmbH, Stuttgart, AG Stuttgart 15843
Weiterer Gesellschafter: GEHE Pharma Handel GmbH, Stuttgart, AG Stuttgart HRB 14591
USt-IdNr.: DE 811 655 252

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

Re: [cx-oracle-users] CentOS 5, Oracle 11g RPM Binary install - libclntsh.so.11.1:

[Jeff M. <je...@te...>]

On 2012-09-11, at 7:19 PM, James C. McPherson <Jam...@or...> wrote:

> On 12/09/12 05:54 AM, Jeff MacDonald wrote:
>> Hi,
>> 
>> I have a fully functional Oracle 11g server install. Its a 32bit machine running CentOS 5.8
> ...
>> [jmacdonald@devoracle python]$ cat env.sh
>> ORACLE_BASE=/u01/app/oracle
>> ORACLE_SID=orcl
>> ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
>> PATH=$PATH:$ORACLE_HOME/bin
>> LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/
>> export ORACLE_BASE ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH
>> 
>> Then when I run my teeny tiny testing script, I get the following:
> ...
>> I'm kinda stuck. Any suggestions?
> 
> I think you need to have a copy of the lib in your ORACLE_HOME
> as well as in $ORACLE_HOME/lib. That's the config I've got, which
> appears to have been created by the instant client installer
> (based on the timestamps).


Copying libclntsh.so.11.1 to the $ORACLE_HOME worked.  Thanks!

jeff.



> 
> 
> James C. McPherson
> --
> Oracle
> http://www.jmcpdotcom.com/blog

Re: [cx-oracle-users] CentOS 5, Oracle 11g RPM Binary install - libclntsh.so.11.1:

[Jeff M. <je...@te...>]

See below, already done. Also, there isn't a lib32 directory anywhere.


Jeff.

On 2012-09-11, at 5:15 PM, Dwayne King <dw...@kr...> wrote:

> I've had this in the past.  If I remember correctly, adding the $ORACLE_HOME/lib to LD_LIBRARY_PATH fixed it
> 
> Since you're running 32 bit (having done this myself in a long time), check to see if there's a lib32 directory underneath $ORACLE_HOME.  If so, you may need to put that in LD_LIBRARY_PATH instead.
> 
> HTH
> 
> Dwayne
> 
> 
> 
> On Tue, Sep 11, 2012 at 3:54 PM, Jeff MacDonald <je...@te...> wrote:
> Hi,
> 
> I have a fully functional Oracle 11g server install. Its a 32bit machine running CentOS 5.8
> 
> I installed this : http://prdownloads.sourceforge.net/cx-oracle/cx_Oracle-5.1.2-11g-py24-1.i386.rpm?download
> 
> By doing "rpm -i cx_Oracle-5.1.2-11g-py24-1.i386.rpm"
> 
> Then as a regular user I set the following environment variables (i run source env.sh..)
> 
> [jmacdonald@devoracle python]$ cat env.sh
> ORACLE_BASE=/u01/app/oracle
> ORACLE_SID=orcl
> ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
> PATH=$PATH:$ORACLE_HOME/bin
> LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/
> export ORACLE_BASE ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH
> 
> Then when I run my teeny tiny testing script, I get the following:
> 
> [jmacdonald@devoracle python]$ python connect.py
> Traceback (most recent call last):
>   File "connect.py", line 2, in ?
>     import cx_Oracle
> ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory
> [jmacdonald@devoracle python]$ locate libclntsh.so.11.1
> /u01/app/oracle/product/11.2.0.3/dbhome_1/inventory/Scripts/ext/lib/libclntsh.so.11.1
> /u01/app/oracle/product/11.2.0.3/dbhome_1/inventory/backup/2012-01-30_10-26-09PM/Scripts/ext/lib/libclntsh.so.11.1
> /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclntsh.so.11.1
> 
> I'm kinda stuck. Any suggestions?
> 
> Thanks
> 
> 
> 
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and 
> threat landscape has changed and how IT managers can respond. Discussions 
> will include endpoint security, mobile security and the latest in malware 
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/_______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] CentOS 5, Oracle 11g RPM Binary install - libclntsh.so.11.1:

[James C. M. <Jam...@or...>]

On 12/09/12 05:54 AM, Jeff MacDonald wrote:
> Hi,
>
> I have a fully functional Oracle 11g server install. Its a 32bit machine running CentOS 5.8
...
> [jmacdonald@devoracle python]$ cat env.sh
> ORACLE_BASE=/u01/app/oracle
> ORACLE_SID=orcl
> ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
> PATH=$PATH:$ORACLE_HOME/bin
> LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/
> export ORACLE_BASE ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH
>
> Then when I run my teeny tiny testing script, I get the following:
...
> I'm kinda stuck. Any suggestions?

I think you need to have a copy of the lib in your ORACLE_HOME
as well as in $ORACLE_HOME/lib. That's the config I've got, which
appears to have been created by the instant client installer
(based on the timestamps).


James C. McPherson
--
Oracle
http://www.jmcpdotcom.com/blog

Re: [cx-oracle-users] CentOS 5, Oracle 11g RPM Binary install - libclntsh.so.11.1:

[Dwayne K. <dw...@kr...>]

I've had this in the past.  If I remember correctly, adding the
$ORACLE_HOME/lib to LD_LIBRARY_PATH fixed it

Since you're running 32 bit (having done this myself in a long time), check
to see if there's a lib32 directory underneath $ORACLE_HOME.  If so, you
may need to put that in LD_LIBRARY_PATH instead.

HTH

Dwayne



On Tue, Sep 11, 2012 at 3:54 PM, Jeff MacDonald <je...@te...> wrote:

> Hi,
>
> I have a fully functional Oracle 11g server install. Its a 32bit machine
> running CentOS 5.8
>
> I installed this :
> http://prdownloads.sourceforge.net/cx-oracle/cx_Oracle-5.1.2-11g-py24-1.i386.rpm?download
>
> By doing "rpm -i cx_Oracle-5.1.2-11g-py24-1.i386.rpm"
>
> Then as a regular user I set the following environment variables (i run
> source env.sh..)
>
> [jmacdonald@devoracle python]$ cat env.sh
> ORACLE_BASE=/u01/app/oracle
> ORACLE_SID=orcl
> ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
> PATH=$PATH:$ORACLE_HOME/bin
> LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/
> export ORACLE_BASE ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH
>
> Then when I run my teeny tiny testing script, I get the following:
>
> [jmacdonald@devoracle python]$ python connect.py
> Traceback (most recent call last):
>   File "connect.py", line 2, in ?
>     import cx_Oracle
> ImportError: libclntsh.so.11.1: cannot open shared object file: No such
> file or directory
> [jmacdonald@devoracle python]$ locate libclntsh.so.11.1
> /u01/app/oracle/product/
> 11.2.0.3/dbhome_1/inventory/Scripts/ext/lib/libclntsh.so.11.1
>
> /u01/app/oracle/product/11.2.0.3/dbhome_1/inventory/backup/2012-01-30_10-26-09PM/Scripts/ext/lib/libclntsh.so.11.1
> /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclntsh.so.11.1
>
> I'm kinda stuck. Any suggestions?
>
> Thanks
>
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] CentOS 5, Oracle 11g RPM Binary install - libclntsh.so.11.1:

[Jeff M. <je...@te...>]

Hi,

I have a fully functional Oracle 11g server install. Its a 32bit machine running CentOS 5.8

I installed this : http://prdownloads.sourceforge.net/cx-oracle/cx_Oracle-5.1.2-11g-py24-1.i386.rpm?download

By doing "rpm -i cx_Oracle-5.1.2-11g-py24-1.i386.rpm"

Then as a regular user I set the following environment variables (i run source env.sh..)

[jmacdonald@devoracle python]$ cat env.sh 
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=orcl
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/
export ORACLE_BASE ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH

Then when I run my teeny tiny testing script, I get the following:

[jmacdonald@devoracle python]$ python connect.py 
Traceback (most recent call last):
  File "connect.py", line 2, in ?
    import cx_Oracle
ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[jmacdonald@devoracle python]$ locate libclntsh.so.11.1
/u01/app/oracle/product/11.2.0.3/dbhome_1/inventory/Scripts/ext/lib/libclntsh.so.11.1
/u01/app/oracle/product/11.2.0.3/dbhome_1/inventory/backup/2012-01-30_10-26-09PM/Scripts/ext/lib/libclntsh.so.11.1
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclntsh.so.11.1

I'm kinda stuck. Any suggestions?

Thanks

Re: [cx-oracle-users] This must be something simple....

[Dwayne K. <dw...@kr...>]

Mark,

Thanks for the hint - this may have proved useful.

When I do what you suggest and use strace to follow what's going on, I
noticed two things:
1) the installation of the cx_Oracle rpm seems to have dropped (only?) two
files in /usr/lib/python3.2/site-packages:   cx_Oracle.cpython-32mu.so and
cx_Oracle-5.1.2-py3.2.egg-info
Note that these are in the site-packages folder itself - not a subfolder
2) the output of strace shows that it looks for a slightly different name:
cx_Oracle.cpython-32m.so  (not the absence of the "u")

So....a few followup questions:
1) should there be more files there?  Or is it really just the .so?  The
strace seems to show that it also looks for:
- cx_Oracle
- cx_Oraclemodule.cpython-32m.so
- cx_Oracle.abi3.so
- cx_Oraclemodule.abi3.so
- cx_Oracle.so
- cx_Oraclemodule.so
- cx_Oracle.py
- cx_Oracle.pyc

2) Should I be able to create a symlink to resolve at least the one with a
slightly different name?

I'm at a bit of a loss simply because I don't have a working cx_Oracle on a
linux box anywhere to compare to.

Thanks again - your time is greatly appreciated.

Dwayne


On Mon, Sep 10, 2012 at 1:20 PM, Mark Harrison <mh...@pi...> wrote:

> On 9/10/12 8:05 AM, Dwayne King wrote:
> > I will be extremely grateful if someone can point what I'm missing.
>
> I don't have any specific suggestions, but I've always found strace to be
> helpful in diagnosing library problems.
>
> echo import cx_Oracle >foo.py
> strace python foo.py
>
> You can look in the output for the files you installed and see if they're
> being found, or if there's any other oddness in loading them.
>
> HTH!
>
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] This must be something simple....

[Mark H. <mh...@pi...>]

On 9/10/12 8:05 AM, Dwayne King wrote:
> I will be extremely grateful if someone can point what I'm missing.

I don't have any specific suggestions, but I've always found strace to be
helpful in diagnosing library problems.

echo import cx_Oracle >foo.py
strace python foo.py

You can look in the output for the files you installed and see if they're
being found, or if there's any other oddness in loading them.

HTH!

Re: [cx-oracle-users] cx_Oracle and libclntsh(FreeBSD)

[Glyph <gl...@tw...>]

On Sep 10, 2012, at 3:27 AM, ix...@po... wrote:

> I want to use cx_Oracle for connect to Oracle DB. I using FreeBSD 9.0(i386) as client.
> After I installed all the necessary ports for linux-emulations(32bit fedora emul. port) and 
> oracle client 10.2.0.3, I can't make setup.py build:

I don't know the specifics here, either related to FreeBSD or Oracle, but I do know that FreeBSD and Linux binaries are different.  As I understand it, FreeBSD's linux compatibility layer will let you run Linux executables, but it won't let arbitrary FreeBSD programs load arbitrary linux shared objects into their own address space.  In this case, you'll probably need a full Linux build of Python, so that you can import the module as if everything were running on Linux.

Good luck,

-g

[cx-oracle-users] This must be something simple....

[Dwayne K. <dw...@kr...>]

OK, I KNOW I'm missing something simple here. Please point out the simple
thing that I'm missing.

I've been setting up a system and I have Oracle installed and running, and
upgraded Python to 3.2 However whenever I try to install and use cx_Oracle
it can't seem to find the package. At first I thought it might have been
something related to my distrib (CentOS) so I changed to RHEL and I get
exactly the same thing!

So as you can see from the output below, the install of cx_Oracle was
uneventful:


[root@dblinux uploads]# rpm -Uvh cx_Oracle-5.1.1-11g-py32-1.x86_64.rpm
Preparing...                ###########################################
[100%]
   1:cx_Oracle              ###########################################
[100%]

[root@dblinux /]# find . -name cx_*
./uploads/cx_Oracle-5.1.1-11g-py32-1.x86_64.rpm
./usr/share/doc/cx_Oracle-5.1.1
./usr/lib/python3.2/site-packages/cx_Oracle.cpython-32mu.so
./usr/lib/python3.2/site-packages/cx_Oracle-5.1.1-py3.2.egg-info

Oracle is installed and running:


[oracle@dblinux ~]$ sqlplus system/config

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 6 16:42:42 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL>

You can see that I have my environment set up so that PYTHONPATH includes
the folder where the cx_Oracle files were found. Also verified by printing
sys.path:

CODE: SELECT ALL<http://www.python-forum.org/pythonforum/viewtopic.php?f=16&t=36468#>
[oracle@dblinux ~]$ env|grep PYTH
PYTHONPATH=/usr/local/lib/python32.zip:/opt/Python-3.2.3/Lib:/opt/Python-3.2.3/Lib/plat-linux2:/opt/Python-3.2.3/build/lib.linux-x86_64-3.2:/usr/local/lib/python3.2/site-packages:/usr/lib/python3.2/site-packages

[oracle@dblinux ~]$ python
Python 3.2.3 (default, Sep  6 2012, 16:12:29)
[GCC 4.4.6 20110731 (Red Hat 4.4.6-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> print (sys.path)
['', '/usr/local/lib/python32.zip', '/opt/Python-3.2.3/Lib',
'/opt/Python-3.2.3/Lib/plat-linux2',
'/opt/Python-3.2.3/build/lib.linux-x86_64-3.2',
'/usr/local/lib/python3.2/site-packages',
'/usr/lib/python3.2/site-packages', '/opt/python/Lib',
'/opt/python/Lib/plat-linux2', '/opt/python/build/lib.linux-x86_64-3.2']
>>> import cx_Oracle
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named cx_Oracle

Yet, for some reason it STILL can't see cx_Oracle. This is the first
non-windoze box I've set cx_Oracle up on, so I'm not sure if there is
something platform specific that I'm missing.

I will be extremely grateful if someone can point what I'm missing.

Dwayne

[cx-oracle-users] cx_Oracle and libclntsh(FreeBSD)

[<ix...@po...>]

Hello!I want to use cx_Oracle for connect to Oracle DB. I using FreeBSD 9.0(i386) as client.After I installed all the necessary ports for linux-emulations(32bit fedora emul. port) and oracle client 10.2.0.3, I can't make setup.py build:
antares[cx_Oracle-5.1.2]# python setup.py build
running buildrunning build_extbuilding 'cx_Oracle' extensioncreating buildcreating build/temp.freebsd-9.0-RELEASE-i386-2.7-10gcc -fno-strict-aliasing -O2 -pipe -fno-strict-aliasing -DNDEBUG -O2 -pipe -fno-strict-aliasing -fPIC -I/compat/linux/usr/include/oracle/10.2.0.3/client -I/usr/local/include/python2.7 -c cx_Oracle.c -o build/temp.freebsd-9.0-RELEASE-i386-2.7-10g/cx_Oracle.o -DBUILD_VERSION=5.1.2creating build/lib.freebsd-9.0-RELEASE-i386-2.7-10gcc -shared -pthread build/temp.freebsd-9.0-RELEASE-i386-2.7-10g/cx_Oracle.o -L/compat/linux/usr/lib/oracle/10.2.0.3/client/lib -lclntsh -o build/lib.freebsd-9.0-RELEASE-i386-2.7-10g/cx_Oracle.so/compat/linux/usr/lib/oracle/10.2.0.3/client/lib/libclntsh.so: could not read symbols: File in wrong formaterror: command 'cc' failed with exit status 1
My env:ORACLE_HOME=/compat/linux/usr/lib/oracle/10.2.0.3/client/binLD_LIBRARY_PATH=/compat/linux/usr/lib/oracle/10.2.0.3/client/lib
antares[cx_Oracle-5.1.2]# uname -aFreeBSD antares 9.0-RELEASE FreeBSD 9.0-RELEASE #0: Mon May 14 21:15:32 MSK 2012     root@antares:/usr/obj/usr/src/sys/KERNEL  i386
antares[lib]# gcc -vUsing built-in specs.Target: i386-undermydesk-freebsdConfigured with: FreeBSD/i386 system compilerThread model: posixgcc version 4.2.1 20070831 patched [FreeBSD]
antares[lib]# python -v...Python 2.7.3 (default, Jul  9 2012, 12:11:34)[GCC 4.2.1 20070831 patched [FreeBSD]] on freebsd9

I checked all libs with 'objdump' - all libs are 32bit. Library libclntsh.so linked to libclntsh.so.10.1. 
As I understand, cx_Oracle does not like that libclntsh.so is dynamic? Necessary static library? Or not? But oracle client have only *.so. My Oracle DB server running from AIX 64bit and I could not take libclntsh.a.As I solve this problem?Thanks.

Re: [cx-oracle-users] blob streaming, possible improvement

[Walter D. <wa...@li...>]

On 31.08.12 15:47, Amaury Forgeot d'Arc wrote:

> Hi,
>
> 2012/8/31 Joram Agten <jor...@gm...>:
>> I had to upload 20GB of documents from an ftp site, to a table with a BLOB
>> column. In order to do this in a streaming fashion I had to write a little helper
>> class for the BLOB stream.
>
> This is a good idea!
>
>>      def write(self, data, offset=None):
> But this "offset" is not standard for file-like objects.
>
> In this case I'd suggest your class to inherit from io.RawIOBase,
> and follow its interface.
>
> Here you'd simply have to implement write(), override writable() to return True,
> and implement seek() and tell() just by keeping an internal ._offset member.

When we get a file-like object for writing to a [BC]LOG, can we have one 
for reading too?

Servus,
    Walter

Re: [cx-oracle-users] blob streaming, possible improvement

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

Hi,

2012/8/31 Joram Agten <jor...@gm...>:
> I had to upload 20GB of documents from an ftp site, to a table with a BLOB
> column. In order to do this in a streaming fashion I had to write a little helper
> class for the BLOB stream.

This is a good idea!

>     def write(self, data, offset=None):
But this "offset" is not standard for file-like objects.

In this case I'd suggest your class to inherit from io.RawIOBase,
and follow its interface.

Here you'd simply have to implement write(), override writable() to return True,
and implement seek() and tell() just by keeping an internal ._offset member.

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] blob streaming, possible improvement

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

Hello,

I had to upload 20GB of documents from an ftp site, to a table with a BLOB
column.
In order to do this in a streaming fashion I had to write a little helper
class for the BLOB stream.
(see code example below)

It would be handy if the BLOB write function did not need an offset
parameter.
So writing without offset specified moves it automatically along ( offset
+= len(data) ) and the BLOB object remembers the last written offset (as
the instantiated Helper class in my example)

I think that is more in sync with the other file-type like write functions
(see also StringIO as an example)

Hopefully this is easy to integrate in cx_Oracle.
Thanks in advance
Joram



import cx_Oracle
import ftplib

dsn = "scott/tiger"
connection = cx_Oracle.connect(dsn)
cursor = connection.cursor()

ftp = ftplib.FTP()
ftp.connect("host", 21)
ftp.login("user", "passwd")

class Helper(object):
    def __init__(self, writer):
        print("initializing helper with writer {w}".format(w=writer))
        self.writer = writer
        self.offset = 1

    def write(self, data, offset=None):
        if not offset:
            offset = self.offset
        print("calling write on helper with {len} bytes of date, current
offset {offset}".format(len=len(data), offset=self.offset))
        self.writer.write(data, offset)
        self.offset += len(data)

try:
    attachment_id = 1
    file_name = "bigfile.txt"
    bigFileVar = cursor.var(cx_Oracle.BLOB)
    cursor.execute("""UPDATE ta_cs_attachments
                         SET file_data = empty_blob()
                           , upload_processed_flg = 'Y'
                       WHERE attachment_id = :attachment_id
                      RETURNING file_data INTO :file_data"""
                   , {'attachment_id' : attachment_id, "file_data":
bigFileVar})
    blob = bigFileVar.getvalue()
    helper = Helper(blob)
    ftp.retrbinary("RETR " + file_name, helper.write, 65536)
    connection.commit()

except cx_Oracle.DatabaseError, exc:
    error, = exc.args
    print(error)
except ftplib.error_temp, msg:
    print("error in uploading, {e}".format(e=msg))

cursor.close()
connection.close()
ftp.quit()