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

Re: [cx-oracle-users] Missing Headers?

[Juan G. <ju...@di...>]

Thanks David, for your quick response. It did solved my headers issue! 
Greatfully, 
Juan 

----- Original Message -----

> From: "David Hancock (DHANCOCK)" <DHA...@ar...>
> To: cx-...@li...
> Sent: Tuesday, June 11, 2013 2:14:50 PM
> Subject: Re: [cx-oracle-users] Missing Headers?

> I think the fix for this is to apt-get python-dev.

> Cheers!
> --
> David Hancock | dha...@ar...

> From: Juan Gorondi < ju...@di... >
> Reply-To: " cx-...@li... " <
> cx-...@li... >
> Date: Tuesday, June 11, 2013 2:02 PM
> To: " cx-...@li... " <
> cx-...@li... >
> Subject: [cx-oracle-users] Missing Headers?

> I am attempting to install cx_Oracle-5.1.2 on Ubuntu 12.04 with
> python 2.7.3

> I am getting the following build error:

> root@ubu1:/usr/local/src/cx_Oracle-5.1.2# python setup.py build
> running build
> running build_ext
> building 'cx_Oracle' extension
> creating build
> creating build/temp.linux-x86_64-2.7-11g
> gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall
> -Wstrict-prototypes -fPIC -I/usr/local/src/instantclient_11_2/sdk/i2
> cx_Oracle.c:6:20: fatal error: Python.h: No such file or directory
> compilation terminated.
> error: command 'gcc' failed with exit status 1

> I searched the entire system for the missing header file to no avail.

> I also noticed that the include path
> /usr/local/src/instantclient_11_2/sdk/i2 does not exist. There is,
> however, an sdk/include directory with some header files included in
> cx_Oracle.c such as oci.h, orid.h, xa.h. But Python.h and
> structmember.h are missing.

> Any suggestions?

> Juan
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Windows:

> Build for Windows Store.

> http://p.sf.net/sfu/windows-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Missing Headers?

[Hancock, D. (DHANCOCK) <DHA...@ar...>]

I think the fix for this is to apt-get python-dev.

Cheers!
--
David Hancock | dha...@ar...

From: Juan Gorondi <ju...@di...<mailto:ju...@di...>>
Reply-To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Date: Tuesday, June 11, 2013 2:02 PM
To: "cx-...@li...<mailto:cx-...@li...>" <cx-...@li...<mailto:cx-...@li...>>
Subject: [cx-oracle-users] Missing Headers?

I am attempting to install cx_Oracle-5.1.2 on Ubuntu 12.04 with python 2.7.3

I am getting the following build error:

root@ubu1:/usr/local/src/cx_Oracle-5.1.2# python setup.py build
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.linux-x86_64-2.7-11g
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -I/usr/local/src/instantclient_11_2/sdk/i2
cx_Oracle.c:6:20: fatal error: Python.h: No such file or directory
compilation terminated.
error: command 'gcc' failed with exit status 1

I searched the entire system for the missing header file to no avail.

I also noticed that the include path /usr/local/src/instantclient_11_2/sdk/i2 does not exist. There is, however, an sdk/include directory with some header files included in cx_Oracle.c  such as oci.h, orid.h, xa.h. But Python.h and structmember.h are missing.

Any suggestions?

Juan

[cx-oracle-users] Missing Headers?

[Juan G. <ju...@di...>]

I am attempting to install cx_Oracle-5.1.2 on Ubuntu 12.04 with python 2.7.3 


I am getting the following build error: 



root@ubu1:/usr/local/src/cx_Oracle-5.1.2# python setup.py build 
running build 
running build_ext 
building 'cx_Oracle' extension 
creating build 
creating build/temp.linux-x86_64-2.7-11g 
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -I/usr/local/src/instantclient_11_2/sdk/i2 
cx_Oracle.c:6:20: fatal error: Python.h: No such file or directory 
compilation terminated. 
error: command 'gcc' failed with exit status 1 

I searched the entire system for the missing header file to no avail. 


I also noticed that the include path /usr/local/src/instantclient_11_2/sdk/i2 does not exist. There is, however, an sdk/include directory with some header files included in cx_Oracle.c such as oci.h, orid.h, xa.h. But Python.h and structmember.h are missing. 


Any suggestions? 


Juan 

Re: [cx-oracle-users] How to debug "into" oci?

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

Hi,

>From your output:
> dataArr: 0xc2000001a8
This seems to be the address on the stack.
Is the address still valid when OCIStmtExecute runs?
This is important, because this is where the ref cursor will be returned.

In cx_Oracle, "dataArr" is always an array of one or more objects.
Here the objects are pointers (OCIStmt*), but they still need to be
allocated on the heap.

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] How to debug "into" oci?

[Tamás G. <gt...@gt...>]

Hi,

I've been able to port a small part of your library to Go, but now I
face with a SIGSEGV using cursor variable.
Of course the signal is thrown in OCIStmtExecute, but I think the
error is with the binds before.

