Thread: [cx-oracle-users] cx_Oracle cannot parse this convoluted SQL statement
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Peter M. <pma...@ya...> - 2006-10-31 17:36:52
|
cx_Oracle worked for me just fine - thanks.
With cx_Oracle, I developed couple of simple web apps to query
Oracle database and display results in browser. So far so good.
That is, until DB developer asked me to run this convoluted SQL statement:
--
SELECT x neuron
FROM TABLE(SDO_RDF_MATCH(
'(?x r:type c:neuron)',
SDO_RDF_Models('senselab3'),
SDO_RDF_Rulebases('RDFS','senselab3_rb'),
SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class#'),
SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
),
null));
--
It is using new oracle RDF storage facility in Oracle 10g
enterprise edition release 10.2.0.2.0
python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)
All is fine when I do my usual SQL ping excercise, like:
--
import cx_Oracle
con = cx_Oracle.connect("user", "pass", "host/ORCLRDF")
cur = con.cursor()
sql = "select count(*) from myschema.senselab3_rdf_data"
cur.execute(sql)
res = cur.fetchall() # get results
print res
--
results are OK: [(10220,)]
When I use above convoluted SQL (which works correctly in oracle's own
SQL scratchpad app), I got ORA-00911 error:
Traceback (most recent call last):
File "<pyshell#15>", line 1, in -toplevel-
cur1.execute(sql1)
DatabaseError: ORA-00911: invalid character
For me, it looks I have couple problems:
(1) cx_Oracle might not be able to handle SQL statements with
proprietary Oracle tricks like collections - the TABLE(...) construct.
It it true? If so, i am just wasting my time.
(2) If cx_Oracle can handle even that convoluted SQL, I might be doing some
wrong quoting.
Maybe I need to pass all string constants as run-time parameters?
(3) I know I need a way to add 'myschema' to convoluted SQL,
something like 'use myschema' . But I doubt this is a problem:
looks like SQL was not even parsed correctly.
Any help, suggestions, insight?
What other information could be useful to localize the problem?
Peter
"Our enemies ... never stop thinking about new ways
to harm our country and our people, and neither do we."
G.W.Bush, Washington, D.C., Aug. 5, 2004
____________________________________________________________________________________
Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates
(http://voice.yahoo.com)
|
|
From: Peter M. <pma...@ya...> - 2006-10-31 22:07:16
|
cx_Oracle worked for me just fine - thanks.
With cx_Oracle, I developed couple of simple web apps to query
Oracle database and display results in browser. So far so good.
That is, until DB developer asked me to run this convoluted SQL statement:
--
SELECT x neuron
FROM TABLE(SDO_RDF_MATCH(
'(?x r:type c:neuron)',
SDO_RDF_Models('senselab3'),
SDO_RDF_Rulebases('RDFS','senselab3_rb'),
SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class#'),
SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
),
null));
--
It is using new oracle RDF storage facility in Oracle 10g
enterprise edition release 10.2.0.2.0
python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)
All is fine when I do my usual SQL ping excercise, like:
--
import cx_Oracle
con = cx_Oracle.connect("user", "pass", "host/ORCLRDF")
cur = con.cursor()
sql = "select count(*) from myschema.senselab3_rdf_data"
cur.execute(sql)
res = cur.fetchall() # get results
print res
--
results are OK: [(10220,)]
When I use above convoluted SQL (which works correctly in oracle's own
SQL scratchpad app), I got ORA-00911 error:
Traceback (most recent call last):
File "<pyshell#15>", line 1, in -toplevel-
cur1.execute(sql1)
DatabaseError: ORA-00911: invalid character
For me, it looks I have couple problems:
(1) cx_Oracle might not be able to handle SQL statements with
proprietary Oracle tricks like collections - the TABLE(...) construct.
It it true? If so, i am just wasting my time.
(2) If cx_Oracle can handle even that convoluted SQL, I might be doing some
wrong quoting.
Maybe I need to pass all string constants as run-time parameters?
(3) I know I need a way to add 'myschema' to convoluted SQL,
something like 'use myschema' . But I doubt this is a problem:
looks like SQL was not even parsed correctly.
Any help, suggestions, insight?
What other information could be useful to localize the problem?
Peter
"Our enemies ... never stop thinking about new ways
to harm our country and our people, and neither do we."
G.W.Bush, Washington, D.C., Aug. 5, 2004
__________________________________________________________________________________________
Check out the New Yahoo! Mail - Fire up a more powerful email and get things done faster.
(http://advision.webevents.yahoo.com/mailbeta)
|
|
From: Anthony T. <ant...@gm...> - 2006-10-31 18:06:32
|
I am not familiar with RDF storage facility but I can tell you a
number of things:
1) cx_Oracle can execute any statement that is supported by the OCI
2) SQL*Plus uses the OCI to execute its statements -- except for those
which are specific to itself (like startup/shutdown/set/define etc.)
3) Using bind variables generally avoids problems with quoting and
improves performance
So I would suggest that #1 in your list of possibilities is not a
problem and look into your #2. I've generally triple quoted my SQL
statements if they are large enough and this often eliminates quoting
problems as well.
Hope that helps.
Anthony
On 10/31/06, Peter Masiar <pma...@ya...> wrote:
> cx_Oracle worked for me just fine - thanks.
>
> With cx_Oracle, I developed couple of simple web apps to query
> Oracle database and display results in browser. So far so good.
>
> That is, until DB developer asked me to run this convoluted SQL statement=
:
>
> --
> SELECT x neuron
> FROM TABLE(SDO_RDF_MATCH(
> '(?x r:type c:neuron)',
> SDO_RDF_Models('senselab3'),
> SDO_RDF_Rulebases('RDFS','senselab3_rb'),
> SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class=
#'),
>
> SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
>
> SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
> ),
> null));
>
> --
>
> It is using new oracle RDF storage facility in Oracle 10g
> enterprise edition release 10.2.0.2.0
> python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)
>
> All is fine when I do my usual SQL ping excercise, like:
>
> --
> import cx_Oracle
> con =3D cx_Oracle.connect("user", "pass", "host/ORCLRDF")
> cur =3D con.cursor()
> sql =3D "select count(*) from myschema.senselab3_rdf_data"
> cur.execute(sql)
> res =3D cur.fetchall() # get results
> print res
> --
>
> results are OK: [(10220,)]
>
> When I use above convoluted SQL (which works correctly in oracle's own
> SQL scratchpad app), I got ORA-00911 error:
>
> Traceback (most recent call last):
> File "<pyshell#15>", line 1, in -toplevel-
> cur1.execute(sql1)
> DatabaseError: ORA-00911: invalid character
>
> For me, it looks I have couple problems:
>
> (1) cx_Oracle might not be able to handle SQL statements with
> proprietary Oracle tricks like collections - the TABLE(...) construct.
> It it true? If so, i am just wasting my time.
>
> (2) If cx_Oracle can handle even that convoluted SQL, I might be doing so=
me
> wrong quoting.
> Maybe I need to pass all string constants as run-time parameters?
>
> (3) I know I need a way to add 'myschema' to convoluted SQL,
> something like 'use myschema' . But I doubt this is a problem:
> looks like SQL was not even parsed correctly.
>
> Any help, suggestions, insight?
> What other information could be useful to localize the problem?
>
>
> Peter
>
> "Our enemies ... never stop thinking about new ways
> to harm our country and our people, and neither do we."
> =97 G.W.Bush, Washington, D.C., Aug. 5, 2004
>
>
>
> _________________________________________________________________________=
___________
> Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call =
rates
> (http://voice.yahoo.com)
>
>
> -------------------------------------------------------------------------
> 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 ea=
sier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronim=
o
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Amaury F. d'A. <ama...@gm...> - 2006-10-31 18:13:01
|
Peter Masiar wrote:
> cx_Oracle worked for me just fine - thanks.
>
> With cx_Oracle, I developed couple of simple web apps to query
> Oracle database and display results in browser. So far so good.
>
> That is, until DB developer asked me to run this convoluted SQL statement:
>
> --
> SELECT x neuron
> FROM TABLE(SDO_RDF_MATCH(
> '(?x r:type c:neuron)',
> SDO_RDF_Models('senselab3'),
> SDO_RDF_Rulebases('RDFS','senselab3_rb'),
> SDO_RDF_Aliases(SDO_RDF_Alias('c','http://senselab.med.yale.edu/class#'),
>
> SDO_RDF_Alias('i','http://senselab.med.yale.edu/instance#'),
>
> SDO_RDF_Alias('r','http://www.w3.org/1999/02/22-rdf-syntax-ns#')
> ),
> null));
>
> --
>
> It is using new oracle RDF storage facility in Oracle 10g
> enterprise edition release 10.2.0.2.0
> python 2.4.2 (#67) cx_Oracle 4.2 (according to history.txt)
>
> All is fine when I do my usual SQL ping excercise, like:
>
> --
> import cx_Oracle
> con = cx_Oracle.connect("user", "pass", "host/ORCLRDF")
> cur = con.cursor()
> sql = "select count(*) from myschema.senselab3_rdf_data"
> cur.execute(sql)
> res = cur.fetchall() # get results
> print res
> --
>
> results are OK: [(10220,)]
>
> When I use above convoluted SQL (which works correctly in oracle's own
> SQL scratchpad app), I got ORA-00911 error:
>
> Traceback (most recent call last):
> File "<pyshell#15>", line 1, in -toplevel-
> cur1.execute(sql1)
> DatabaseError: ORA-00911: invalid character
>
> For me, it looks I have couple problems:
>
> (1) cx_Oracle might not be able to handle SQL statements with
> proprietary Oracle tricks like collections - the TABLE(...) construct.
> It it true? If so, i am just wasting my time.
>
> (2) If cx_Oracle can handle even that convoluted SQL, I might be doing some
> wrong quoting.
> Maybe I need to pass all string constants as run-time parameters?
>
> (3) I know I need a way to add 'myschema' to convoluted SQL,
> something like 'use myschema' . But I doubt this is a problem:
> looks like SQL was not even parsed correctly.
>
> Any help, suggestions, insight?
> What other information could be useful to localize the problem?
>
cx_Oracle does not parse queries. It passes the content "as is" to the
Oracle server.
So you should be able to use RDF features without problems.
The Oracle server is complaining about the syntax of your query. What
it its exact content?
A very common error is to leave the semicolon [;] at the end of the
query (did it dozens of times). The semicolon is not part of the SQL
syntax; It belongs to other languages such as PL/SQL or SqlPlus
scripts.
Hope this helps,
--
Amaury Forgeot d'Arc
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X