2011年3月

oracle给not null约束指定名称

一般我们建表结构的时候都是使用以下的形式:

create table test(
menuid number(8) default '0'; not null,
name varchar2(40) not null,
id_parent number(10) not null,
url varchar2(300) null,
constraint pk_test primary key(menuid)
);

执行后,查看这个test表的所有约束:

select constraint_name,table_name from user_constraints

发现命名都是SYS_CXXXXXX(XXXXXX表示数字),这些是oracle自动给约束的命名,我感觉为了提高以后数据库的可维护性,还是需要给not null约束指定名称,语法很简单:

create table test(
menuid number(8) default '0' constraint menuid_nn not null,
name varchar2(40) constraint name_nn not null,
id_parent number(10) constraint id_parent_nn not null,
url varchar2(300) null,
constraint pk_test primary key(menuid)
)

参考

oracle没有create or replace table

Oracle数据库和其他数据库(比如MySQL)在新建数据表的时候有一下区别:

SQL> create or replace table testTb;
create or replace table testTb
ORA-00922: 选项缺失或无效

只能使用先drop再create来代替

drop table testTb;
create teble testTb(
    fid   varchar2(4),
    fname   varchar2(10)
);

可以用create or replace的对象有:functions, procedures, packages, types, synonyms, trigger and views,就是没有table,也没有sequence。 drop掉一个并不存在的表报错:

SQL> drop table non_exists;
drop table non_exists
ORA-00942: 表或视图不存在

drop table容错的方法是:

BEGIN
  DROP TABLE non_exists_table;
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode != -0942 THEN RAISE; END IF;
END;
/

错误代码:-0942

    • *drop sequence容错的方法是:
BEGIN
  DROP SEQUENCE non_exists_sequence;
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode != -2289 THEN RAISE; END IF;
END;
/

错误代码:-2289 参考

#167 codeigniter oci8 database driver do not support setting charset and session_mode while ocilogon() to oracle db

system/database/drivers/oci8/oci8_driver.php line 80 db_connect() function did not support set the charset and session mode while connecting to oracle db.

function db_connect()
    {
        return @ocilogon($this->username, $this->password, $this->hostname);
    }
function db_pconnect()
    {
        return @ocilogon($this->username, $this->password, $this->hostname);
    }

here is the prototype of oci_connect( ocilogon improved version )

resource oci_connect ( string $username , string $password [, string $db [, string $charset [, int $session_mode ]]] )

codeigniter最新的2.0.1太蛋疼鸟。system/database/drivers/oci8/oci8_driver.php里面的db_connect()和db_pconnect()函数尽然不支持连接的时候设定字符集,因为函数的原形ocilogon(被oci_connect代替)支持设定charset和session_mode。还有codeigniter为啥要使用@ocilogon而非oci_connect呢?php官方说5.0.0以前的版本才使用ocilogon,codeigniter为了使用ocilogon不报warning还前面加上了"@"符号。但是codeigniter声称是支持php 5.1.6以及以后版本的php框架。有点搞不懂codeigniter的想法了。 没有版本,这个问题,只有通过hard coding修改oci8_driver.php文件的db_connect()和db_pconnect()函数为:

function db_connect()
    {
        return @ociplogon($this->username, $this->password, $this->hostname, $this->char_set);
    }
function db_pconnect()
    {
        return @ociplogon($this->username, $this->password, $this->hostname, $this->char_set);
    }

已经将这个问题提交到

bitbucket

codeigniter配置oracle数据库连接

Connection Parameters

Not all of the parameters in application/config/database.php are used as one might expect.  Namely, $db[‘default’][‘database’] isn’t used at all.  The value used for $db[‘default’][‘hostname’] depends on whether the Oracle client’s tnsnames.ora file exists and contains information about the database to be used.  If the file exists and is configured for the intended database, this parameter should be set to the symbolic name.  Otherwise, it should be set to a single string of the connection parameters that tnsmames.ora would normally contain.

An example of connection parameters for the latter case:

$db['default']['hostname'] = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';
$db['default']['username'] = 'dbuser';
$db['default']['password'] = 'dbpassword';
$db['default']['database'] = ''; // not used by this Oracle driver
$db['default']['dbdriver'] = 'oci8';

In this case, the appropriate values need to be set for the HOST, PORT, and SID keywords in the hostname parameter.