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

[cx-oracle-users] cx_Oracle 4.3

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

What is cx_Oracle?

cx_Oracle is a Python extension module that allows access to Oracle and
conforms to the Python database API 2.0 specifications with a few
exceptions.


Where do I get it?

http://starship.python.net/crew/atuining


What's new?

1) Added preliminary support for fetching Oracle objects (SQL types)
as requested by Kristof Beyls (who kindly provided an initial patch).
Additional work needs to be done to support binding and updating
objects but the basic structure is now in place.

2) Added connection.maxBytesPerCharacter which indicates the maximum
number of bytes each character can use; use this value to also
determine the size of local buffers in order to handle discrepancies
between the client character set and the server character set. Thanks
to Andreas Mock for providing the initial patch and working with me to
resolve this issue.

3) Added support for querying native floats in Oracle 10g as requested
by Danny Boxhoorn.

4) Add support for temporary LOB variables created via PL/SQL instead
of only directly by cx_Oracle; thanks to Henning von Bargen for
discovering this problem.

5) Added support for specifying variable types using the builtin types
int, float, str and datetime.date which allows for finer control of
what type of Python object is returned from cursor.callfunc() for
example.

6) Added support for passing booleans to callproc() and callfunc() as
requested by Anana Aiyer.

7) Fixed support for 64-bit environments in Python 2.5.

8) Thanks to Filip Ballegeer and a number of his co-workers, an
intermittent crash was tracked down; specifically, if a connection is
closed, then the call to OCIStmtRelease() will free memory twice.
Preventing the call when the connection is closed solves the problem.

Anthony Tuininga

Re: [cx-oracle-users] [Fwd: string_literal in cx_Oracle]

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

No, there is no such thing in cx_Oracle. I'm including here, however,
something that I wrote for the purposes of generating SQL statements
which can be run through (for example) SQL*Plus after suitable
manipulation -- or for storage in a source control system.

def QuotedString(value):
    """Return the value quoted as needed."""
    return "'%s'" % value.replace("'", "''")

def GetConstantRepr(value):
    """Return the value represented as an Oracle constant."""
    if value is None:
        return "null"
    elif isinstance(value, (int, long, float)):
        return str(value)
    elif isinstance(value, basestring):
        parts = []
        lastPos = 0
        for i, char in enumerate(value):
            if not char.isalnum() and char != " " \
                    and char not in string.punctuation:
                temp = value[lastPos:i]
                lastPos = i + 1
                if temp:
                    parts.append(QuotedString(temp))
                parts.append("chr(%s)" % ord(char))
        temp = value[lastPos:]
        if temp:
            parts.append(QuotedString(temp))
        return " || ".join(parts)
    elif isinstance(value, datetime.datetime):
        return "to_date('%s', 'YYYY-MM-DD HH24:MI:SS')" % \
                value.strftime("%Y-%m-%d %H:%M:%S")
    elif isinstance(value, datetime.date):
        return "to_date('%s', 'YYYY-MM-DD')" % value.strftime("%Y-%m-%d")
    raise "Cannot convert %r to an Oracle constant representation." % value

Hopefully this is of use to you. This can be found in the
cx_OracleUtils module in the cx_PyOracleLib project which you can get
from here:

http://starship.python.net/crew/atuining/cx_OracleTools/index.html

On 3/7/07, Mark Harrison <mh...@pi...> wrote:
>  From a colleague who is converting some mysql code to oracle:
> I've mentioned the desirability of binding rather than quoting,
> but they may need to run in interoperable mode for a while
> before totally switching over.
>
>  >The python MySQL module has a string_literal() function
>  >that takes a string and returns a string with appropriate characters
>  >escaped so that it can be used in an SQL statement.
>  >
>  >For example, if you wanted to store the string (He said, "Hello."),
>  >string_literal() would return (He said, \"Hello.\")
>  >
>  >Is there such a thing in cx_Oracle?
>
> Many TIA,
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] [Fwd: string_literal in cx_Oracle]

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

 From a colleague who is converting some mysql code to oracle:
I've mentioned the desirability of binding rather than quoting,
but they may need to run in interoperable mode for a while
before totally switching over.

 >The python MySQL module has a string_literal() function
 >that takes a string and returns a string with appropriate characters
 >escaped so that it can be used in an SQL statement.
 >
 >For example, if you wanted to store the string (He said, "Hello."),
 >string_literal() would return (He said, \"Hello.\")
 >
 >Is there such a thing in cx_Oracle?

Many TIA,
Mark

-- 
Mark Harrison
Pixar Animation Studios

Re: [cx-oracle-users] Problem with importing BLOBs

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

On 2/22/07, Christian Klinger <ckl...@no...> wrote:
> Hi List,
>
> i try to add an PDF in a blob field in oracle.
>
> This is  my insert - statement
>
>
> def getStream(filename):
>      """ Helper Methode um einen Filestream zu bekommen """
>      file = open(filename, 'r')
>      stream = file.read()
>      file.close()
>      return stream

First, you might want to consider using this instead

file = open(filename, "rb")

since PDFs can most definitely contain binary characters.

> cursor.setinputsizes( PDF_DOKUMENT=cx_Oracle.CLOB )

I thought you were trying to insert a BLOB? Wouldn't that be more
reasonable for PDF documents anyway?

> qs = """INSERT INTO EXTRANET_BX90DALEUV (BERICHTS_NR, DOKUMENTDATUM,
>
>                                           PDF_DOKUMENT, XSL_FILENAME)
>              VALUES ('%s','%s',%s,'%s')""" %(kwargs.get('BERICHTS_NR'),
>                          kwargs.get('DOKUMENTDATUM'),
>
> encodestring(getStream(kwargs.get('PDF_DOKUMENT'))),
>                          kwargs.get('XSL_FILENAME'),
>                          )

You want to do the following instead:

qs = "insert into ...... values (:BERICHTS_NR, :DOKUMENTDATUM,
:PDF_DOKUMENT, :XSL_FILENAME)"
cursor.execute(qs, kwargs)

