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

[Peter M. <pma...@ya...>]

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)