CODEWITHSUNDEEP
pythoncx-oracle
user1050619
user1050619

Reputation: 20896

get sequence number in cx_oracle python

Im trying to execute a insert statement that has a sequence number column but unable to determine the returning value.

When I execute the below statement without binding seqvalue I get a error. When I initialize the seqvalue to 0 and bind it the output is 0.

code

    col2 = 'test'
    qry=insert into table1 (col1,col2) value (seq1.nextval,:col2) returning col1 into :seqvalue

   cur.excute(qry,:col2)

Please provide your inputs.

Upvotes: 2

Views: 4226

Answers (2)

Dhruv Das
Dhruv Das

Reputation: 1

  1. Create a sequence in database: Create sequence my_seq minvalue 1 maxvalue 99999999999999 increment by 1 start with 1 nocache noorder nocycle;

  2. Create a Oracle function to generate sequence numbers in database: Create or replace function gen_seq Return number Is Seq_value number; Begin

      Select my_seq.nextval into seq_value from dual;
  Return seq_value;

    End;

  3. self.emp_id = cur.callfunc('gen_seq', int)

  4. Use self.emp_id as bind variable in order to insert into your database.

Any doubt. Please feel free to ask.

Upvotes: 0

mohanjot
mohanjot

Reputation: 1510

You can try using the bind variable and prepare statements which would use the same oracle method as SEQ.nextval. Steps :

  1. Make a DB connection using cx_Oracle.

  2. Obtain a cursor from DB connection.

  3. Declare variables : which will be pushed into Oracle insert statement. for example below:

var_CHANGES = 'Test Changes' var_COMMENTS = 'Test'

  1. Make a statement with next line characters as show in example below:

statement = "INSERT"                    + "\n" + \
       "INTO   CHANGE_LOG"    + "\n" + \
       "( CHANGE_LOG_ID"      + "\n" + \
       ", CHANGE_TYPE"             + "\n" + \
       ", CHANGE_DATE"            + "\n" + \
       ", COMMENTS )"              + "\n" + \
       "VALUES"                    + "\n" + \
       "( CHANGE_LOG_SEQ.nextval" + "\n" + \
       ", :bCHANGE_TYPE"               + "\n" + \
       ", sysdate"              + "\n" + \
       ", :bCOMMENTS )"

then, using regex substitution format the query : 

stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  1. execute the query using cursor.execute. as per the above example:

cursor.execute(statement, bCHANGE_TYPE=var_CHANGES     \
                        , bCOMMENTS = var_COMMENTS )

  1. Execute the commit statement

So, here you can use SQL nextval() and sysdate as in the normal SQL query. This should do the job for you.

Upvotes: 1

Related Questions