(assuming that kwargs contains only the four entries referenced in the
insert statement. In other words, use bind variables, not strings.

> If i run the script i got these error!
>
>    File "impuaz.py", line 39, in imp_uaz
>      cursor.execute(qs)
> cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
>
>
> Any tips?
>
> Thx Christian
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Philip SION/cg13 est absent(e).

[<phi...@cg...>]

Je serai absent(e) =E0 partir du  19/02/2007 de retour le 23/02/2007.

Merci de limiter vos messages pendant mon absence.

Pour les probl=E8mes techniques, la boite aux lettres
srt...@cg... reste trait=E9e pendant mon absence.

Merci.=

[cx-oracle-users] Problem with importing BLOBs

[Christian K. <ckl...@no...>]

Hi List,

i try to add an PDF in a blob field in oracle.

This is  my insert - statement


def getStream(filename):
     """ Helper Methode um einen Filestream zu bekommen """
     file = open(filename, 'r')
     stream = file.read()
     file.close()
     return stream



cursor.setinputsizes( PDF_DOKUMENT=cx_Oracle.CLOB )
qs = """INSERT INTO EXTRANET_BX90DALEUV (BERICHTS_NR, DOKUMENTDATUM, 

                                          PDF_DOKUMENT, XSL_FILENAME)
             VALUES ('%s','%s',%s,'%s')""" %(kwargs.get('BERICHTS_NR'),
                         kwargs.get('DOKUMENTDATUM'),
 
encodestring(getStream(kwargs.get('PDF_DOKUMENT'))),
                         kwargs.get('XSL_FILENAME'),
                         )


If i run the script i got these error!

   File "impuaz.py", line 39, in imp_uaz
     cursor.execute(qs)
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number


Any tips?

Thx Christian

Re: [cx-oracle-users] curs.fetch() that grabs LOB data?

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

Anthony Tuininga wrote:
> On 2/15/07, Walter Dörwald <wa...@li...> wrote:
>> Mark Harrison wrote:
>>
>>> curs.fetch will return a LOB object for CLOB, etc. columns.
>>> It's easy enough to iterate over the list of returned columns
>>> and fetch the LOB data, but is there there some pre-built variant
>>> of fetch that will return the CLOB data as a string?
> 
> No. Since CLOB and BLOB columns can be very large this has not been
> done. I realize that there are some instances when this is not true
> and it would be convenient to simply return the strings. This could be
> done but I am not sure how to do it in a reasonable way. Specifically,
> it would be nice to say that the 3rd column is to be returned as a
> string, not a BLOB or the 5th column is to be returned as a string
> instead of a number, etc. This question has come up before but no
> reasonable api has been forthcoming. If you have any suggestions, I'm
> all ears. :-)

What I'm currentlt trying is this:

class Record(dict):
   @classmethod
   def fromdata(cls, cursor, row):
     record = cls()
     for (i, field) in enumerate(row):
       if cursor.readclobs and cursor.description[i][1] is CLOB and 
isinstance(field, LOB):
         field = field.read()
       elif cursor.readblobs and cursor.description[i][1] is BLOB and 
isinstance(field, LOB):
         field = field.read()
       record[cursor.description[i][0]] = field
     return record

class Cursor(cx_Oracle.Cursor):
   def __init__(self, connection, readclobs=False, readblobs=False):
     super(Cursor, self).__init__(connection)
     self.readclobs = readclobs
     self.readblobs = readblobs
		
   def fetchone(self, type=Record):
     row = super(Cursor, self).fetchone()
     if row is not None:
       row = type.fromdata(self, row)
     return row


>> Strange coincidence. I was trying to implement something like this today
>> (in a wrapper that wraps cx_Oracle.Connection/cx_Oracle.Cursor).
>>
>> But I'd like to treat CLOB and BLOB differently (CLOBs are text typed by
>> the user, so the length should be fairly limited, but BLOBs might be
>> large uploaded files, so reading them unconditionally might be dangerous).
> 
> This all depends on your application. CLOBs are not unconditionally
> small, nor are BLOBs unconditionally large.

True.

> In your particular wrapper
> you could (for example) say that if a LOB value is returned and its
> size is less than 128K (or some other arbitrary amount) return its
> value rather than the locator. Of course if the value exceeded that
> length you would have to decide what to do about it -- perhaps raise
> an exception?

Or maybe return the origin LOB object? I think I'll give this version a try.

>> However both BLOBs and CLOBs get returned as LOB objects (although
>> cursor.description contains cx_Oracle.BLOB and cx_Oracle.CLOB).
>>
>> I would have expected that instances of cx_Oracle.BLOB and
>> cx_Oracle.CLOB get returned (and maybe that BLOB and CLOB are subclasses
>> of LOB).
> 
> Yes, this is because the bind variables themselves are not returned to
> you but the values are. cx_Oracle.CLOB and cx_Oracle.BLOB are the
> __bind variable__ types (just like STRING and NUMBER are the bind
> variable types and not the values str and int/long/float). Its
> possible that instead of putting the bind variable types in the
> cursor.description I put the actual Python type -- that, however,
> doesn't jive with the DB API.

PEP 249 only states that cx_Oracle.STRING must be the type used for 
returning strings. But why couldn't cx_Oracle.STRING *be* str?

> Since Oracle numbers can be represented
> as int, long or float (and possibly decimal as well) and the DB API
> insists upon a single type for that, that solution doesn't appear to
> be tenable -- unless you have a way around that dilemma?

No, but when the specification get's in the way of using cx_Oracle, I'd 
that the later should take precedence.

A cx_Oracle that returns NUMBERs as ints or Decimal objects would be 
great, but that might have to wait until there's a C implementation of 
the Decimal module (AFAICR there was a Google Summer Of Code Project 
that tried to do that).

Servus,
    Walter

Re: [cx-oracle-users] Question concerning executemany() in cx_Oracle

[matilda m. <ma...@gr...>]

Hi all,

>>>> "Anthony Tuininga" <ant...@gm...> 02/16/07 4:13 PM
>>>
>On 2/16/07, D.R.   Boxhoorn <da...@as...> wrote:
>> "requirement" means both formal definition and being very practical
in daily
>> use, I'd say leave it as it is. Although I do not feeling very
strongly about
>> this.
>
>I think the general concensus is to leave it alone for now.

Ok, ok, I surrender.   :-))
I'm happy that we have somthing like a discussion about that. ;-)

>>      File "<stdin>", line 1, in ?
>>    cx_Oracle.DatabaseError: ORA-24333: zero iteration count
>>
>> Is this a bug?
>
>Depends on your definition. I'm not sure why you want to call
>executemany() with a zero length list but perhaps don't do that?? ;-)

When I read original post I had to laugh because the exactly same
thing happend to me some days ago. I never heard about that
ORA error message before and was a little bit surprised. But then
I found the reason to be exactly this behaviour.
My szenario: Select rowid with "FOR UPDATE NOWAIT".
When I get the locks, I do an executemany on the resultset (list of
tuples) ;-))
It looked to good to be true. Now I have to add a if-clause to ask
whether the result set is empty or not.

>
>Perhaps you can explain what you feel the desired behavior ought to
>be? Were you hoping for a different error message? Silently ignoring
>the problem by doing nothing instead of making the Oracle call? I
>don't see either behavior as particularly compelling and I like the
>last suggestion I made the least. But perhaps I missed what you were
>intending. Please enlighten me. :-)

My thinking about the behaviour was (really personally):
Do something for all elements of the list. So, if there's no element,
then do
nothing. The question is: Shall the interface point you to the fact,
that you
gave an empty list. A list is a list, with or without elements.

Now, perhaps we can introduce a parameter with which you can choose the
behaviour. (The
"Please-Point-Me-To-The-Fact-That-I-Gave-An-Empty-List"-Parameter)
And if you choose this parameter name nobody will ever use it.  :-)))

