Moving a block fails with Oracle ORA-00001 unique constraint violation. The problem appears to be related to the handling of a single space as the Oracle empty string value.

Although the exception occurs within reposition_block (lib/blocklib.php), the problem starts when reading the page's blocks in load_blocks. When the subpage is blank, the SQL appears to outer join on block_positions with subpage = ''. As a result, nothing comes back for block_position because (for Oracle), the subpage values in the database are a single space instead of an empty string.

Later, then, in reposition_block, the logic thinks that the block does not have a position and so attempts to insert a block_position record instead of update the existing one. That's when ORA-00001 strikes.

This change appears to fix the defect:

diff --git a/lib/blocklib.php b/lib/blocklib.php
index be0831f..8ace1a7 100644
— a/lib/blocklib.php
+++ b/lib/blocklib.php
@@ -530,9 +530,11 @@ class block_manager {

list($ccselect, $ccjoin) = context_instance_preload_sql('b.id', CONTEXT_BLOCK, 'ctx');

+ $subpage = $this->page->subpage ? $this->page->subpage : $DB->sql_empty();
+
$params = array(

• 'subpage1' => $this->page->subpage,
• 'subpage2' => $this->page->subpage,
  + 'subpage1' => $subpage,
  + 'subpage2' => $subpage,
  'contextid1' => $context->id,
  'contextid2' => $context->id,
  'pagetype' => $this->page->pagetype,

Here is the logged error:
[Fri Dec 03 14:13:40 2010] [error] [client xxx.xxx.xxx.xxx] Default exception handler: Error writing to database Debug: ORA-00001: unique constraint (MOODLE2.M_BLOCPOSI_BLOCONPAGSUB_UIX) violated\nINSERT INTO m_block_positions (region,weight,blockinstanceid,contextid,pagetype,subpage,visible) VALUES (:region,:weight,:blockinstanceid,:contextid,:pagetype,:subpage,:visible) RETURNING id INTO :oracle_id\n[array (\n 'region' => 'side-post',\n 'weight' => -6,\n 'blockinstanceid' => '73',\n 'contextid' => '2',\n 'pagetype' => 'site-index',\n 'subpage' => '',\n 'visible' => '1',\n)]\n* line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown\n* line 268 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()\n* line 1147 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()\n* line 1190 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->insert_record_raw()\n* line 739 of /lib/blocklib.php: call to oci_native_moodle_database->insert_record()\n* line 1381 of /lib/blocklib.php: call to block_manager->reposition_block()\n* line 1028 of /lib/blocklib.php: call to block_manager->process_url_move()\n* line 1178 of /lib/pagelib.php: call to block_manager->process_url_actions()\n* line 702 of /lib/pagelib.php: call to moodle_page->starting_output()\n* line 580 of /lib/outputrenderers.php: call to moodle_page->set_state()\n* line ? of unknownfile: call to core_renderer->header()\n* line 1200 of /lib/setuplib.php: call to call_user_func_array()\n* line 89 of /index.php: call to bootstrap_renderer->__call()\n* line 89 of /index.php: call to bootstrap_renderer->header()\n, referer: https://<my.moodle.domain>/?sesskey=72k5Q817s3&bui_moveid=73

In this case, the exception is on a block being moved out of the way for the block I am actually moving.