Possible Bug - Storing auto-generated values in Oracle

[contracode]

Hello--

I'm trying to do the following:

 * Get current timestamp.
|Query | SELECT SYSDATE AS NOW FROM DUAL |
|NOW?  |
|>>now |

 * Create sequence records.
|Insert     | TAB |
|SEQ_NO?    |PROC_DATE |
|>>seq_no_a |<<now     |
|>>seq_no_b |<<now     |

However, this yields the following exception and stack trace:

java.sql.SQLException: ORA-06550: line 1, column 158:
PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
	at dbfit.fixture.StatementExecution.run(StatementExecution.java:24)
	at dbfit.fixture.DbObjectExecutionFixture.executeStatementAndEvaluateOutputs(DbObjectExecutionFixture.java:184)
	at dbfit.fixture.DbObjectExecutionFixture.runRow(DbObjectExecutionFixture.java:155)
	at dbfit.fixture.DbObjectExecutionFixture.doRows(DbObjectExecutionFixture.java:79)
	at fit.Fixture.doTable(Fixture.java:155)
	at fitlibrary.traverse.AlienTraverseHandler.doTable(AlienTraverseHandler.java:18)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:99)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:87)
	at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:69)
	at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:34)
	at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
	at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
	at fit.Fixture.doTables(Fixture.java:80)
	at fit.FitServer.process(FitServer.java:81)
	at fit.FitServer.run(FitServer.java:56)
	at fit.FitServer.main(FitServer.java:41)

The SEQ_NO field is of type NUMBER(12) and is set as a PK. I've narrowed the issue down to this field by hardcoding a value in it, and in that case, it works.

Is this a bug and, if so, is there a possible workaround?

Thanks!
Jared

[Yavor Nikolov]

Hi Jared,

Thank you for reporting this!

Looks like a bug to me. Needs to be investigated.

Could you try to change the column positions in the Insert command (proc_date first, seq_no second)?

Here is one of the dbfit own tests (build hasn't failed on it so it should work):

https://github.com/dbfit/dbfit/blob/master/FitNesseRoot/DbFit/AcceptanceTests/JavaTests/OracleTests/FlowMode/ParametersAreLoadedAutomaticallyFromVariables/content.txt

Thanks!

Yavor

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at http://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

[jagad...@gmail.com]

Hi Jared,

I also had similar issue. I think trunc(sysdate) instead of just sysdate resolved the issue.

|Query | SELECT trunc(SYSDATE) AS NOW FROM DUAL |
|NOW?  |
|>>now |

 * Create sequence records.
|Insert     | TAB |
|SEQ_NO?    |PROC_DATE |
|>>seq_no_a |<<now     |
|>>seq_no_b |<<now     |

[Jared Contrascere]

Yavor--

The table I'm INSERTing to actually has many more columns. I pruned several for the sake of the example, however, changing the position of the auto-generated value yielded interesting results...

|Query | SELECT SYSDATE AS NOW FROM DUAL |
|NOW?  |
|>>now |

|Insert     | TAB |
|SEQ_NO?    |DUMMY |PROC_DATE |
|>>seq_no_a |A     |<<now     |
|>>seq_no_b |B     |<<now     |

...and, with SEQ_NO transposed one column to the right...

|Insert| TAB |
|DUMMY |SEQ_NO?    |PROC_DATE |
|A     |>>seq_no_a |<<now     |
|B     |>>seq_no_b |<<now     |

both yield:

PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

However, when the auto-generated value is at the rightmost position, the following exception is raised. This is closer to the expected functionality, I'd assume-- but there needs to be a syntax to specify the correct Oracle structure to use for this auto-generated value.

|Insert| TAB |
|DUMMY |PROC_DATE |SEQ_NO?    |
|A     |<<now     |>>seq_no_a |
|B     |<<now     |>>seq_no_b |

yields:

java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("ODS_PROD2"."PONE_PERMIT_T"."PERMIT_SEQ_NO")
ORA-06512: at line 1

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
	at dbfit.fixture.StatementExecution.run(StatementExecution.java:24)
	at dbfit.fixture.DbObjectExecutionFixture.executeStatementAndEvaluateOutputs(DbObjectExecutionFixture.java:184)
	at dbfit.fixture.DbObjectExecutionFixture.runRow(DbObjectExecutionFixture.java:155)
	at dbfit.fixture.DbObjectExecutionFixture.doRows(DbObjectExecutionFixture.java:79)
	at fit.Fixture.doTable(Fixture.java:155)
	at fitlibrary.traverse.AlienTraverseHandler.doTable(AlienTraverseHandler.java:18)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:99)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:87)
	at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:69)
	at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:34)
	at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
	at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
	at fit.Fixture.doTables(Fixture.java:80)
	at fit.FitServer.process(FitServer.java:81)
	at fit.FitServer.run(FitServer.java:56)
	at fit.FitServer.main(FitServer.java:41)

Perhaps a syntax like the following would be conducive to Oracle TDD, where the appropriate sequence object is specified within the fixture:

|Insert     | TAB  | TABSEQ.NEXTVAL |
|SEQ_NO?    |DUMMY |PROC_DATE |
|>>seq_no_a |A     |<<now     |
|>>seq_no_b |B     |<<now     |

Also-- Jagad --I tried that, but it didn't work for this problem. However, I want to thank you for the suggestion!

Thank you all!
Jared

--

You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at http://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

--
Jared N. Contrascere

[Yavor Nikolov]

Hi Jared,

Thanks for your investigations! (Weird behavior that the column should be rightmost - but could be a feasible workaround until we fix that).

The current expectation is that the underlying column is auto-generated (either via IDENTITY column or via trigger). You may see here for example.

Thanks for the suggestion for specifying a sequence object! One thing with sequences is that: generated values are not fully predictable; and they're leaving side effects (sequence is not being reset back on rollback). Both these are not very good for tests, I would avoid them If possible - e.g. by manually hard-coding the values.

Alternatively: you may use the sequence to fill in the variables before the insert. And just use them in the insert.

Best regards,

Yavor