I think this question is a little bit like the way "fetchall" returns:
Why does this statement not throw an exception when no row is returned?
(PL/SQL does by the way)
Why does "fetchall" return an empty list in this case silently? I think,
because
otherwise we would get crazy handling this special case while fitting
really good
to the other cases (iterating on an empty list does nothing).
So, as stated before: Only my personal opinion.

Have a nice evening
Andreas Mock

[cx-oracle-users] Calling executemany() with an empty list as argument

[D.R. B. <da...@as...>]

> >    awe> cx_Oracle.version
> >    '4.2.1'
> >    awe> cursor.executemany('INSERT INTO SOMETABLE(SOMECOLUMN) VALUES (:1)', [])
> >    Traceback (most recent call last):
> >      File "<stdin>", line 1, in ?
> >    cx_Oracle.DatabaseError: ORA-24333: zero iteration count
> >
> > Is this a bug?
> 
> Depends on your definition. I'm not sure why you want to call
> executemany() with a zero length list but perhaps don't do that?? ;-)
> 
> Perhaps you can explain what you feel the desired behavior ought to
> be? Were you hoping for a different error message? Silently ignoring
> the problem by doing nothing instead of making the Oracle call? I
> don't see either behavior as particularly compelling and I like the
> last suggestion I made the least. But perhaps I missed what you were
> intending. Please enlighten me. :-)

The desired behaviour should be that supplying an empty list is a noop.
Maybe some background helps to understand why we have this error.
We have made our own layer to make classes and attributes persistent.
For example

   class TestA(DBObject):
      int_attribute        = persistent('', int, -42)
      float_list           = persistent('', float, [])


define a persistent class with two persistent attributes. The first
attribute is a simple attribute, but the second is an array of floats.
The class definition is turned on-the-fly into Oracle tables and
instantiations of the class become insert statements.
This means that the underlying layer translates

   a = Test()
   a.float_list = [1,2]
   a.commit()

into something (with a meaning) like 

   executemany('INSERT INTO TESTA$FLOAT_LIST(...) VALUES (:1)', a.float_list)

However, users can decide to leave a.float_list equal to an empty list.
Consequently, "my" intermediate layer has to prepend each and every (well, there
are only two :) `executemany(...,values)' with an `if values is not []' guard.
If "your" intermediate layer would allow empty lists I do not have to check.
Obviously, the next level is that you would not have to check for empty lists
if Oracle would allow 0 rows to be inserted.

Oracle aren't sure that this is an error either.

   24333, 00000, "zero iteration count"
   // *Cause:  An iteration count of zero was specified for the statement
   // *Action: Specify the number of times this statement must be executed

The cause is identified correctly, but the action will get you stuck in an
endless loop, if you specify zero, which is want you want.
Interestingly, to avoid getting stuck in an endless loop, you have to perform
the loop exactly zero times. :)

In short, I can survive without this "feature" and I recommend you don't
try to convince Oracle that they should allow doing something zero times.
It's more work to convince them than for me or you to make the necessary
changes and for me to convince you.


Thanks!

                                                   Danny

Re: [cx-oracle-users] Question concerning executemany() in cx_Oracle

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

On 2/16/07, D.R.   Boxhoorn <da...@as...> wrote:
>
> Regarding the signature for executemany().
> Accepting that in the argument
>
>    "the use of types other than lists is certainly not a requirement"
>
> "requirement" means both formal definition and being very practical in daily
> use, I'd say leave it as it is. Although I do not feeling very strongly about
> this.

I think the general concensus is to leave it alone for now.

> The "requirement" may appear when (if at all) numpy will become part of
> Python, though. Then it would be natural to supply numpy arrays (including
> arrays of records) as an argument to executemany().

But perhaps revisit it when this occurs, of course. :-)

> Perhaps I should open another thread for this, but yesterday we found
> that executemany() accepts empty lists. That is to say, cx_Oracle
> accepts them, but Oracle doesn't.
>
>    Connected to:
>    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
>
>    awe> cx_Oracle.version
>    '4.2.1'
>    awe> cursor.executemany('INSERT INTO SOMETABLE(SOMECOLUMN) VALUES (:1)', [])
>    Traceback (most recent call last):
>      File "<stdin>", line 1, in ?
>    cx_Oracle.DatabaseError: ORA-24333: zero iteration count
>
> Is this a bug?

Depends on your definition. I'm not sure why you want to call
executemany() with a zero length list but perhaps don't do that?? ;-)

Perhaps you can explain what you feel the desired behavior ought to
be? Were you hoping for a different error message? Silently ignoring
the problem by doing nothing instead of making the Oracle call? I
don't see either behavior as particularly compelling and I like the
last suggestion I made the least. But perhaps I missed what you were
intending. Please enlighten me. :-)

>                                                    Danny
>
>
>
>
>
> On Mon, Feb 12, 2007 at 02:13:09PM -0700, Anthony Tuininga wrote:
> > I'm going to add another argument in support of leaving the method
> > signature for executemany() alone -- a slight performance penalty will
> > be incurred as the generic PySequence routines have to be used instead
> > of the macros that are supplied for manipulating lists. Since the use
> > of types other than lists is certainly not a requirement (and to a
> > certain extent undesirable) I'm of a mind to leave things as they
> > stand. If anyone else feels strongly about this, please speak up now.
> >
> > On 2/7/07, matilda matilda <ma...@gr...> wrote:
> > > > 1) The data coming back from fetchall() and fetchmany() is a list of
> > > tuples.
> > > > 2) Standard thinking (forget the exact reference) is that tuples are
> > > > used for sequences containing different types and lists are used for
> > > > sequences containing identical types
> > > > 3) Strings are also sequences which can lead to bizarre behavior if
> > > > you forget one layer of sequence
> > > > 4) That's the way I originally wrote it. :-)
> > >
> > > 2) I have to start at the end:
> > > Point 4) is a very very strong argument  :-))) I can't say anything
> > > against
> > > that. Quite the contrary: The more I look at the code the more I'm
> > > impressed.
> > > I'm sure this is your killer-argument in any case.  ;-)
> > >
> > > 3)
> > > > 3) Strings are also sequences which can lead to bizarre behavior if
> > > > you forget one layer of sequence
> > >
> > > This argument is true with every kind of programming as soon as I
> > > make mistakes.
> > >
> > > 4)
> > > > 1) The data coming back from fetchall() and fetchmany() is a list of
> > > tuples.
> > > Yes, that's ok. PEP 249 says:
> > > "[...] returning them as a sequence of sequences (e.g. a list of
> > > tuples) [...]".
> > > The interesting thing there is, that the programmer can expect
> > > sequence
> > > behaviour of the object returned. You surely know:
> > > http://docs.python.org/lib/typesseq.html
> > >
> > > A list as returned by your implementation does fulfill the sequence
> > > behaviour
> > > and therefore fulfill the PEP spec.
> > > That's now also the right transition to topic 5)
> > >
> > > 5) If executemany does expect a list explicitly, functionality is
> > > limited in
> > > my opinion as the PEP allows (!) a sequence and therefore a broader
> > > range of objects delivering the necessary data to the
> > > executemany()-call.
> > >
> > > I just wanted to create a litte test program to show what I mean and
> > > found
> > > a snippet in the sqlite-documentation doing exactly what I tried to
> > > build:
> > > --------------8<-----------------------
> > > from pysqlite2 import dbapi2 as sqlite
> > >
> > > def char_generator():
> > >     import string
> > >     for c in string.letters[:26]:
> > >         yield (c,)
> > >
> > > con = sqlite.connect(":memory:")
> > > cur = con.cursor()
> > > cur.execute("create table characters(c)")
> > >
> > > cur.executemany("insert into characters(c) values (?)",
> > > char_generator())
> > >
> > > cur.execute("select c from characters")
> > > print cur.fetchall()
> > > --------------8<-----------------------
> > >
> > > I'm sure that the more general approach could lead to difficulties
> > > in the current implementation especially in combination with
> > > bulk-binds,
> > > but functionality would be enhanced in a very "pythonic" manner.
> > > What do you think?
> > >
> > > 6) In any case: Your argument 4) will beat everything.  ;-)
> > > 7) For all interested:
> > > http://jtauber.com/blog/2006/04/15/python_tuples_are_not_just_constant_lists
> > >
> > >
> > > Best regards
> > > Andreas Mock
> > >
> > >
> > > -------------------------------------------------------------------------
> > > Using Tomcat but need to do more? Need to support web services, security?
> > > Get stuff done quickly with pre-integrated technology to make your job easier.
> > > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> > > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> > -------------------------------------------------------------------------
> > Using Tomcat but need to do more? Need to support web services, security?
> > Get stuff done quickly with pre-integrated technology to make your job easier.
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] curs.fetch() that grabs LOB data?

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