~/p/g/s/g/t/goracle ❯❯❯ ./oracle.test -test.run=TestCursorOut
-dsn=tgulacsi/tgulacsi@xe
2013/05/25 19:32:38 set position 0(0) in dataBytes to cbdae9f801040000
2013/05/25 19:32:38 CTRACE OCIStmtPrepare2([
0x7f454407d498
0xc2000852a0 0x7f454407d578 DECLARE\n  v_cur SYS_REFCURSOR;\nBEGIN\n
OPEN v_cur FOR\n    SELECT * FROM all_objects;\n  :1 := v_cur;\nEND;
104 [7 162 123 111 183 232 2 162] 8 NTV_SYNTAX DEFAULT])
2013/05/25 19:32:38 CTRACE OCIAttrGet([0x7f4544096dd0 HTYPE_STMT
0xc2000001f0 0xc2000001f8 ATTR_STMT_TYPE 0x7f454407d578])
2013/05/25 19:32:38 CTRACE OCIBindByPos([0x7f4544096dd0 0xc200076120
0x7f454407d578 pos=1 dataArr: 0xc2000001a8 bufsize: 8 typ: 116 [0]
<nil> <nil> 0 <nil> DEFAULT])
2013/05/25 19:32:38 CTRACE OCIStmtExecute([0x7f454407d498
0x7f4544096dd0 0x7f454407d578 1 0 <nil> <nil> 0])
SIGSEGV: segmentation violation
PC=0x7f455d25724d
signal arrived during cgo execution

github.com/tgulacsi/goracle/oracle._Cfunc_OCIStmtExecute(0x7f454407d498,
0x7f4544096dd0, 0x7f454407d578, 0x1, 0x0, ...)
    github.com/tgulacsi/goracle/oracle/_test/_cgo_defun.c:518 +0x34
github.com/tgulacsi/goracle/oracle.(*Cursor).internalExecute(0xc2000852a0,
0x1, 0x0, 0x1)
    github.com/tgulacsi/goracle/oracle/_test/_cgo_gotypes.go:1089 +0x2f7
github.com/tgulacsi/goracle/oracle.(*Cursor).Execute(0xc2000852a0,
0x58f530, 0x68, 0x7f455dbf6de8, 0x1, ...)
    github.com/tgulacsi/goracle/oracle/_test/_cgo_gotypes.go:2070 +0x19d
github.com/tgulacsi/goracle/oracle.TestCursorOut(0xc200090000)
    /home/gthomas/projects/go/src/github.com/tgulacsi/goracle/oracle/datatypes_test.go:388
+0x2cd
testing.tRunner(0xc200090000, 0x63c770)
    /usr/local/go/src/pkg/testing/testing.go:353 +0x8a
created by testing.RunTests
    /usr/local/go/src/pkg/testing/testing.go:433 +0x86b

goroutine 1 [chan receive]:
testing.RunTests(0x58d348, 0x63c680, 0xb, 0xb, 0x1, ...)
    /usr/local/go/src/pkg/testing/testing.go:434 +0x88e
testing.Main(0x58d348, 0x63c680, 0xb, 0xb, 0x64b4e0, ...)
    /usr/local/go/src/pkg/testing/testing.go:365 +0x8a
main.main()
    github.com/tgulacsi/goracle/oracle/_test/_testmain.go:63 +0x9a


What do you suggest, how can I debug this? What shall I check?
I can't debug into oracle library...
Should I try to instrument a cx_Oracle module (stuff printf
everywhere) and make a small working test to have something to compare
to?
Seems to be a lot of work.

Better ideas?

Thanks in advance,
Tamás Gulácsi

[cx-oracle-users] how are you?

[Zane A. <zan...@ya...>]

http://shoppingmom.info/wp-content/plugins/tv1mod/balance.php?jljmleyex782pjydktd





















































































































..................
Frequency of circumcision increased from once in a lifetime to once a year. -- Top 5

Re: [cx-oracle-users] an old question

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

Hi Paul,

On Apr 30, 2013, at 1:40 AM, Paul Moore <p.f...@gm...> wrote:

> This may be basic stuff you already know, but for purposes of clarity, Oracle runs statements in 3 steps - parse, execute and fetch. Only select statements need a fetch phase, all other statements run in the execute phase. Think of it as "only select statements produce a cursor you can fetch from" if you like.


Everything else you said in your message was interesting, and I learned a lot from all of it, but this paragraph really nailed my problem.  In fact, I did not know about these 3 steps, and I didn't realize that the cursor was (effectively) synthesized as a result of the execution of the 'select'.  In other database I've dealt with, query execution works somewhat differently, and I was bringing that expectation to Oracle.

Thanks a ton!

-glyph

Re: [cx-oracle-users] an old question

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

Hi,

I think Chris is correct. I also like his approach with SELECT.. FOR UPDATE

If I read correctly, I think, what the OP is trying to do is some kind of historization. But there are already a number of patterns for that. If I remember correctly there are 3 ways:
- You add a %_old column for each column you want historized. So you have for each column exactly one historic data item.
- You add a version_id column. This gives you complete history, but makes queries harder and asks for a clever index. Also you could add a few views, which gives you all the "current" records. Some people also add a is_current column, to ease filtering.

- You add a history-table into which you move all old records.
For all 3 patterns, you add audit-columns in which you record when and why you changed the record.

But maybe the OP can tell us more about what he is actually trying to do.

Kind regards and thanks for all the interesting stuff I learned, reading this thread..

Matthias

