SQL Statement in Python

[MikePowell]

I am getting one of 2 errors and I don't know why. They are ORA-00911: invalid character and ORA-00933: (something about the statement not ended correctly). I am hoping a fresh set of eyes can help with it. Here is the code:

        orcl = cx_Oracle.connect("xxx/xxx@xxx")
        curs = orcl.cursor()
        database = "Database Connections\\xxx.sde\\xxx.xxx_xxx"
        sql1 = "create view RSIDs as " 
        sql2 = "select * " 
        sql3 = "from "+database+" where "
        sql4 = "xxxx like '%RSID%' or xxxxx like '%m%'"
        sql = str(sql1+sql2+sql3+sql4)
        print sql
        curs.execute(sql)
        curs.close()
        orcl.close()

The put x's in for the sensitive stuff but left all the slashes, comma, quotes etc... the way I have them. I tried several different ways with the slashes because I know python is funny with them but I can't get it to create the view. The statement works in SQLPLus. I am using Python 2.7. Any help is appreciated.

[David_JAnderson]

I am getting one of 2 errors and I don't know why. They are ORA-00911: invalid character and ORA-00933: (something about the statement not ended correctly). I am hoping a fresh set of eyes can help with it. Here is the code:

        orcl = cx_Oracle.connect("xxx/xxx@xxx")
        curs = orcl.cursor()
        database = "Database Connections\\xxx.sde\\xxx.xxx_xxx"
        sql1 = "create view RSIDs as " 
        sql2 = "select * " 
        sql3 = "from "+database+" where "
        sql4 = "xxxx like '%RSID%' or xxxxx like '%m%'"
        sql = str(sql1+sql2+sql3+sql4)
        print sql
        curs.execute(sql)
        curs.close()
        orcl.close()

The put x's in for the sensitive stuff but left all the slashes, comma, quotes etc... the way I have them. I tried several different ways with the slashes because I know python is funny with them but I can't get it to create the view. The statement works in SQLPLus. I am using Python 2.7. Any help is appreciated.

Have you checked the value of the database variable?  I think in that in the SQL statement it needs to just be the table name, without the Database Connections\\xxx.sde\\ prefix.  I am not sure why you have that as a variable as it does not seem to change.  You might also consider using a multiline string instead of a bunch of concatenated together variables.

[MikePowell]

Wow, that seem to be it. I took out the "database" variable and just put in the database table in the statement and that worked. It is creating the view. Thanks for the response

[ShawnThorne]

I was getting the same ORA-00911 & ORA-00933 errors as well.  But after removing the reference to the SDE Connection File, it worked.

import cx_Oracle

def main():

    try:

        orcl = cx_Oracle.connect("xxxx/xxxx@XXXXXX/XXXXXX")
        curs = orcl.cursor()

        vw = ("COUNTRIES_PY_VIEW")
        fc = ("COUNTRIES")

        createView = (" CREATE OR REPLACE VIEW " + vw + " AS (select * from " + fc + " where country like '%States%' or country = 'Canada')")

        print ("\n Create View Syntax ---> " + createView)
        curs.execute(createView)
        print ("\n Successfully created the View '" + vw + "'.\n")

        orcl.close()

        print ("\n Completed!! \n")

        del orcl
        del curs
        del createView
        del fc
        del vw

    except arcpy.ExecuteError:
        print (arcpy.GetMessages(2))

    except Exception as e:
        print (e[0])


if __name__ == '__main__':
    main()


Results :

 Create View Syntax --->  CREATE OR REPLACE VIEW COUNTRIES_PY_VIEW AS (select * from COUNTRIES where country like '%States%' or country = 'Canada')

 Successfully created the View 'COUNTRIES_PY_VIEW'.


 Completed!! 

[MikePowell]

Thanks for looking into it