On 2/15/07, Walter D=F6rwald <wa...@li...> wrote:
> Mark Harrison wrote:
>
> > curs.fetch will return a LOB object for CLOB, etc. columns.
> > It's easy enough to iterate over the list of returned columns
> > and fetch the LOB data, but is there there some pre-built variant
> > of fetch that will return the CLOB data as a string?

No. Since CLOB and BLOB columns can be very large this has not been
done. I realize that there are some instances when this is not true
and it would be convenient to simply return the strings. This could be
done but I am not sure how to do it in a reasonable way. Specifically,
it would be nice to say that the 3rd column is to be returned as a
string, not a BLOB or the 5th column is to be returned as a string
instead of a number, etc. This question has come up before but no
reasonable api has been forthcoming. If you have any suggestions, I'm
all ears. :-)

> Strange coincidence. I was trying to implement something like this today
> (in a wrapper that wraps cx_Oracle.Connection/cx_Oracle.Cursor).
>
> But I'd like to treat CLOB and BLOB differently (CLOBs are text typed by
> the user, so the length should be fairly limited, but BLOBs might be
> large uploaded files, so reading them unconditionally might be dangerous)=
.

This all depends on your application. CLOBs are not unconditionally
small, nor are BLOBs unconditionally large. In your particular wrapper
you could (for example) say that if a LOB value is returned and its
size is less than 128K (or some other arbitrary amount) return its
value rather than the locator. Of course if the value exceeded that
length you would have to decide what to do about it -- perhaps raise
an exception?

> However both BLOBs and CLOBs get returned as LOB objects (although
> cursor.description contains cx_Oracle.BLOB and cx_Oracle.CLOB).
>
> I would have expected that instances of cx_Oracle.BLOB and
> cx_Oracle.CLOB get returned (and maybe that BLOB and CLOB are subclasses
> of LOB).

Yes, this is because the bind variables themselves are not returned to
you but the values are. cx_Oracle.CLOB and cx_Oracle.BLOB are the
__bind variable__ types (just like STRING and NUMBER are the bind
variable types and not the values str and int/long/float). Its
possible that instead of putting the bind variable types in the
cursor.description I put the actual Python type -- that, however,
doesn't jive with the DB API. Since Oracle numbers can be represented
as int, long or float (and possibly decimal as well) and the DB API
insists upon a single type for that, that solution doesn't appear to
be tenable -- unless you have a way around that dilemma?

> Servus,
>     Walter
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share y=
our
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3D=
DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Question concerning executemany() in cx_Oracle

[D.R. B. <da...@as...>]

Regarding the signature for executemany().
Accepting that in the argument

   "the use of types other than lists is certainly not a requirement"

"requirement" means both formal definition and being very practical in daily
use, I'd say leave it as it is. Although I do not feeling very strongly about
this.
The "requirement" may appear when (if at all) numpy will become part of
Python, though. Then it would be natural to supply numpy arrays (including
arrays of records) as an argument to executemany().

Perhaps I should open another thread for this, but yesterday we found
that executemany() accepts empty lists. That is to say, cx_Oracle
accepts them, but Oracle doesn't.

   Connected to:
   Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

   awe> cx_Oracle.version
   '4.2.1'
   awe> cursor.executemany('INSERT INTO SOMETABLE(SOMECOLUMN) VALUES (:1)', [])
   Traceback (most recent call last):
     File "<stdin>", line 1, in ?
   cx_Oracle.DatabaseError: ORA-24333: zero iteration count

Is this a bug?

                                                   Danny