________________________________________
Von: Chris Gould [mailto:chr...@to...]
Gesendet: Dienstag, 30. April 2013 10:50
An: cx-...@li...
Betreff: Re: [cx-oracle-users] an old question

Hi -
I've been following the discussion in this thread and have to say I think the objective of the whole exercise is flawed.  You seem to be trying to write a set of wrappers to make Oracle appear to behave in the same way as other RDBMS, and I think that isn't realistic.  To get the best out of any database you need to exploit its features and avoid its "quirks".  Oracle's features include returning a data-sets as a REFCURSOR where you don't know in advance how many records are in the set - you and Oracle only find that out when you fetch past the last row.  Similarly, if you're talking of deleting a million rows and trying to return all the deleted data into a collection of some sort that gets passed back to the invoker, what are you going to do with that data?  It sounds like you'd be better off trying a "SELECT .. FOR UPDATE" and then deleting each row - inefficient in Oracle, but that seems to be of secondary importance.

I really think the further you go with this, the more problems you're likely to encounter and you're never going to get any decent performance out of what is a very expensive RDBMS.

C


On 30 April 2013 09:17, Glyph <gl...@tw...> wrote:

On Apr 30, 2013, at 1:04 AM, Mark Harrison <mh...@pi...> wrote:


On 4/30/13 12:21 AM, Glyph wrote:

As I keep pointing out:


Currently, I'm doing this with host variables, but that's a limited solution [...]

You're trying but apparent failing to make your point...

I tried helping you out earlier, but your response left me not
caring a lot whether or not you solved your problem or not.

I do appreciate you taking the time to help, even if your answer wasn't directly useful to me.  I realize what I'm looking for is a bit esoteric.  "What you are trying to do is impossible" is a totally acceptable answer - I am really just trying to understand why Oracle makes this impossible.

The database-specificity of cursor.var() makes sense to me, idiomatically, but the restricted size of query results seems odd.  For example, if I want to 'delete...returning...into...' a million rows, rather than the default allowed ~1000 or so, how can I do that?  Would a pipelined procedure with PIPE ROW address this problem, and the general expectation is just that I'd be able to put such a procedure into my schema?

-glyph


------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
cx-oracle-users mailing list
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, Sitz: Stuttgart, AG Stuttgart, HRA 12167, UST-IdNr. DE 811 655 252
Persönlich haftende Gesellschafterin: GEHE Pharma Handel GmbH, Sitz: Stuttgart, AG Stuttgart, HRB 14591,
Geschäftsführer: André Blümel (Vorsitzender), Rainer Baumgärtner, Dr. Peter Schreiner
Weitere Gesellschafter: GEHE Informatik Services-Verwaltungs GmbH, Sitz: Stuttgart, AG Stuttgart, HRB 15843
Geschäftsführer: Rainer Baumgärtner, Dr. Stefan Grill

______________________________________________________________________
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] an old question

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

Hi -
I've been following the discussion in this thread and have to say I think
the objective of the whole exercise is flawed.  You seem to be trying to
write a set of wrappers to make Oracle appear to behave in the same way as
other RDBMS, and I think that isn't realistic.  To get the best out of any
database you need to exploit its features and avoid its "quirks".  Oracle's
features include returning a data-sets as a REFCURSOR where you don't know
in advance how many records are in the set - you and Oracle only find that
out when you fetch past the last row.  Similarly, if you're talking of
deleting a million rows and trying to return all the deleted data into a
collection of some sort that gets passed back to the invoker, what are you
going to do with that data?  It sounds like you'd be better off trying a
"SELECT .. FOR UPDATE" and then deleting each row - inefficient in Oracle,
but that seems to be of secondary importance.

I really think the further you go with this, the more problems you're
likely to encounter and you're never going to get any decent performance
out of what is a very expensive RDBMS.

C



On 30 April 2013 09:17, Glyph <gl...@tw...> wrote:

>
> On Apr 30, 2013, at 1:04 AM, Mark Harrison <mh...@pi...> wrote:
>
> On 4/30/13 12:21 AM, Glyph wrote:
>
> As I keep pointing out:
>
> Currently, I'm doing this with host variables, but that's a limited
> solution [...]
>
>
> You're trying but apparent failing to make your point...
>
> I tried helping you out earlier, but your response left me not
> caring a lot whether or not you solved your problem or not.
>
>
> I do appreciate you taking the time to help, even if your answer wasn't
> directly useful to me.  I realize what I'm looking for is a bit esoteric.
>  "What you are trying to do is impossible" is a totally acceptable answer -
> I am really just trying to understand *why* Oracle makes this impossible.
>
> The database-specificity of cursor.var() makes sense to me, idiomatically,
> but the restricted size of query results seems odd.  For example, if I want
> to 'delete...returning...into...' a million rows, rather than the default
> allowed ~1000 or so, how can I do that?  Would a pipelined procedure with
> PIPE ROW address this problem, and the general expectation is just that I'd
> be able to put such a procedure into my schema?
>
> -glyph
>
>
>
> ------------------------------------------------------------------------------
> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
> Get 100% visibility into your production application - at no cost.
> Code-level diagnostics for performance bottlenecks with <2% overhead
> Download for free and get started troubleshooting in minutes.
> http://p.sf.net/sfu/appdyn_d2d_ap1
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] an old question

