Executing script by using function callproc from cx_Oracle module in python 2.7.5

Question

I'm relatively new to Python. I'm currently working on SQL statement execution in Oracle DB.

When I execute query:

query = 'select * from table' 
cursor.execute(query)
result = cursor.fetchall()

everything is going fine, but when I try to execute script:

script in plain text:

begin
 SIEBEL_DBA.X_DR_DEPLOY(id => '1-4NANEI', env_code => 'SVE_SIT');
end;
/

code from script

script = "begin\nSIEBEL_DBA.X_DR_DEPLOY(id => '1-4NANEI', env_code => 'SVE_SIT');\nend;"
cursor.execute(script)

result = cursor.fetchall()

I get an exception, that this is not a query, but still this script has worked.

So from what I've googled, looks like I should use callproc function:

cursor.callproc['SIEBEL_DBA.X_DR_DEPLOY',{'id' : '1-4NANEI', 'env_code' : 'SVE_SIT'}]
connection.commit()

result = cursor.fetchall()

When I'm executing this statement, I'm also getting exception, but this time nothing has been changed in DB:

'builtin_function_or_method' object has no attribute 'getitem'

Could someone please point where I'm not correct and how should I modify statement so it would be working.

Huge thanks in advance!

RESOLUTION:

I was frustrated by the syntax and the complexity of callproc and callfunc functions. I've found good resource: http://dbaportal.eu/sidekicks/sidekick-cx_oracle-code-paterns/#part1 in this link I found all needed info and examples on how to work with cx_Oracle library.

at the end I just needed to modify a bit my code:

cursor.callproc('SIEBEL_DBA.X_DR_DEPLOY', ['1-4NANEI', 'SVE_SIT'])

and the needed part was done, I didn't need to specify any return type, as script that I'm executing doesn't return any value, it just sets it.

Crayon

I was frustrated by the syntax and the complexity of callproc and callfunc functions. – Crayon

Answer 1

The exception is because you are using [ ] where you should be using ():

cursor.callproc('SIEBEL_DBA.X_DR_DEPLOY',{'id' : '1-4NANEI', 'env_code' : 'SVE_SIT'})

Keep in mind the return type is required:

Cursor.callfunc(name, returnType, parameters=[], keywordParameters = {})

Call a function with the given name. The return type is specified in the same notation as is required by setinputsizes(). The sequence of parameters must contain one entry for each argument that the function expects. Any keyword parameters will be included after the positional parameters. The result of the call is the return value of the function.

Horologist

Thanks for you're input, It cleared a bit my thoughts but not entirely! I've added resolution to my first post and also resource that I've read in order to fix it. – Crayon