On Mon, Feb 12, 2007 at 02:13:09PM -0700, Anthony Tuininga wrote:
> I'm going to add another argument in support of leaving the method
> signature for executemany() alone -- a slight performance penalty will
> be incurred as the generic PySequence routines have to be used instead
> of the macros that are supplied for manipulating lists. Since the use
> of types other than lists is certainly not a requirement (and to a
> certain extent undesirable) I'm of a mind to leave things as they
> stand. If anyone else feels strongly about this, please speak up now.
> 
> On 2/7/07, matilda matilda <ma...@gr...> wrote:
> > > 1) The data coming back from fetchall() and fetchmany() is a list of
> > tuples.
> > > 2) Standard thinking (forget the exact reference) is that tuples are
> > > used for sequences containing different types and lists are used for
> > > sequences containing identical types
> > > 3) Strings are also sequences which can lead to bizarre behavior if
> > > you forget one layer of sequence
> > > 4) That's the way I originally wrote it. :-)
> >
> > 2) I have to start at the end:
> > Point 4) is a very very strong argument  :-))) I can't say anything
> > against
> > that. Quite the contrary: The more I look at the code the more I'm
> > impressed.
> > I'm sure this is your killer-argument in any case.  ;-)
> >
> > 3)
> > > 3) Strings are also sequences which can lead to bizarre behavior if
> > > you forget one layer of sequence
> >
> > This argument is true with every kind of programming as soon as I
> > make mistakes.
> >
> > 4)
> > > 1) The data coming back from fetchall() and fetchmany() is a list of
> > tuples.
> > Yes, that's ok. PEP 249 says:
> > "[...] returning them as a sequence of sequences (e.g. a list of
> > tuples) [...]".
> > The interesting thing there is, that the programmer can expect
> > sequence
> > behaviour of the object returned. You surely know:
> > http://docs.python.org/lib/typesseq.html
> >
> > A list as returned by your implementation does fulfill the sequence
> > behaviour
> > and therefore fulfill the PEP spec.
> > That's now also the right transition to topic 5)
> >
> > 5) If executemany does expect a list explicitly, functionality is
> > limited in
> > my opinion as the PEP allows (!) a sequence and therefore a broader
> > range of objects delivering the necessary data to the
> > executemany()-call.
> >
> > I just wanted to create a litte test program to show what I mean and
> > found
> > a snippet in the sqlite-documentation doing exactly what I tried to
> > build:
> > --------------8<-----------------------
> > from pysqlite2 import dbapi2 as sqlite
> >
> > def char_generator():
> >     import string
> >     for c in string.letters[:26]:
> >         yield (c,)
> >
> > con = sqlite.connect(":memory:")
> > cur = con.cursor()
> > cur.execute("create table characters(c)")
> >
> > cur.executemany("insert into characters(c) values (?)",
> > char_generator())
> >
> > cur.execute("select c from characters")
> > print cur.fetchall()
> > --------------8<-----------------------
> >
> > I'm sure that the more general approach could lead to difficulties
> > in the current implementation especially in combination with
> > bulk-binds,
> > but functionality would be enhanced in a very "pythonic" manner.
> > What do you think?
> >
> > 6) In any case: Your argument 4) will beat everything.  ;-)
> > 7) For all interested:
> > http://jtauber.com/blog/2006/04/15/python_tuples_are_not_just_constant_lists
> >
> >
> > Best regards
> > Andreas Mock
> >
> >
> > -------------------------------------------------------------------------
> > Using Tomcat but need to do more? Need to support web services, security?
> > Get stuff done quickly with pre-integrated technology to make your job easier.
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] curs.fetch() that grabs LOB data?

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

Mark Harrison wrote:

> curs.fetch will return a LOB object for CLOB, etc. columns.
> It's easy enough to iterate over the list of returned columns
> and fetch the LOB data, but is there there some pre-built variant
> of fetch that will return the CLOB data as a string?

Strange coincidence. I was trying to implement something like this today 
(in a wrapper that wraps cx_Oracle.Connection/cx_Oracle.Cursor).

But I'd like to treat CLOB and BLOB differently (CLOBs are text typed by 
the user, so the length should be fairly limited, but BLOBs might be 
large uploaded files, so reading them unconditionally might be dangerous).

However both BLOBs and CLOBs get returned as LOB objects (although 
cursor.description contains cx_Oracle.BLOB and cx_Oracle.CLOB).

I would have expected that instances of cx_Oracle.BLOB and 
cx_Oracle.CLOB get returned (and maybe that BLOB and CLOB are subclasses 
of LOB).

Servus,
    Walter

[cx-oracle-users] curs.fetch() that grabs LOB data?

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

curs.fetch will return a LOB object for CLOB, etc. columns.
It's easy enough to iterate over the list of returned columns
and fetch the LOB data, but is there there some pre-built variant
of fetch that will return the CLOB data as a string?

TIA!
Mark

-- 
Mark Harrison
Pixar Animation Studios

Re: [cx-oracle-users] Question concerning executemany() in cx_Oracle

[Chris D. <cdu...@ya...>]

My only comment is that existing code should work unaltered with the same results, and ideally no
performance penalty, after any change to executemany().

Cheers,

Chris
--- Anthony Tuininga <ant...@gm...> wrote:

> I'm going to add another argument in support of leaving the method
> signature for executemany() alone -- a slight performance penalty will
> be incurred as the generic PySequence routines have to be used instead
> of the macros that are supplied for manipulating lists. Since the use
> of types other than lists is certainly not a requirement (and to a
> certain extent undesirable) I'm of a mind to leave things as they
> stand. If anyone else feels strongly about this, please speak up now.
> 
> On 2/7/07, matilda matilda <ma...@gr...> wrote:
> > > 1) The data coming back from fetchall() and fetchmany() is a list of
> > tuples.
> > > 2) Standard thinking (forget the exact reference) is that tuples are
> > > used for sequences containing different types and lists are used for
> > > sequences containing identical types
> > > 3) Strings are also sequences which can lead to bizarre behavior if
> > > you forget one layer of sequence
> > > 4) That's the way I originally wrote it. :-)
> >
> > 2) I have to start at the end:
> > Point 4) is a very very strong argument  :-))) I can't say anything
> > against
> > that. Quite the contrary: The more I look at the code the more I'm
> > impressed.
> > I'm sure this is your killer-argument in any case.  ;-)
> >
> > 3)
> > > 3) Strings are also sequences which can lead to bizarre behavior if
> > > you forget one layer of sequence
> >
> > This argument is true with every kind of programming as soon as I
> > make mistakes.
> >
> > 4)
> > > 1) The data coming back from fetchall() and fetchmany() is a list of
> > tuples.
> > Yes, that's ok. PEP 249 says:
> > "[...] returning them as a sequence of sequences (e.g. a list of
> > tuples) [...]".
> > The interesting thing there is, that the programmer can expect
> > sequence
> > behaviour of the object returned. You surely know:
> > http://docs.python.org/lib/typesseq.html
> >
> > A list as returned by your implementation does fulfill the sequence
> > behaviour
> > and therefore fulfill the PEP spec.
> > That's now also the right transition to topic 5)
> >
> > 5) If executemany does expect a list explicitly, functionality is
> > limited in
> > my opinion as the PEP allows (!) a sequence and therefore a broader
> > range of objects delivering the necessary data to the
> > executemany()-call.
> >
> > I just wanted to create a litte test program to show what I mean and
> > found
> > a snippet in the sqlite-documentation doing exactly what I tried to
> > build:
> > --------------8<-----------------------
> > from pysqlite2 import dbapi2 as sqlite
> >
> > def char_generator():
> >     import string
> >     for c in string.letters[:26]:
> >         yield (c,)
> >
> > con = sqlite.connect(":memory:")
> > cur = con.cursor()
> > cur.execute("create table characters(c)")
> >
> > cur.executemany("insert into characters(c) values (?)",
> > char_generator())
> >
> > cur.execute("select c from characters")
> > print cur.fetchall()
> > --------------8<-----------------------
> >
> > I'm sure that the more general approach could lead to difficulties
> > in the current implementation especially in combination with
> > bulk-binds,
> > but functionality would be enhanced in a very "pythonic" manner.
> > What do you think?
> >
> > 6) In any case: Your argument 4) will beat everything.  ;-)
> > 7) For all interested:
> > http://jtauber.com/blog/2006/04/15/python_tuples_are_not_just_constant_lists
> >
> >
> > Best regards
> > Andreas Mock
> >
> >
> > -------------------------------------------------------------------------
> > Using Tomcat but need to do more? Need to support web services, security?
> > Get stuff done quickly with pre-integrated technology to make your job easier.
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



 
____________________________________________________________________________________
Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

