Re: [cx-oracle-users] cx_Oracle cannot parse this convoluted SQL statement

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

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