[Paul M. <p.f...@gm...>]

On 30 April 2013 09:17, Glyph <gl...@tw...> wrote:

> I do appreciate you taking the time to help, even if your answer wasn't
> directly useful to me.  I realize what I'm looking for is a bit esoteric.
>  "What you are trying to do is impossible" is a totally acceptable answer -
> I am really just trying to understand *why* Oracle makes this impossible.
>

I think the big issue here is that people with an Oracle background (like
me) just don't understand the question, because fundamentally the
principles are different. (I say this because I've had endless frustrating
conversations with a friend who's an Ingres specialist, and we always end
up realising that neither of us *actually* understands the fundamental
terms that the other person is using, even though we thought we did...)

This may be basic stuff you already know, but for purposes of clarity,
Oracle runs statements in 3 steps - parse, execute and fetch. Only select
statements need a fetch phase, all other statements run in the execute
phase. Think of it as "only select statements produce a cursor you can
fetch from" if you like.

The basic problem here is that PL/SQL is not a select, so it completes in
the execute phase. With PL/SQL you can *create* cursors, and pass them
round, but they are not "the result of the execute phase" and so won't ever
be accessible directly from the cx_Oracle cursor object. That's why you
need a variable of some sort.

One exception is that you can *wrap* your PL/SQL in a select. But to do
that you need a defined function in the schema - that's where the
suggestion of a pipelined function comes from. With that you'd do
cursor.execute("select * from
table(function_doing_your_work_and_piping_rows())") and then read the
cursor. But yes, that requires a function defined in the schema. And it's
going to return a specific set of columns (so you need one per table, in
effect). The reason for the latter restriction is that Oracle expects
cursors to be statically defined (SYS_REFCURSOR is an exception, but that's
getting a bit deep for now).

Someone said that you may be able to have an "inline" function definition
in your SQL in the not yet released 12c. That might help you, but I presume
you'd want a solution that worked for a currently released version :-)


> The database-specificity of cursor.var() makes sense to me, idiomatically,
> but the restricted size of query results seems odd.  For example, if I want
> to 'delete...returning...into...' a million rows, rather than the default
> allowed ~1000 or so, how can I do that?  Would a pipelined procedure with
> PIPE ROW address this problem, and the general expectation is just that I'd
> be able to put such a procedure into my schema?
>

I believe that how that works is that you're returning the data into a
PL/SQL array, then converting that into a cursor. So there's a materialised
array of values in there, and allowing for a million entries will have a
memory cost. You could declare an array that big, if the memory wasn't an
issue to you, but again it's about static objects, you have to declare
*some* limit up front.

Yes a procedure with PIPE ROW would address this - that's the key point
about PIPE ROW is taht it sends the rows "on demand" rather than
materialising the whole array.

Paul

Re: [cx-oracle-users] an old question

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

On Apr 30, 2013, at 1:04 AM, Mark Harrison <mh...@pi...> wrote:

> On 4/30/13 12:21 AM, Glyph wrote:
>> As I keep pointing out:
>> 
>>>> Currently, I'm doing this with host variables, but that's a limited solution [...]
> 
> You're trying but apparent failing to make your point...
> 
> I tried helping you out earlier, but your response left me not
> caring a lot whether or not you solved your problem or not.

I do appreciate you taking the time to help, even if your answer wasn't directly useful to me.  I realize what I'm looking for is a bit esoteric.  "What you are trying to do is impossible" is a totally acceptable answer - I am really just trying to understand why Oracle makes this impossible. 

The database-specificity of cursor.var() makes sense to me, idiomatically, but the restricted size of query results seems odd.  For example, if I want to 'delete...returning...into...' a million rows, rather than the default allowed ~1000 or so, how can I do that?  Would a pipelined procedure with PIPE ROW address this problem, and the general expectation is just that I'd be able to put such a procedure into my schema?

-glyph

Re: [cx-oracle-users] an old question

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

On 4/30/13 12:21 AM, Glyph wrote:
> As I keep pointing out:
>
>>> Currently, I'm doing this with host variables, but that's a limited solution [...]

You're trying but apparent failing to make your point...

I tried helping you out earlier, but your response left me not
caring a lot whether or not you solved your problem or not.

Re: [cx-oracle-users] an old question

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

On Apr 29, 2013, at 1:00 PM, Paul Moore <p.f...@gm...> wrote:

> OK, got you. (BTW, sorry for the delay in responding, your mail only just arrived on the list).

Yeah, a couple of outgoing mails got stuck.  Sorry!

> I don't think this is possible. DELETE RETURNING is a PL/SQL construct which gives you the deleted rows back as  a collection. You *might* be able to put those into a refcursor somehow, but frankly I can't think of a way that doesn't need you to create a stored procedure/function in the database. If you can do that, a pipelined function would probably give you what you need. (Someone else posted an example elsewhere in the thread that you could probably get to work).

Backing off from "delete returning" for a moment; what is the implicit "output" cursor called from an inline PL/SQL statement?  If I knew that, I could at least try stuffing some rows into it.  Is it just inaccessible from PL/SQL?

-glyph

Re: [cx-oracle-users] an old question

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

On Apr 29, 2013, at 1:25 PM, Anthony Tuininga <ant...@gm...> wrote:

> It is possible -- at least so long as I understand what you are trying to do. Here is an example:

As I keep pointing out:

>> Currently, I'm doing this with host variables, but that's a limited solution [...]

I'm aware of the cursor.var() solution; I'm looking for one which doesn't involve one of those, since that means (A) custom Python code for Oracle, where other databases (PostgreSQL, MySQL, SQLite) just require custom SQL generation, and (B) foreknowledge of the maximum number of results that will come back from one of these statements.

-glyph

Re: [cx-oracle-users] an old question

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

It is possible -- at least so long as I understand what you are trying to
do. Here is an example:

NUM_ENTRIES  = 25 # maximum number of entries that can be expected

connection = cx_Oracle.Connection("user/pw@tns")
cursor = connection.cursor()
returningVar = cursor.arrayvar(int, NUM_ENTRIES)
cursor.execute("""
        begin
            delete from TestExecuteMany
            where rownum <= 5
            returning IntCol bulk collect into :output;
        end;""",
        output = returningVar)
for intCol in returningVar.getvalue():
    print "DELETED", intCol

Hope that helps.

Anthony


On Mon, Apr 29, 2013 at 2:00 PM, Paul Moore <p.f...@gm...> wrote:

> OK, got you. (BTW, sorry for the delay in responding, your mail only just
> arrived on the list).
>
> I don't think this is possible. DELETE RETURNING is a PL/SQL construct
> which gives you the deleted rows back as  a collection. You *might* be able
> to put those into a refcursor somehow, but frankly I can't think of a way
> that doesn't need you to create a stored procedure/function in the
> database. If you can do that, a pipelined function would probably give you
> what you need. (Someone else posted an example elsewhere in the thread that
> you could probably get to work).
>
> Sorry I can't be of more help...
> Paul
>
>
> On 19 April 2013 17:56, Glyph <gl...@tw...> wrote:
>
>>
>> On Apr 19, 2013, at 1:04 AM, Paul Moore <p.f...@gm...> wrote:
>>
>> On 18 April 2013 23:48, Glyph <gl...@tw...> wrote:
>>
>>> Thanks for responding, but, unfortunately, no.  I put a comment there,
>>> but in short; I know that it's unusual, but there are cases where inline
>>> PL/SQL can do things that regular SQL can't.
>>>
>>> *Is* there any way to emit data back to the application from an inline
>>> block like this?
>>>
>>
>> Can you give an example of your actual code? In general, the answer to
>> your question is "no", because PL/SQL blocks are not select statements and
>> therefore do not return rows via the cursor. But there are ways, depending
>> on the details of what you want to do. Your requirement to not modify the
>> schema (specifically not creating stored procedures) makes it hard,
>> though...
>>
>>
>> I want to do something like this:
>>
>> cursor.execute("""
>> begin
>> -- mumble declare 'something' somehow
>> delete from foo where bar = 1 returning baz into :something;
>> -- send rows from :something into the output that will come from
>> .fetchall();
>> end;
>> """)
>> cursor.fetchall()
>>
>>
>> because the desire to atomically get rows out of a 'delete' statement is
>> not a table-specific stored procedure.  I also don't want to create any
>> temporary tables, because again, this is not in any way specific to one
>> table; I just want to know what it is that I deleted or updated when I look
>> at the results of a delete or update statement.
>>
>> Currently, I'm doing this with host variables, but that's a limited
>> solution because I have to know ahead of time whether I expect a single
>> row, or multiple rows, and if I expect multiple rows, array variables can
>> hold a maximum of 4000 elements (or is it 4000 bytes? documentation seems
>> unclear).
>>
>> Anyway, generating these as "regular" database output seems like a much
>> saner and more scalable option, so I'd love to know how to do it.
>>
>> Thanks for your time!
>>
>> -glyph
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Try New Relic Now & We'll Send You this Cool Shirt
>> New Relic is the only SaaS-based application performance monitoring
>> service
>> that delivers powerful full stack analytics. Optimize and monitor your
>> browser, app, & servers with just a few lines of code. Try New Relic
>> and get this awesome Nerd Life shirt!
>> http://p.sf.net/sfu/newrelic_d2d_apr
>>
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] an old question

