Call procedure on Oracle 11.2.0 via JayDeBeApi in Python

Question

I am trying to call dbms_random.seed(42) on my DB, see ref. I need to use python and the JayDeBeApi connector. So far I've only been able to execute select statement without issue. I fail to understand what I am doing wrong.

It seems that JayDeBeApi does not provide the callproc method, so I cannot use it:

AttributeError: 'Cursor' object has no attribute 'callproc'

I've naively tried:

conn = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver',
    ['jdbc:oracle:thin:@server:1521/dbname', 'user', 'password'])
curs = conn.cursor()
sql="exec dbms_random.seed(42)"
curs.execute(sql)

but this leads to: Error: ORA-00900: invalid SQL statement

I've tried two solutions which seems to have correct syntax, but since the generator is not deterministic, I believe they actually failed:

Using begin/end:

sql="begin dbms_random.seed(42); end;"
curs.execute(sql)

Using call:

sql="{ call dbms_random.seed(42) }"
curs.execute(sql)

So my question is: how do I call dbms_random.seed(42) on Oracle using JayDeBeApi ? As a side question how do I check that a statement has actually failed to execute (no exception was thrown and return value for execute is undefined.)

Update:

In fact the seed initialization is working as expected, since the results are what I expect for:

sql="SELECT DBMS_RANDOM.value FROM dual"
curs.execute(sql)
data = curs.fetchall()
print(data)

However I still see some odd behavior for my random query selection:

SELECT *
FROM   (
    SELECT *
    FROM   table
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 21;

For some reason in the latter case, the DBMS_RANDOM.RANDOM is actually random...

score 0 · Accepted Answer · answered Oct 19 '15 at 07:21

After multiple trial and error, I believe this is just a side effect. Since I was not doing the cleanup pass:

curs.close()
conn.close()
jpype.shutdownJVM()

The query would lead to somewhat undefined behavior. Now that I have a proper cleanup code, I am getting deterministic results when calling multiples times my python script.

Call procedure on Oracle 11.2.0 via JayDeBeApi in Python

1 Answers1