Re: [cx-oracle-users] Question concerning executemany() in cx_Oracle

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

I'm going to add another argument in support of leaving the method
signature for executemany() alone -- a slight performance penalty will
be incurred as the generic PySequence routines have to be used instead
of the macros that are supplied for manipulating lists. Since the use
of types other than lists is certainly not a requirement (and to a
certain extent undesirable) I'm of a mind to leave things as they
stand. If anyone else feels strongly about this, please speak up now.

On 2/7/07, matilda matilda <ma...@gr...> wrote:
> > 1) The data coming back from fetchall() and fetchmany() is a list of
> tuples.
> > 2) Standard thinking (forget the exact reference) is that tuples are
> > used for sequences containing different types and lists are used for
> > sequences containing identical types
> > 3) Strings are also sequences which can lead to bizarre behavior if
> > you forget one layer of sequence
> > 4) That's the way I originally wrote it. :-)
>
> 2) I have to start at the end:
> Point 4) is a very very strong argument  :-))) I can't say anything
> against
> that. Quite the contrary: The more I look at the code the more I'm
> impressed.
> I'm sure this is your killer-argument in any case.  ;-)
>
> 3)
> > 3) Strings are also sequences which can lead to bizarre behavior if
> > you forget one layer of sequence
>
> This argument is true with every kind of programming as soon as I
> make mistakes.
>
> 4)
> > 1) The data coming back from fetchall() and fetchmany() is a list of
> tuples.
> Yes, that's ok. PEP 249 says:
> "[...] returning them as a sequence of sequences (e.g. a list of
> tuples) [...]".
> The interesting thing there is, that the programmer can expect
> sequence
> behaviour of the object returned. You surely know:
> http://docs.python.org/lib/typesseq.html
>
> A list as returned by your implementation does fulfill the sequence
> behaviour
> and therefore fulfill the PEP spec.
> That's now also the right transition to topic 5)
>
> 5) If executemany does expect a list explicitly, functionality is
> limited in
> my opinion as the PEP allows (!) a sequence and therefore a broader
> range of objects delivering the necessary data to the
> executemany()-call.
>
> I just wanted to create a litte test program to show what I mean and
> found
> a snippet in the sqlite-documentation doing exactly what I tried to
> build:
> --------------8<-----------------------
> from pysqlite2 import dbapi2 as sqlite
>
> def char_generator():
>     import string
>     for c in string.letters[:26]:
>         yield (c,)
>
> con = sqlite.connect(":memory:")
> cur = con.cursor()
> cur.execute("create table characters(c)")
>
> cur.executemany("insert into characters(c) values (?)",
> char_generator())
>
> cur.execute("select c from characters")
> print cur.fetchall()
> --------------8<-----------------------
>
> I'm sure that the more general approach could lead to difficulties
> in the current implementation especially in combination with
> bulk-binds,
> but functionality would be enhanced in a very "pythonic" manner.
> What do you think?
>
> 6) In any case: Your argument 4) will beat everything.  ;-)
> 7) For all interested:
> http://jtauber.com/blog/2006/04/15/python_tuples_are_not_just_constant_lists
>
>
> Best regards
> Andreas Mock
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] how to call owa_util.showpage, or dbms_output to get the contents of a web page?

[John_Nowlan <joh...@ca...>]

I'm off to the races!
Thankyou.

Substituting htp.get_page for dbms_output.get_lines in your 2nd example works fine.

Another post jogged my memory to check the test cases after I sent my previous email, where I found a similar test case. Those tests are great, obviously something I have to study some more.

Cheers

Re: [cx-oracle-users] how to call owa_util.showpage, or dbms_output to get the contents of a web page?

[Christopher J. <chr...@or...>]

FWIW see my post at the bottom of: http://forums.oracle.com/forums/thread.jspa?messageID=1625605#1625605

Chris