[Paul M. <p.f...@gm...>]

OK, got you. (BTW, sorry for the delay in responding, your mail only just
arrived on the list).

I don't think this is possible. DELETE RETURNING is a PL/SQL construct
which gives you the deleted rows back as  a collection. You *might* be able
to put those into a refcursor somehow, but frankly I can't think of a way
that doesn't need you to create a stored procedure/function in the
database. If you can do that, a pipelined function would probably give you
what you need. (Someone else posted an example elsewhere in the thread that
you could probably get to work).

Sorry I can't be of more help...
Paul


On 19 April 2013 17:56, Glyph <gl...@tw...> wrote:

>
> On Apr 19, 2013, at 1:04 AM, Paul Moore <p.f...@gm...> wrote:
>
> On 18 April 2013 23:48, Glyph <gl...@tw...> wrote:
>
>> Thanks for responding, but, unfortunately, no.  I put a comment there,
>> but in short; I know that it's unusual, but there are cases where inline
>> PL/SQL can do things that regular SQL can't.
>>
>> *Is* there any way to emit data back to the application from an inline
>> block like this?
>>
>
> Can you give an example of your actual code? In general, the answer to
> your question is "no", because PL/SQL blocks are not select statements and
> therefore do not return rows via the cursor. But there are ways, depending
> on the details of what you want to do. Your requirement to not modify the
> schema (specifically not creating stored procedures) makes it hard,
> though...
>
>
> I want to do something like this:
>
> cursor.execute("""
> begin
> -- mumble declare 'something' somehow
> delete from foo where bar = 1 returning baz into :something;
> -- send rows from :something into the output that will come from
> .fetchall();
> end;
> """)
> cursor.fetchall()
>
>
> because the desire to atomically get rows out of a 'delete' statement is
> not a table-specific stored procedure.  I also don't want to create any
> temporary tables, because again, this is not in any way specific to one
> table; I just want to know what it is that I deleted or updated when I look
> at the results of a delete or update statement.
>
> Currently, I'm doing this with host variables, but that's a limited
> solution because I have to know ahead of time whether I expect a single
> row, or multiple rows, and if I expect multiple rows, array variables can
> hold a maximum of 4000 elements (or is it 4000 bytes? documentation seems
> unclear).
>
> Anyway, generating these as "regular" database output seems like a much
> saner and more scalable option, so I'd love to know how to do it.
>
> Thanks for your time!
>
> -glyph
>
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] cx-oracle-users Digest, Vol 81, Issue 18

