Tuesday, May 17, 2011

Execute PL/SQL on Oracle from Python Script

I had a chance to write a Python script that connects to Oracle and do some SQL..
I thought of bloging the basic steps., that I followed..

Install Oracle client (below packages), If the box doesn't have Oracle Instance., to run the script.

The local box requires the below packages to be installed., to run the script.

oracle-instantclient11.2-basic-11.xxxxx.xx.zip
cx_Oracle-5.xxx.tar.gz

Once the installation is done, write the script to connect to Oracle and do process.

#The Script uses the cx_Oracle to connect to the Oracle instance.. It can be of a local or remote instance. 

def grantSelectOnOracle():
    try:
        import cx_Oracle
        #Checking for the presence of database
        con = cx_Oracle.connect(u"%s/%s@%s:%s/%s" % ('DATABASE_USERNAME',
                                                     'DATABASE_PASSWORD',
                                                     'DATABASE_HOST',
                                                     'DATABASE_PORT',
                                                     'DATABASE_NAME'))


#Once the connection is established, the script tries to run a PL/SQL block, here #it grant select permission on the User passed.

        try:
            #Granting select privilege to read only user on all reports tables
            cur = con.cursor()
            grantCommand = """
begin
for i in (select table_name from user_tables)
loop
execute immediate 'grant select on '|| i.table_name||' to %s';
end loop;
end;""" % ( 'DATABASE_READ_ONLY_USER')
            cur.execute(u"%s" % grantCommand)
            cur.close()
            logger.info("Done.")
        except:
            logger.warn("Some Problem Occured in Granting Read Only Permission to User. Please set permissions Manually")
            pass
        con.close()
    except Exception, e:
        logger.error("Connection problem occured with Oracle")
        pass

Here it is., now enjoy with the Python script to execute PlSql's remotely on Oracle......!





No comments:

Post a Comment