Anthony Tuininga wrote:
> I can't run htp but I can run dbms_output and I have provided a script
> here that does it for your benefit. That should help you figure out
> where to go from here. See the reference documentation for more
> information or ask again if you really run stuck.
> 
> import cx_Oracle
> 
> connection = cx_Oracle.Connection("user/pw@dns")
> cursor = connection.cursor()
> cursor.callproc("dbms_output.enable")
> cursor.callproc("dbms_output.put_line", ("hi there",))
> cursor.callproc("dbms_output.put_line", ("and goodbye",))
> cursor.callproc("dbms_output.put_line", ("wait a minute",))
> 
> # one way of doing it
> if 0:
>     statusVar = cursor.var(cx_Oracle.NUMBER)
>     lineVar = cursor.var(cx_Oracle.STRING)
>     while True:
>         cursor.callproc("dbms_output.get_line", (lineVar, statusVar))
>         if statusVar.getvalue() != 0:
>             break
>         print lineVar.getvalue()
> 
> # using an array
> if 1:
>     numLinesVar = cursor.var(cx_Oracle.NUMBER)
>     linesVar = cursor.arrayvar(cx_Oracle.STRING, 2)
>     while True:
>         numLinesVar.setvalue(0, 2)
>         cursor.callproc("dbms_output.get_lines", (linesVar, numLinesVar))
>         numLines = int(numLinesVar.getvalue())
>         if numLines == 0:
>             break
>         for line in linesVar.getvalue()[:numLines]:
>             print line
> 
> On 2/8/07, John_Nowlan <joh...@ca...> wrote:
>>
>> O.k. I seem to be having the same problems everyone else has with parameters
>> & binding, but I'm still not getting it.
>> I have:
>>
>> CREATE OR REPLACE PROCEDURE webpage
>>    IS
>>    BEGIN
>>     htp.htmlopen;
>>     htp.headopen;
>>     htp.htitle('web page');
>>     htp.headclose;
>>     htp.bodyopen;
>>     htp.p('Neato!');
>>     htp.bodyclose;
>>     htp.htmlclose;
>>    END webpage;
>> /
>>
>> From package htp:
>> type htbuf_arr is table of varchar2(256) index by binary_integer;
>>
>> - which looks to me like an oracle array, not an sql datatype so should be
>> o.k.?
>> - but perhaps is a named datatype which is not supported?
>>
>> htp.procedure get_page (thepage out htbuf_arr, irows in out integer);
>>
>> but whatever I try (and I've tried a few variations...) I get things like:
>>
>>>>> o, i
>> ([], 4)
>>>>> c.callproc('htp.get_page(:o, :i)', [o, i])
>> Traceback (most recent call last):
>>   File "<interactive input>", line 1, in ?
>> DatabaseError: ORA-01008: not all variables bound
>>
>> or
>>
>>>>> r = c.execute('begin htp.get_page(:o, :i); end;', p)
>> Traceback (most recent call last):
>>   File "<interactive input>", line 1, in ?
>> NotSupportedError: Variable_TypeByValue(): unhandled data type type
>>>>> p
>> {'i': <type 'cx_Oracle.NUMBER'>, 'o': []}
>>>>> p['i'] = 1
>>>>> p
>> {'i': 1, 'o': []}
>>>>> r = c.execute('begin htp.get_page(:o, :i); end;', p)
>>>>> r
>>>>> p
>> {'i': 1, 'o': []}
>>>>> p['o'].getvalue()
>> Traceback (most recent call last):
>>   File "<interactive input>", line 1, in ?
>> AttributeError: 'list' object has no attribute 'getvalue'
>>
>>
>> - as you can see I'm in a _bit_ over my head.
>> - Is there a shorthand for AnyAndAllHelpAppreciated? AAAHA
>>
>> and Thanks for cx_Oracle
>>
>> -------------------------------------------------------------------------
>> Using Tomcat but need to do more? Need to support web services, security?
>> Get stuff done quickly with pre-integrated technology to make your job
>> easier.
>> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


-- 
Christopher Jones, Oracle
Email: Chr...@or...    Tel: +1 650 506 8630
Blog:  http://blogs.oracle.com/opal/

Re: [cx-oracle-users] how to call owa_util.showpage, or dbms_output to get the contents of a web page?

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

I can't run htp but I can run dbms_output and I have provided a script
here that does it for your benefit. That should help you figure out
where to go from here. See the reference documentation for more
information or ask again if you really run stuck.

import cx_Oracle

connection = cx_Oracle.Connection("user/pw@dns")
cursor = connection.cursor()
cursor.callproc("dbms_output.enable")
cursor.callproc("dbms_output.put_line", ("hi there",))
cursor.callproc("dbms_output.put_line", ("and goodbye",))
cursor.callproc("dbms_output.put_line", ("wait a minute",))

# one way of doing it
if 0:
    statusVar = cursor.var(cx_Oracle.NUMBER)
    lineVar = cursor.var(cx_Oracle.STRING)
    while True:
        cursor.callproc("dbms_output.get_line", (lineVar, statusVar))
        if statusVar.getvalue() != 0:
            break
        print lineVar.getvalue()

# using an array
if 1:
    numLinesVar = cursor.var(cx_Oracle.NUMBER)
    linesVar = cursor.arrayvar(cx_Oracle.STRING, 2)
    while True:
        numLinesVar.setvalue(0, 2)
        cursor.callproc("dbms_output.get_lines", (linesVar, numLinesVar))
        numLines = int(numLinesVar.getvalue())
        if numLines == 0:
            break
        for line in linesVar.getvalue()[:numLines]:
            print line

On 2/8/07, John_Nowlan <joh...@ca...> wrote:
>
>
> O.k. I seem to be having the same problems everyone else has with parameters
> & binding, but I'm still not getting it.
> I have:
>
> CREATE OR REPLACE PROCEDURE webpage
>    IS
>    BEGIN
>     htp.htmlopen;
>     htp.headopen;
>     htp.htitle('web page');
>     htp.headclose;
>     htp.bodyopen;
>     htp.p('Neato!');
>     htp.bodyclose;
>     htp.htmlclose;
>    END webpage;
> /
>
> From package htp:
> type htbuf_arr is table of varchar2(256) index by binary_integer;
>
> - which looks to me like an oracle array, not an sql datatype so should be
> o.k.?
> - but perhaps is a named datatype which is not supported?
>
> htp.procedure get_page (thepage out htbuf_arr, irows in out integer);
>
> but whatever I try (and I've tried a few variations...) I get things like:
>
> >>> o, i
> ([], 4)
> >>> c.callproc('htp.get_page(:o, :i)', [o, i])
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> DatabaseError: ORA-01008: not all variables bound
>
> or
>
> >>> r = c.execute('begin htp.get_page(:o, :i); end;', p)
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> NotSupportedError: Variable_TypeByValue(): unhandled data type type
> >>> p
> {'i': <type 'cx_Oracle.NUMBER'>, 'o': []}
> >>> p['i'] = 1
> >>> p
> {'i': 1, 'o': []}
> >>> r = c.execute('begin htp.get_page(:o, :i); end;', p)
> >>> r
> >>> p
> {'i': 1, 'o': []}
> >>> p['o'].getvalue()
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> AttributeError: 'list' object has no attribute 'getvalue'
>
>
> - as you can see I'm in a _bit_ over my head.
> - Is there a shorthand for AnyAndAllHelpAppreciated? AAAHA
>
> and Thanks for cx_Oracle
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] how to call owa_util.showpage, or dbms_output to get the contents of a web page?

[John_Nowlan <joh...@ca...>]

O.k. I seem to be having the same problems everyone else has with parameters & binding, but I'm still not getting it.
I have:
 
CREATE OR REPLACE PROCEDURE webpage
   IS
   BEGIN
    htp.htmlopen;
    htp.headopen;
    htp.htitle('web page');
    htp.headclose;
    htp.bodyopen;
    htp.p('Neato!');
    htp.bodyclose;
    htp.htmlclose;
   END webpage;
/
 
>From package htp:
type htbuf_arr is table of varchar2(256) index by binary_integer; 
 
- which looks to me like an oracle array, not an sql datatype so should be o.k.?
- but perhaps is a named datatype which is not supported?
 
htp.procedure get_page (thepage out htbuf_arr, irows in out integer);
 
but whatever I try (and I've tried a few variations...) I get things like:
 
>>> o, i
([], 4)
>>> c.callproc('htp.get_page(:o, :i)', [o, i])
Traceback (most recent call last):
  File "<interactive input>", line 1, in ?
DatabaseError: ORA-01008: not all variables bound
 
or 
 
>>> r = c.execute('begin htp.get_page(:o, :i); end;', p)
Traceback (most recent call last):
  File "<interactive input>", line 1, in ?
NotSupportedError: Variable_TypeByValue(): unhandled data type type
>>> p
{'i': <type 'cx_Oracle.NUMBER'>, 'o': []}
>>> p['i'] = 1
>>> p
{'i': 1, 'o': []}
>>> r = c.execute('begin htp.get_page(:o, :i); end;', p)
>>> r
>>> p
{'i': 1, 'o': []}
>>> p['o'].getvalue()
Traceback (most recent call last):
  File "<interactive input>", line 1, in ?
AttributeError: 'list' object has no attribute 'getvalue'
 
 
- as you can see I'm in a _bit_ over my head. 
- Is there a shorthand for AnyAndAllHelpAppreciated? AAAHA
 
and Thanks for cx_Oracle
 

Re: [cx-oracle-users] how to call owa_util.showpage, or dbms_output to get the contents of a web page?

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

You're right in that this has more to do with Oracle than cx_Oracle
but since most of the people using cx_Oracle have to interact with
Oracle its a good place to fish for answers. :-)

I have not done this myself personally but I know that dbms_output
populates an internal buffer and that SQL*Plus will automatically
print the output placed in that buffer after a PL/SQL procedure
successfully completes. Taking a look at dbms_output there are a
couple of procedures that are of interest: get_line() and get_lines()
which will likely return you data from that internal buffer. That
should help you in the right direction anyway.

On 2/8/07, John_Nowlan <joh...@ca...> wrote:
>
>
>
> I'm in the initial stages of migrating a legacy web app that uses the htp
> and htf packages to generate web pages and would like to get the legacy
> generated page from cx_Oracle if possible.
>
> Can I do this?
>
> I.e. I want to do something like:
> >>> x = c.callproc('owa_util.showpage')
>
> I can do something like:
> >>> c.callproc('johnnowlan.who')
> []
> - but I just get an empty list back, but the proc generates a webpage, how
> can I access it?
>
> dbms_output is being used in there and that is where my search is heading.
> Just thought someone might have some pointers to help speed up this snail in
> his search for ways to do this...
>
> I'm beginning to realize that most of the problems are probably more to do
> with oracle than cx_oracle but it's not completely off topic, is it?
>
> Cheers,
> John
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] how to call owa_util.showpage, or dbms_output to get the contents of a web page?