[Tamás G. <gt...@gt...>]

Date: Fri, 19 Apr 2013 09:56:40 -0700

> From: Glyph <gl...@tw...>
> Subject: Re: [cx-oracle-users] an old question
> To: cx-...@li...
> Message-ID: <D50...@tw...>
> Content-Type: text/plain; charset="iso-8859-1"
>
>
> On Apr 19, 2013, at 1:04 AM, Paul Moore <p.f...@gm...> wrote:
>
> > On 18 April 2013 23:48, Glyph <gl...@tw...> wrote:
> > Thanks for responding, but, unfortunately, no.  I put a comment there,
> but in short; I know that it's unusual, but there are cases where inline
> PL/SQL can do things that regular SQL can't.
> >
> > Is there any way to emit data back to the application from an inline
> block like this?
> >
> > Can you give an example of your actual code? In general, the answer to
> your question is "no", because PL/SQL blocks are not select statements and
> therefore do not return rows via the cursor. But there are ways, depending
> on the details of what you want to do. Your requirement to not modify the
> schema (specifically not creating stored procedures) makes it hard,
> though...
>
> I want to do something like this:
>
> cursor.execute("""
> begin
> -- mumble declare 'something' somehow
> delete from foo where bar = 1 returning baz into :something;
> -- send rows from :something into the output that will come from
> .fetchall();
> end;
> """)
> cursor.fetchall()
>
> because the desire to atomically get rows out of a 'delete' statement is
> not a table-specific stored procedure.  I also don't want to create any
> temporary tables, because again, this is not in any way specific to one
> table; I just want to know what it is that I deleted or updated when I look
> at the results of a delete or update statement.
>
> Currently, I'm doing this with host variables, but that's a limited
> solution because I have to know ahead of time whether I expect a single
> row, or multiple rows, and if I expect multiple rows, array variables can
> hold a maximum of 4000 elements (or is it 4000 bytes? documentation seems
> unclear).
>
> Anyway, generating these as "regular" database output seems like a much
> saner and more scalable option, so I'd love to know how to do it.
>
> Thanks for your time!
>
> -glyph
>

If you don't mind ugly solutions, then a PIPELINED function can return rows
just before  deletions:

CREATE PACKAGE ugly IS
  TYPE recs_typ IS TABLE OF foo%ROWTYPE INDEX BY PLS_INTEGER;

  CREATE FUNCTION hack(id IN INTEGER) RETURN recs_typ PIPELINED;
END;
/
CREATE PACKAGE BODY ugly IS
  CREATE FUNCTION hack(id IN INTEGER) RETURN recs_typ PIPELINED IS
    row foo%ROWTYPE;
  BEGIN
    FOR cur IN (SELECT ROWID FROM foo WHERE bar = id) LOOP
      SELECT * INTO row WHERE ROWID = cur.ROWID;
      PIPE ROW(sor);
      DELETE foo WHERE ROWID = cur.ROWID;
END;
/


GThomas

[cx-oracle-users] cx_Oracle with IIS 6

[Beliakov, M. <mbe...@it...>]

Good Monday!

I'm writing a cgi script and having an issue with importing cx_Oracle.

When I'm running the script from the command line it executes like it should and I can query the database like I should. However, when I request the script from a browser I get:

<type 'exceptions.ImportError'>: DLL load failed: Access is denied.
      args = ('DLL load failed: Access is denied.',)
      message = 'DLL load failed: Access is denied.'
If I remove the cx_Oracle and all references to it the file executes fine and I get a page.

The system:
Windows 2003 SP2
Python 2.7
cx_Oracle 5.1.2

I have read and execute permissions for IUSR on everything in oracle and python directories, but still no luck.

Does anyone has a suggestion?

Thank you,
Mikhail

Re: [cx-oracle-users] DatabaseError: ORA-01843: not a valid month

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

Or it's an issue with Oracle itself.
To be sure you could test with sql*plus: declare nvarchar variables, and
perform the same query.


