Home » Database » MDB2_Driver_oci8 » Bug #6573

[2006-01-24 23:15 UTC] aaron at 100-acre dot com

Description:
------------
The following small change in the oci8.php driver should fix this. Sorry for not including a proper diff.

In...
$Id: oci8.php,v 1.103 2005/04/11 15:10:22 danielc Exp $
Insert the following *replacing line 1004*.

//quoted table names should be passed through unchanged (expect for removing the quotes themselves
if (strstr($result, '"')) {
$result = str_replace('"','',$result);
} else {
$result = strtoupper($result);
}

Test script:
---------------
/*
in oracle:
create table "Test" (
testcolumn int not null
)
*/

$db = DB::connect('oci8://user:pwd@tcp(hostspec)');
print_r($db->tableInfo('"Test"'));

Expected result:
----------------
Array
(
[0] => Array
(
[table] => Test
[name] => TESTCOLUMN
[type] => NUMBER
[len] => 22
[flags] => not_null
)

)

Actual result:
--------------
Array ( )

[2006-01-25 19:01 UTC] aaron at 100-acre dot com

As is often the case, the delimited identifiers are there for "historical" reasons (namely that the database in question was ported from MSSQL where mixed case table names are allowed and tons of code uses this convention).

The quotes need to be striped because the user_* views list quoted-identfiers without the quotes (at least in 10g).

Oracle says [1] that quoted indentifers cannot contain a the (") character so str_replace should be safe to remove the quotes at the start and end of the $result.

[1] http://oracleheva1.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm

(Rule 6)

[2006-01-25 19:08 UTC] aaron at 100-acre dot com

Forgot to mention what may or may not be obivous: while user_* views don't include the quotes they do store the rest of the tablename in it's original form and that it is possible (if painful) to have "TestTable", "Testtable" and "TestTABLE" as three different tables in the same schema so we do need to perserve the case when looking up quoted table names.

[2006-02-07 19:13 UTC] aaron at 100-acre dot com

Also related to quotedIdentifiers....

class DB_oci8 should override getSequenceName to allow the quote (") character. Like so perhaps...

function getSequenceName($sqn)
{
return sprintf($this->getOption('seqname_format'),
preg_replace('/[^a-z0-9_".]/i', '_', $sqn));
}

[2006-03-23 18:12 UTC] aaron at 100-acre dot com

The problem is that it is impossible to access tableInfo on mixed case named tables currently. If you can find a better way to support that then I'm all ears.