2 replies to this topic

[sean.graves]

ProIV 5.5r333 & mfc 518
Windows 2003 Server
Oracle 9i.2.0.3.0

We have a global update function that has 2 characteristics.
They are indipendent from each other ie. ls1 starts at 1 ends at 1

The characteristic 1 has two tables in lookup that both read one record.
On the good read of the last file we have a for loop that loops around an array of 25 elements.
eg FOR #I = 1 to 25

For each array element we are creating an SQL DYNAMIC DELETE statement followed by an SQL DYNAMIC UPDATE.
Both statements are against two different tables.

When #I = 1 for the first DELETE and the UPDATE all is ok.

When #I = 2 for the second DELETE we get a Proiv message box giving a "366 SQL Error".
In the trace file we see the following:
764:=========== OraSqlError: DBerr 3123=>-366: ORA-03123: operation would block

If we put a #OK = COMMIT() after the update the DELETE goes through successfully.

We can get this happen using the above SQL DYNAMIC and using native ProIV updates to do the delete.



We have currently set the following ProIV Environment / Database flags:
SQL_CURSORS=AUTO
SQL_AGGREGATE_KEY=Y

Database Section has
[DATABASE - SQLDEFAULT]
FILETYPE=ORACLE
CONNECTION=USER/PASSWORD@DATABASE
PRODB_CHARSET=8
LOCKED_ROWS_RETURNED=Y

The TABLE which is being deleted has had:
the table ANALYZED for COMPUTE STATISTICS.
the index dropped and re-created.

Does anybody know why Oracle should give the "ORA-03123: operation would block"?

And if so how to go about fixing the problem?

Thanks for any advice.
Sean

[Chris Mackenzie]

Sounds like the first operation is not yet completed (OCI_STILL_EXECUTING),


Is it possible to speed up comms between Pro & Oracle ?

I guess this would be awkward to code using Standard PRO-IV file delete and update
which might get around it and I wouldn't want to introduce a delay in the code so
perhaps BULK bind in a PL/SQL procedure would be worth checking out?

[sean.graves]

Thought I had better update this question.

Under the windows platform when using Oracle you should use the following two settings.

Pro4.ini
[Environment]
SQL_NOSIG=Y

[Database - SQL Default]
LOCKED_ROWS_RETURNED=Y

Whilst we were in our developmetn of just 50 records there was not a problem, but when our client of 50k to 2mil records then without the SQL_NOSIG=Y the system would timeout and then try the next SQL statement, hence the error ORA-03123.

ProIV have confirmed that these two flags should be on when using the Windows platform to stop timeouts.

Cheers for your help
Sean