[John_Nowlan <joh...@ca...>]

I'm in the initial stages of migrating a legacy web app that uses the htp and htf packages to generate web pages and would like to get the legacy generated page from cx_Oracle if possible.

Can I do this? 

I.e. I want to do something like:
>>> x = c.callproc('owa_util.showpage') 

I can do something like:
>>> c.callproc('johnnowlan.who')
[]
- but I just get an empty list back, but the proc generates a webpage, how can I access it?

dbms_output is being used in there and that is where my search is heading. Just thought someone might have some pointers to help speed up this snail in his search for ways to do this...

I'm beginning to realize that most of the problems are probably more to do with oracle than cx_oracle but it's not completely off topic, is it? 

Cheers,
John

Re: [cx-oracle-users] Question concerning executemany() in cx_Oracle

[matilda m. <ma...@gr...>]

Hi Anthony, hi all,

1) Is there somebody else who has an opinion to that?

>>> "Anthony Tuininga" <ant...@gm...> 06.02.2007 21:53
>>>
> veral reasons -- but this is something that can be changed if
necessary.
> 
> 1) The data coming back from fetchall() and fetchmany() is a list of
tuples.
> 2) Standard thinking (forget the exact reference) is that tuples are
> used for sequences containing different types and lists are used for
> sequences containing identical types
> 3) Strings are also sequences which can lead to bizarre behavior if
> you forget one layer of sequence
> 4) That's the way I originally wrote it. :-)

2) I have to start at the end:
Point 4) is a very very strong argument  :-))) I can't say anything
against
that. Quite the contrary: The more I look at the code the more I'm
impressed.
I'm sure this is your killer-argument in any case.  ;-)

3)
> 3) Strings are also sequences which can lead to bizarre behavior if
> you forget one layer of sequence

This argument is true with every kind of programming as soon as I
make mistakes.

4) 
> 1) The data coming back from fetchall() and fetchmany() is a list of
tuples.
Yes, that's ok. PEP 249 says:
"[...] returning them as a sequence of sequences (e.g. a list of
tuples) [...]".
The interesting thing there is, that the programmer can expect
sequence
behaviour of the object returned. You surely know:
http://docs.python.org/lib/typesseq.html 

A list as returned by your implementation does fulfill the sequence
behaviour
and therefore fulfill the PEP spec.
That's now also the right transition to topic 5)

5) If executemany does expect a list explicitly, functionality is
limited in
my opinion as the PEP allows (!) a sequence and therefore a broader
range of objects delivering the necessary data to the
executemany()-call.

I just wanted to create a litte test program to show what I mean and
found
a snippet in the sqlite-documentation doing exactly what I tried to
build:
--------------8<-----------------------
from pysqlite2 import dbapi2 as sqlite

def char_generator():
    import string
    for c in string.letters[:26]:
        yield (c,)

con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)",
char_generator())

cur.execute("select c from characters")
print cur.fetchall() 
--------------8<-----------------------

I'm sure that the more general approach could lead to difficulties
in the current implementation especially in combination with
bulk-binds,
but functionality would be enhanced in a very "pythonic" manner.
What do you think?

6) In any case: Your argument 4) will beat everything.  ;-)
7) For all interested:
http://jtauber.com/blog/2006/04/15/python_tuples_are_not_just_constant_lists


Best regards
Andreas Mock

Re: [cx-oracle-users] Antw: Re: Question concerning executemany() in cx_Oracle

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

On 2/6/07, matilda matilda <ma...@gr...> wrote:
> Hi Anthony,
>
> thank you for your fast reply.

You're welcome.

> >>> "Anthony Tuininga" <ant...@gm...> 06.02.07 19.22 Uhr
> >>>
> > First, your insert statement is specifying ":val" which is a named
> > parameter. You need to specify dictionaries, then. Or you can specify
> > ":1" and use what you have defined. Second, you need to specify a list
> > as the top level as in
> >
> > list = [ (ae,), (ue,), (oe,) ]
>
> 1) It seems also to work with the named parameter as
> soon as I take a list and not a tuple. Does cx_Oracle or
> oracle itself parse the statement differently depending
> on using :1 or :val ?

The statement does get handled differently. It looks like Oracle gets
"smart" and will bind by position even when named variables are used
-- but I wouldn't count on this behavior and if the number of bind
variables exceeds one the order begins to matter.... Use numeric bind
variables in that case to make it clear what you are up to.

> 2) Why do I need a list as the surrounding container and
> can't use a tuple which is a sequence. As far as I understand
> the PEP, the needed parameter has to be a sequence of
> sequences or a sequence of dicts.

Several reasons -- but this is something that can be changed if necessary.

1) The data coming back from fetchall() and fetchmany() is a list of tuples.
2) Standard thinking (forget the exact reference) is that tuples are
used for sequences containing different types and lists are used for
sequences containing identical types
3) Strings are also sequences which can lead to bizarre behavior if
you forget one layer of sequence
4) That's the way I originally wrote it. :-)

Comments anyone? Should this be changed?

> Thank you in advance.
>
> Best regards
> Andreas Mock
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Antw: Re: Question concerning executemany() in cx_Oracle

[matilda m. <ma...@gr...>]

Hi Anthony,

thank you for your fast reply.

>>> "Anthony Tuininga" <ant...@gm...> 06.02.07 19.22 Uhr
>>>
> First, your insert statement is specifying ":val" which is a named
> parameter. You need to specify dictionaries, then. Or you can specify
> ":1" and use what you have defined. Second, you need to specify a list
> as the top level as in
>
> list = [ (ae,), (ue,), (oe,) ]

1) It seems also to work with the named parameter as
soon as I take a list and not a tuple. Does cx_Oracle or
oracle itself parse the statement differently depending
on using :1 or :val ?

2) Why do I need a list as the surrounding container and
can't use a tuple which is a sequence. As far as I understand
the PEP, the needed parameter has to be a sequence of
sequences or a sequence of dicts. 

Thank you in advance.

Best regards
Andreas Mock