Kbase P65778: Records are not created in the Oracle database after using the VALIDATE statement
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/09/2008 |
|
Status: Verified
SYMPTOM(s):
Records are not created in the Oracle database after using the VALIDATE statement
Running the 4GL procedure that creates the record in the Oracle database
Finding the record in the same transaction block from the same session, after the VALIDATE statement, it finds the newly created record.
However, if leaving the transaction session open (with pause) before the end of transaction, sqlplus opened from another session fails to find the newly created record.
After transaction ends from the first session, the sqlplus session finds the record
FACT(s) (Environment):
Oracle DataServer
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
Progress 8.x
CAUSE:
This is expected behavior because validate writes the record but the write is not committed until the transaction is ended. When end transaction is executed, the
record write will be committed.
Turn on DataServer verbose logging, pause a long time then commit, observe "ocom" (oci commit) is sent after the pause.
For example,
11:16:17 OCI retr oexfet <13> rc = 0 (695 us)
11:16:17 <22> orgetnext
11:16:17 <22> ok 0x22
11:16:17 <22> cursor_fetch OC_DIR_FORW
11:16:17 <22> EF 0x22
11:16:17 OCI call omru <22>
11:35:24 OCI call ocom <0>
11:35:34
11:35:34 Cursor <22> oparse: 1 (377 us)
Issued validate at 11:16 but did not commit (end is executed) until 11:35.
FIX:
Find the record in sqlplus after the transaction is ended on the Progress side
or
Issue a RUN STORED-PROCEDURE statement with the send-sql-statement option in order to send a COMMIT command to Oracle. i.e.:
...
VALIDATE <table-name>.
RUN STORED-PROC send-sql-statement ("COMMIT").
...