Forum Stats

  • 3,768,916 Users
  • 2,252,873 Discussions
  • 7,874,795 Comments

Discussions

Keep getting ora-00922 while running python script cx_Oracle module

user1699124
user1699124 Member Posts: 7 Red Ribbon
edited Dec 3, 2015 12:35AM in Python

Hi All,

  I am keep getting ORA-00922 error while running the below unlock_users python script. Not sure what i have missed here. Pls suggest. Thanks !!

def RunSqlplus(sqlcmd,dbsid=None,dba=None,user=None, fetchres=True):
    if 'dba' in dba:
        conn_string = '/'
    elif 'local' in dba:
        conn_string = "%s/%[email protected]%s" % ('system', check_output('/oracle/mgmt/bin/orapwd.pl -u system', shell=True), dbsid)
    else:
        print (strftime("%Y-%m-%d %H:%M:%S") + ' ' + '(' + str(os.getpid()) + ') ' + "ERROR: Unable to Get the ConnectString")
        sys.exit(1)
    try:
        db = cx_Oracle.connect(conn_string, mode = cx_Oracle.SYSDBA)
        cur = db.cursor()
        cur.execute(sqlcmd)
        if fetchres:
            return cur.fetchall()
        else:
            return cur.fetchone()
    except cx_Oracle.DatabaseError, ex:
        error, = ex.args
        print (strftime("%Y-%m-%d %H:%M:%S") + ' ' + '(' + str(os.getpid()) + ') ' + "ERROR: Error occurred in the database %s. ORA_ERROR_CODE" %(dbsid))
, error.code
        return False
    return True


if __name__ == '__main__':
    if dbutl.Get_RunInst():
        dbsid = dbutl.Get_RunInst()
    else:
        print (strftime("%Y-%m-%d %H:%M:%S") + ' ' + '(' + str(os.getpid()) + ') ' + "ERROR: Error getting Running Oracle Instance")
        sys.exit(1)
    sqlcmd="""
    set echo off verify off feedback off heading off pages 0 linesize 200 trimspool on timing off
    begin
         for rec in (select username, account_status from dba_users where username like '%DBLINK' or username in ('DW2_EXTRACT','INSPECTOR') or username
in ('SNOWWHITE','HAPPY','GRUMPY','SNEEZY','SLEEPY','DOPEY','DOC','BASHFUL'))
         loop
            execute immediate 'alter user ' || rec.username || ' account lock';
     end loop;
    end;
    """
    print sqlcmd
    if RunSqlplus(sqlcmd,dbsid,'dba',fetchres=True):
        res = RunSqlplus(sqlcmd,dbsid,'dba',fetchres=True)
        print res
    else:
       print "Error"

$ ./new.py

2015-11-20 04:30:18 (1356) ERROR: Error occurred in the database ETFDB20-1. ORA_ERROR_CODE 922

Error

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,643 Employee
    edited Dec 3, 2015 12:35AM

    cur.execute() will execute one SQL or PL/SQL call by sending it to the DB for execution.


    Your code appears to be

    1) passing in two statements. You need to pass each statement separately.  The BEGIN/END block counts as one statement.

    2) passing a SQL*Plus SET command.  This is neither a SQL or PL/SQL statement so the DB won't understand it.

This discussion has been closed.