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.

I was frustrated by the syntax and the complexity of callproc and callfunc functions. — user2768632
– user2768632, Commented Feb 3, 2014 at 11:55

Burhan Khalid · Accepted Answer · 2014-01-31 12:05:11Z

3

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.

answered Jan 31, 2014 at 12:05

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2768632 Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related