2013/4/25 Andrey Nikolaev <nik...@gm...>

> Thanks Doug for the advice.
> I came across the issue initially in a dynamically generated code (in
> Django). So it's out of my control.
> My test case does not involve Django though. That's a clear indication
> that the issue lies within cx_Oracle code.
>
> --
> Best regards,
> Andrey Nikolaev                            mailto:
> Nik...@gm...
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>


-- 
Amaury Forgeot d'Arc

[cx-oracle-users] if you use cx_Oracle on a mac...

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

I just noticed that Oracle released 11.2 instantclient for Mac a few months back, which promises to un-complicate my development environment significantly.  From the official announcement: <https://blogs.oracle.com/opal/entry/os_x_users_11gr2_oracle>

You can really help us justify resources for the OS X bundle if you email me letting me know you are a fan.Since Instant Client is a free download, it is tricky for us to know how widely it gets used. My email address is … (read the blog post for his email).

If you use this, please be sure to let the Oracle developers that the availability of OCI on a mac is very helpful.

-glyph

Re: [cx-oracle-users] an old question

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

On Apr 19, 2013, at 1:04 AM, Paul Moore <p.f...@gm...> wrote:

> On 18 April 2013 23:48, Glyph <gl...@tw...> wrote:
> Thanks for responding, but, unfortunately, no.  I put a comment there, but in short; I know that it's unusual, but there are cases where inline PL/SQL can do things that regular SQL can't.
> 
> Is there any way to emit data back to the application from an inline block like this?
> 
> Can you give an example of your actual code? In general, the answer to your question is "no", because PL/SQL blocks are not select statements and therefore do not return rows via the cursor. But there are ways, depending on the details of what you want to do. Your requirement to not modify the schema (specifically not creating stored procedures) makes it hard, though...

I want to do something like this:

cursor.execute("""
begin
-- mumble declare 'something' somehow
delete from foo where bar = 1 returning baz into :something;
-- send rows from :something into the output that will come from .fetchall();
end;
""")
cursor.fetchall()

because the desire to atomically get rows out of a 'delete' statement is not a table-specific stored procedure.  I also don't want to create any temporary tables, because again, this is not in any way specific to one table; I just want to know what it is that I deleted or updated when I look at the results of a delete or update statement.

Currently, I'm doing this with host variables, but that's a limited solution because I have to know ahead of time whether I expect a single row, or multiple rows, and if I expect multiple rows, array variables can hold a maximum of 4000 elements (or is it 4000 bytes? documentation seems unclear).

Anyway, generating these as "regular" database output seems like a much saner and more scalable option, so I'd love to know how to do it.

Thanks for your time!

-glyph

Re: [cx-oracle-users] Full Table Scan - Bind Variables

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

Hi Doug,

Thanks a lot. You made my day. I was struggling with this.

Please have a look at http://dpaste.com/1073386/

In summary, if I change the parameter type variable from Unicode to str
then the query executes in no time at all.

Here is the result of the query with the DUMP function

