Py and SQL

[Rod Person]

On Wed, 30 Nov 2011 15:30:48 -0500
Verde Denim <tdldev at gmail.com> wrote:

> All
> I have a sql script that I've included in a simple Py file that gives
> an error in the SQL. The problem is that the SQL code executes
> correctly in a database IDE environment (in this case ora developer).
> So, I'm concluding that I'm doing something amiss in the Py code.
> Does anyone see why this code would return a 'missing expression' sql
> error? Essentially, the code should start, ask for a privilege, and
> then collect the priv, role, and user data. Any input is appreciated.
> 
> #!/bin/bash
> import time
> import cx_Oracle
> 
> dbConn = cx_Oracle.connect('juser', 'pass', '1.2.3.4:/orcl:DEDICATED',
>              cclass = "ABC", purity = cx_Oracle.ATTR_PURITY_SELF)
> 
> pStart = time.time()
> 
> dbVersion = dbConn.version.split(".")
> 
> majVer = dbVersion[0]
> 
> print "Oracle Version: %s" %(majVer)
> print "Full Version: %s" %(dbConn.version)
> 
> dbCursor1 = dbConn.cursor()
> dbCursor1.execute('select lpad(' ', 2*level) || c "Privilege, Roles
> and Users" from ( select null p, name c from system_privilege_map
> where name like upper(\'%&enter_privliege%\') union select
> granted_role p, grantee c from dba_role_privs union select privilege
> p, grantee c from dba_sys_privs) start with p is null connect by p =
> prior c') dbRowResult = dbCursor1.fetchall()
> for dbRow in dbRowResult:
>     print dbRow
> 

Try changing the wrapping ' ' to " " and the inside double qoutes to
single, like this

dbCursor1.execute("select lpad(' ', 2*level) || c 'Privilege, Roles
and Users' from ( select null p, name c from system_privilege_map
where name like upper(\'%&enter_privliege%\') union select
granted_role p, grantee c from dba_role_privs union select privilege
p, grantee c from dba_sys_privs) start with p is null connect by p =
prior c")


-- 
Rod
The club is like groceries, and I jus bag a bi@$&!
­ Santonio Holmes on the Twitter