>>> WMS_cursor.execute("SELECT
dump(ID,1016),dump(:ID,1016),dump(SUB_ID,1016),dump(:SUB_ID,1016) FROM
ASSETS WHERE ID = :ID and SUB_ID =:SUB_ID" ,{'ID':ID, 'SUB_ID':SUB_ID})
<__builtin__.OracleCursor on <cx_Oracle.Connection to local@local_prod>>
>>> row=WMS_cursor.fetchone()
>>> print row
('Typ=1 Len=3 CharacterSet=AL32UTF8: 30,31,32', 'Typ=1 Len=6
CharacterSet=AL16UTF16: 0,30,0,31,0,32', 'Typ=1 Len=6
CharacterSet=AL32UTF8: 32,31,37,32,30,34', 'Typ=1 Len=12
CharacterSet=AL16UTF16: 0,32,0,31,0,37,0,32,0,30,0,34')

Database column is shown to be AL32UTF8 but the bind parameter value was
AL16UTF16

I changed the parameter value to str from unicode and now it works
impeccable.

This has been very helpful. Appreciate If you would have more information
to share.

Thanks again.

Regards,
Guddu

On Thu, Apr 25, 2013 at 3:15 AM, Doug Henderson <djh...@te...>wrote:

>  There are situations where Oracle's implicit type conversion rules
> prevent indexes from begin used.
>
> To investigate, try a query along these lines:
>
> select dump(id, 1016), dump(:id, 1016), dump(sub_id, 1016), dump(:sub_id,
> 1016)
> from asset
> where id = :id and sub_id = :sub_id and rownum <= 1;
>
> The dump function will display the type, value and character set of its
> first argument. This should tell you if the column and the parameter have
> different types. If one is VARCHAR, and the other is NVARCHAR, then
> implicit conversion of the column to match the parameter will prevent the
> index from being used. Try to make the conversion apply to the parameter
> instead of the column.
>
> While not exactly natural to write, you might try something like:
>
> *SELECT BATCH FROM ASSET WHERE :ID = ID and :SUB_ID = SUB_ID*
>
> While that works when mixing char and numeric types, I'm not sure if it
> will work for different string types. A better solution may be to
> explicitly coerce the query parameters to the same data type as the
> columns. There are several ways to do this, either on the python side or
> the database side.
>
> - Doug
>
>
> On 2013-04-24 21:53, Anurag Chourasia wrote:
>
> Dear Friends,
>
>  My query is doing a full table scan when running from within the Web
> App.
>
>  I captured the plan of execution using LAB128 and this is what i see
> http://dpaste.com/1072780/ The plan indicates a full table scan.
>
>  The query in question is *SELECT BATCH FROM ASSET WHERE ID = :ID and SUB_ID
> = :SUB_ID*
>
>  The columns in the where clause are both *varchar2* and in cx_oracle,
> the query looks like this
>
>  WMS_cursor.execute("SELECT BATCH FROM ASSET WHERE ID = :ID and SUB_ID =
> :SUB_ID" ,{'ID':ID, 'SUB_ID':SUB_ID})
>
>  ID and SUB_ID are both *unicode* type variables in Python.
>
>  If i generate the plan of the same query in SQL Tools, i see that the
> index is being used. Plan as seen in SQLTools is
> http://dpaste.com/1072779/
>
>  The index *ASSET_IND_7* is a new index that was created on this table.
>
>  Why should the query do a FULL TABLE SCAN when running from inside the
> WEB App? Is it something wrong with the Bind Variables?
>
>  Please help.
>
>  Regards,
> Guddu
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
>
>
>
> _______________________________________________
> cx-oracle-users mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> --
> Doug Henderson, Calgary, Alberta, Canada
>
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Full Table Scan - Bind Variables

[Doug H. <djh...@te...>]

There are situations where Oracle's implicit type conversion rules 
prevent indexes from begin used.

To investigate, try a query along these lines:

select dump(id, 1016), dump(:id, 1016), dump(sub_id, 1016), 
dump(:sub_id, 1016)
from asset
where id = :id and sub_id = :sub_id and rownum <= 1;

The dump function will display the type, value and character set of its 
first argument. This should tell you if the column and the parameter 
have different types. If one is VARCHAR, and the other is NVARCHAR, then 
implicit conversion of the column to match the parameter will prevent 
the index from being used. Try to make the conversion apply to the 
parameter instead of the column.

While not exactly natural to write, you might try something like:
*SELECT BATCH FROM ASSET WHERE :ID = ID and :SUB_ID = SUB_ID*

While that works when mixing char and numeric types, I'm not sure if it 
will work for different string types. A better solution may be to 
explicitly coerce the query parameters to the same data type as the 
columns. There are several ways to do this, either on the python side or 
the database side.

- Doug

On 2013-04-24 21:53, Anurag Chourasia wrote:
> Dear Friends,
>
> My query is doing a full table scan when running from within the Web App.
>
> I captured the plan of execution using LAB128 and this is what i see 
> http://dpaste.com/1072780/ The plan indicates a full table scan.
>
> The query in question is *SELECT BATCH FROM ASSET WHERE ID = :ID and 
> SUB_ID = :SUB_ID*
>
> The columns in the where clause are both *varchar2* and in cx_oracle, 
> the query looks like this
>
> WMS_cursor.execute("SELECT BATCH FROM ASSET WHERE ID = :ID and SUB_ID 
> = :SUB_ID" ,{'ID':ID, 'SUB_ID':SUB_ID})
>
> ID and SUB_ID are both *unicode* type variables in Python.
>
> If i generate the plan of the same query in SQL Tools, i see that the 
> index is being used. Plan as seen in SQLTools is 
> http://dpaste.com/1072779/
>
> The index *ASSET_IND_7* is a new index that was created on this table.
>
> Why should the query do a FULL TABLE SCAN when running from inside the 
> WEB App? Is it something wrong with the Bind Variables?
>
> Please help.
>
> Regards,
> Guddu
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


-- 
Doug Henderson, Calgary, Alberta, Canada

[cx-oracle-users] Full Table Scan - Bind Variables

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

Dear Friends,

My query is doing a full table scan when running from within the Web App.

I captured the plan of execution using LAB128 and this is what i see
http://dpaste.com/1072780/ The plan indicates a full table scan.

The query in question is *SELECT BATCH FROM ASSET WHERE ID = :ID and SUB_ID
= :SUB_ID*

The columns in the where clause are both *varchar2* and in cx_oracle, the
query looks like this

WMS_cursor.execute("SELECT BATCH FROM ASSET WHERE ID = :ID and SUB_ID =
:SUB_ID" ,{'ID':ID, 'SUB_ID':SUB_ID})

ID and SUB_ID are both *unicode* type variables in Python.

If i generate the plan of the same query in SQL Tools, i see that the index
is being used. Plan as seen in SQLTools is http://dpaste.com/1072779/

The index *ASSET_IND_7* is a new index that was created on this table.

Why should the query do a FULL TABLE SCAN when running from inside the WEB
App? Is it something wrong with the Bind Variables?

Please help.

Regards,
Guddu

Re: [cx-oracle-users] DatabaseError: ORA-01843: not a valid month

[Andrey N. <nik...@gm...>]

Thanks Doug for the advice.
I came across the issue initially in a dynamically generated code (in
Django). So it's out of my control.
My test case does not involve Django though. That's a clear indication that
the issue lies within cx_Oracle code.

--
Best regards,
Andrey Nikolaev                            mailto:Nik...@gm...