Thursday, September 21, 2006

如何得到控制台程序的输出

所有的程序都有标准输入,标准输出,标准错误这三个管道存在, 所以我们想得到控制台程序的输出,可以通过重定向这些管道来实现.

首先, 我们要建立一个管道作为控制台程序的标准输出管道, 因为这个管道要转给控制台程序使用, 所以它应该是可以被控制台程序继承的句柄.
ZeroMemory(@mSa, SizeOf(mSa));
mSa.nLength := SizeOf(mSa);
mSa.bInheritHandle := True; //设定为可以为子进程继承的句柄
mSa.lpSecurityDescriptor := nil;

if not CreatePipe(mOutRd, mOutWr, @mSa, 0) then raise Exception.Create('建立管道出错');

第二步, 复制读管道的句柄, 因为读管道的句柄在建立时是一个可继承句柄, 它可能会被子进程使用, 那样会造成信息的丢失与父进程的堵塞, 所以我们要复制成一个不可以继承句柄, 并且它实际上在子进程中是没有用的, 所以我们在复制成功后要关闭它.

if not DuplicateHandle(GetCurrentProcess, mOutRd, GetCurrentProcess,
@mOutRdDup, 0, False, DUPLICATE_SAME_ACCESS) then
raise Exception.Create('复制读句柄出错');

CloseHandle(mOutRd);

第三步, 我们要建立控制台子进程了
ZeroMemory(@mStartupInfo, SizeOf(mStartupInfo));
mStartupInfo.cb := SizeOf(mStartupInfo);
mStartupInfo.dwFlags := mStartupInfo.dwFlags or STARTF_USESTDHANDLES; //将标准输出重定向
mStartupInfo.hStdOutput := mOutWr;
mStartupInfo.hStdError := mOutWr;
ZeroMemory(@mProcess, SizeOf(mProcess));

if not CreateProcess(nil, cmdLine,nil, nil, True, 0, nil, WorkDir, mStartupInfo,mProcess) then
raise Exception.Create('启动程序出错');

CloseHandle(mProcess.hProcess);
CloseHandle(mProcess.hThread);

第四步, 关闭写句柄, 要不然当子进程关闭了自己后, 会因为写句柄还被父进程所打开, 而使下面的ReadFile无法得知管道已结束, 从而堵塞.

if not CloseHandle(mOutWr) then
raise Exception.Create('标准输出句柄关闭出错');

while ReadFile(mOutRdDup, mBuff, 256, mRealRead, nil) do begin
if mRealRead = 0 then Break;
//作点自己爱作的事情吧
end;

完整的代码如下:
var
mRealRead: DWORD;
mOutRd, mOutWr, mOutRdDup: THandle;
mSa: TSecurityAttributes;
mProcess: TProcessInformation;
mStartupInfo: TStartupInfo;
mBuff: array[0..255] of Char;
begin
ZeroMemory(@mSa, SizeOf(mSa));
mSa.nLength := SizeOf(mSa);
mSa.bInheritHandle := True;
mSa.lpSecurityDescriptor := nil;

if not CreatePipe(mOutRd, mOutWr, @mSa, 0) then raise Exception.Create('建立管道出错');

if not DuplicateHandle(GetCurrentProcess, mOutRd, GetCurrentProcess,
@mOutRdDup, 0, False, DUPLICATE_SAME_ACCESS) then
raise Exception.Create('复制读句柄出错');

CloseHandle(mOutRd);
ZeroMemory(@mStartupInfo, SizeOf(mStartupInfo));
mStartupInfo.cb := SizeOf(mStartupInfo);
mStartupInfo.dwFlags := mStartupInfo.dwFlags or STARTF_USESTDHANDLES;
mStartupInfo.hStdOutput := mOutWr;
mStartupInfo.hStdError := mOutWr;
mStartupInfo.hStdInput := GetStdHandle(STD_INPUT_HANDLE);
ZeroMemory(@mProcess, SizeOf(mProcess));


if not CreateProcess(nil, cmdLine,nil, nil, True, 0, nil, WorkDir, mStartupInfo,mProcess) then raise Exception.Create('启动程序出错');

CloseHandle(mProcess.hProcess);
CloseHandle(mProcess.hThread);

if not CloseHandle(mOutWr) then
raise Exception.Create('标准输出句柄关闭出错');

while ReadFile(mOutRdDup, mBuff, 256, mRealRead, nil) do begin
if mRealRead = 0 then Break;
mmo1.Lines.Add(mBuff);
Application.ProcessMessages;
end;

Wednesday, September 20, 2006

如何修改文件版本号

修改文件版本号, 其实就是修改文件的资源. MSDN上没有明说如何修改文件的版本号的方法, 因为MS认为版本号应该是属于文件创建者的, 而不是如我们这样的文件所有者应该去修改的. 但资源总是资源, MS提供了如何修改资源的函数, 当然这些函数也可以修改版本号.

首先, 我们要找到文件的版本信息资源:
mSize := GetFileVersionInfoSize('FileName.exe');
SetLength(mVersion, mSize);
GetFileVersionInfo('FileName.exe', mRes, mSize, mVersion);
这个代码能让我们得到文件的版本信息资源, 得到的结果就放在mVersion中, 其实这个就是一串的二进制形的资源内存块.

第二步, 我们要找到文件的版本信息:
VerQueryValue(mVersion, '\', mFixV, mLen);
mFixVersion1 := PVSFixedFileInfo(mFixV);
这一步能让我们得到mFixVersionInfo, 它是mVersion这个资源内存块中指向文件的版本信息的那一部分的指针.

第三步, 修改文件的版本信息:
mFixVersion^.dwFileVersionMS := $10001; //高位低位都为1
mFixVersion^.dwFileVersionLS := 26 shl 16 + 26; //高位低位都为26
这两步演示了如何修改文件的版本号, dwFileVersionMS的高位是Major Version, 低位是Minor Version, dwFileVersionLS的高位是Release Version, 低位是Build Version.

第四步, 保存修改的内容, 下面就可以参考MSDN上的UpdateResource部分的例子了, 因为都是资源嘛:
mHExe := BeginUpdateResource('FileName.Exe', False);
if VerQueryValue(mVersion, '\VarFileInfo\Translation', mTranslate, mLen) then begin
//这步是因为基本都是单区域资源的, 如果是多区域的, 可以参考MSDN中的作法,
//将mTranslate/sizeof(LANGANDCODEPAGE)来得到第一个
mLang := PLANGANDCODEPAGE(mTranslate);
if UpdateResource(mHExe, RT_VERSION, MakeIntResource(1), mLang^.wLanguage, mVersion, mSize) then
EndUpdateResource(mHExe, False);
FreeLibrary(mHExe);

这样, 我们就修改了文件的版本号了, 可以类推出如何修改版本资源中的其它资源.

完整的代码如下
type
PLANGANDCODEPAGE = ^LANGANDCODEPAGE;
LANGANDCODEPAGE = record
wLanguage: Word;
wCodePage: Word;
end;

var
mSize: Integer;
mLen, mLen1: UINT;
mTranslate, mFixV: Pointer;
mHExe, mRes: THandle;
mVersion: array of Byte;
mFixVersion: VS_FIXEDFILEINFO;
mFixVersion1: PVSFixedFileInfo;
mLang: PLANGANDCODEPAGE;
begin
mSize := GetFileVersionInfoSize('FileName.exe', mRes);
SetLength(mVersion, mSize);
GetFileVersionInfo('FileName.exe', mRes, mSize, mVersion);
VerQueryValue(mVersion, '\', mFixV, mLen);
mFixVersion1 := PVSFixedFileInfo(mFixV);
mFixVersion1^.dwFileVersionMS := $10001;
mFixVersion1^.dwFileVersionLS := 26 shl 16 + 26;
mHExe := BeginUpdateResource('FileName.exe', False);
if VerQueryValue(mVersion, '\VarFileInfo\Translation', mTranslate, mLen) then begin
mLang := PLANGANDCODEPAGE(mTranslate);
if UpdateResource(mHExe, RT_VERSION, MakeIntResource(1), mLang^.wLanguage, mVersion, mSize) then
EndUpdateResource(mHExe, False);
end
FreeLibrary(mHExe);

Wednesday, September 13, 2006

有关instr函数的问题

关于pl/sql中的instr函数, 当要查找的字符串或被查找的字符串为NULL时, 返回的值也是NULL.

关于oracle9以上自定义聚集函数的例子

以下是自定义聚集函数的代码例子, 抄自http://yangtingkun.itpub.net/post/468/3380

SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (
2 STR VARCHAR2(30000),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBE
R) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
7 )
8 /

SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := T_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
9 BEGIN
10 SELF.STR := SELF.STR || VALUE;
11 RETURN ODCICONST.SUCCESS;
12 END;
13
14 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBE
R) RETURN NUMBER IS
15 BEGIN
16 RETURNVALUE := SELF.STR;
17 RETURN ODCICONST.SUCCESS;
18 END;
19
20 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /

SQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2
2 AGGREGATE USING T_LINK;
3 /

Monday, September 11, 2006

oracle 8i 关键字列表

Oracle8 SQL Reference
Release 8.0

A58225-01


Oracle Reserved Words and Keywords

This appendix lists Oracle reserved words and keywords.

lists Oracle reserved words. Words followed by an asterisk (*) are also ANSI reserved words.


Note:

In addition to the following reserved words, Oracle uses system-generated names beginning with "SYS_" for implicitly generated schema objects and subobjects. Oracle discourages you from using this prefix in the names you explicitly provide to your schema objects and subobjects to avoid possible conflict in name resolution.



Table C-1 Reserved Words

ACCESS

AUDIT

COMPRESS

DESC*

ADD*

BETWEEN*

CONNECT*

DISTINCT*

ALL*

BY*

CREATE*

DROP*

ALTER*

CHAR*

CURRENT*

ELSE*

AND*

CHECK*

DATE*

EXCLUSIVE

ANY*

CLUSTER

DECIMAL*

EXISTS

AS*

COLUMN

DEFAULT*

FILE

ASC*

COMMENT

DELETE*

FLOAT*

FOR*

LONG

PCTFREE

SUCCESSFUL

FROM*

MAXEXTENTS

PRIOR*

SYNONYM

GRANT*

MINUS

PRIVILEGES*

SYSDATE

GROUP*

MODE

PUBLIC*

TABLE*

HAVING*

MODIFY

RAW

THEN*

IDENTIFIED

NETWORK

RENAME

TO*

IMMEDIATE*

NOAUDIT

RESOURCE

TRIGGER

IN*

NOCOMPRESS

REVOKE*

UID

INCREMENT

NOT*

ROW

UNION*

INDEX

NOWAIT

ROWID

UNIQUE*

INITIAL

NULL*

ROWNUM

UPDATE*

INSERT*

NUMBER

ROWS*

USER*

INTEGER*

OF*

SELECT*

VALIDATE

INTERSECT*

OFFLINE

SESSION*

VALUES*

INTO*

ON*

SET*

VARCHAR*

IS*

ONLINE

SHARE

VARCHAR2

LEVEL*

OPTION*

SIZE*

VIEW*

LIKE*

OR*

SMALLINT*

WHENEVER*

LOCK

ORDER*

START

WHERE

WITH*


lists Oracle keywords. Keywords marked with asterisks (*) are also ANSI reserved words. For maximum portability to other implementations of SQL, do not use these words as schema object names.

ACCOUNT

CACHE_INSTANCES

CONSTRAINT*

DEGREE

ACTIVATE

CANCEL

CONSTRAINTS*

DEREF

ADMIN

CASCADE*

CONTENTS

DIRECTORY

AFTER

CAST

CONTINUE*

DISABLE

ALLOCATE*

CFILE

CONTROLFILE

DISCONNECT

ALL_ROWS

CHAINED

CONVERT*

DISMOUNT

ANALYZE

CHANGE

COST

DISTRIBUTED

ARCHIVE

CHARACTER*

COUNT*

DML

ARCHIVELOG

CHAR_CS

CPU_PER_CALL

DOUBLE*

ARRAY

CHECKPOINT

CPU_PER_SESSION

DUMP

AT*

CHOOSE

CURRENT_SCHEMA

AUTHENTICATED

CHUNK

CURRENT_USER*

EACH

AUTHORIZATION

CLEAR

CURSOR*

ENABLE

AUTOEXTEND

CLOB

CYCLE

END*

AUTOMATIC

CLONE

ENFORCE

CLOSE*

DANGLING

ENTRY

BACKUP

CLOSED_CACHED_OPEN_

CURSORS

DATABASE

ESCAPE*

BECOME

COALESCE*

DATAFILE

ESTIMATE

BEFORE

COLUMNS

DATAFILES

EVENTS

BEGIN*

COMMIT*

DATAOBJNO

EXEMPT*

BFILE

COMMITTED

DBA

EXCEPTIONS

BITMAP

COMPATIBILITY

DEALLOCATE*

EXCHANGE

BLOB

COMPILE

DEBUG

EXCLUDING

BLOCK

COMPLETE

DEC*

EXECUTE*

BODY

COMPOSITE_LIMIT

DECLARE*

EXPIRE

COMPUTE

DEFERRABLE

EXPLAIN

CACHE

CONNECT_TIME

DEFERRED

EXTENT

EXTENTS

INDEXES

MANAGE

NLS_CALENDAR

EXTERNALLY

INDICATOR*

MASTER

NLS_CHARACTERSET

IND_PARTITION

MAX*

NLS_ISO_CURRENCY

FAILED_LOGIN_ATTEMPTS

INITIALLY

MAXARCHLOGS

NLS_LANGUAGE

FALSE

INITRANS

MAXDATAFILES

NLS_NUMERIC_

CHARACTERS

FAST

INSTANCE

MAXINSTANCES

NLS_SORT

FIRST_ROWS

INSTANCES

MAXLOGFILES

NOS_SPECIAL_CHARS

FLAGGER

INSTEAD

MAXLOGHISTORY

NLS_TERRITORY

FLUSH

INT*

MAXLOGMEMBERS

NOARCHIVELOG

FORCE

INTERMEDIATE

MAXSIZE

NOCACHE

FOREIGN*

ISOLATION*

MAXTRANS

NOCYCLE

FREELIST

ISOLATION_LEVEL

MAXVALUE

NOFORCE

FREELISTS

MEMBER

NOLOGGING

FULL

KEEP

MIN*

NOMAXVALUE

FUNCTION

KEY*

MINEXTENTS

NOMINVALUE

KILL

MINIMUM

NONE

GLOBAL*

MINVALUE

NOORDER

GLOBALLY

LAYER

MOUNT

NOOVERIDE

GLOBAL_NAME

LESS

MOVE

NOPARALLEL

GROUPS

LIBRARY

MTS_DISPATCHERS

NORESETLOGS

HASH

LIMIT

MULTISET

NOREVERSE

HASHKEYS

LINK

NORMAL

HEADER

LIST

NATIONAL*

NOSORT

INSTANCE

LOB

NCHAR*

NOTHING

HEAP

LOCAL*

NCHAR_CS

NUMERIC*

LOG

NCLOB

NVARCHAR2

IDLE_TIME

LOGFILE

NEEDED

OBJECT

IF

LOGGING

NESTED

OBJNO

INCLUDING

LOGICAL_READS_PER_CALL

NEW

OBJNO_REUSE

INDEXED

LOGICAL_READS_PER_

SESSION

NEXT*

OFF

OID

PLSQL_DEBUG

RESIZE

SHARED_POOL

OIDINDEX

POST_TRANSACTION

RESTRICTED

SHRINK

OLD

PRECISION*

RETURN

SKIM_UNUSABLE_INDEXES

ONLY*

PRESERVE*

RETURNING

SNAPSHOT

OPCODE

PRIMARY*

REUSE

SOME*

OPEN*

PRIVATE

REVERSE

SORT

OPTIMAL

PRIVATE_SGA

ROLE

SPECIFICATION

OPTIMIZER_GOAL

PRIVILEGE

ROLES

SPLIT

ORGANIZATION

PROCEDURE*

ROLLBACK*

SQLCODE*

OVERFLOW

PROFILE

ROWLABEL

SQLERROR*

OWN

PURGE

RULE

SQL_TRACE

STANDBY

PACKAGE

QUEUE

SAMPLE

STATEMENT_ID

PARALLEL

QUOTA

SAVEPOINT

STATISTICS

PARTITION

SCAN_INSTANCES

STOP

PASSWORD

RANGE

SCHEMA*

STORAGE

PASSWORD_GRACE_TIME

RBA

SCN

STORE

PASSWORD_LIFE_TIME

READ*

SCOPE

STRUCTURE

PASSWORD_LOCK_TIME

REAL*

SD_ALL

SUM*

PASSWORD_REUSE_MAX

REBUILD

SD_INHIBIT

SWITCH

PASSWORD_REUSE_TIME

RECOVER

SD_SHOW

SYSDBA

PASSWORD_VERIFY_

FUNCTION

RECOVERABLE

SEGMENT

SYSOPER

PCTINCREASE

RECOVERY

SEG_BLOCK

SYSTEM

PCTTHRESHOLD

REF

SEG_FILE

PCTUSED

REFERENCES*

SEQUENCE

TABLES

PCTVERSION

REFERENCING

SERIALIZABLE

TABLESPACE

PERCENT

REFRESH

SESSIONS_PER_USER

TABLESPACE_NO

PERMANENT

REPLACE

SESSION_CACHED_

CURSORS

TABNO

PLAN

RESET

SHARED

TEMPORARY*

RESETLOGS

THAN

TRUE

UNLOCK

VALIDATION

THE

TRUNCATE

UNRECOVERABLE

VALUE

THREAD

TX

UNTIL*

VARRAY

TIME

TYPE

UNUSABLE

VARYING*

TIMESTAMP

UNUSED

WHEN

TOPLEVEL

UBA

UPDATABLE

WITHOUT

TRACE

UNARCHIVED

USAGE*

WORK*

TRACING

UNDER

USE

WRITE*

TRANSACTION*

UNDO

USING*

TRANSITIONAL

UNLIMITED

XID

TRIGGERS



oracle 8i上的优化参数

/*+ FULL(tablename) */: 明确的指定进行全表扫描

/*+ ROWID(tablename) */: 明确的选择根据rowid进行表扫描

/*+ CLUSTER(tablename) */: 明确的指定对指定表进行群集扫描, 只能应用于tablename的条件中有群集对象时

/*+ HASH(tablename) */: 明确的选择对指定表进行hash扫描, 只能应用于tablename的条件中有群集对象时

/*+ HASH_AJ */: 转化一个not in子查询为一个anti-join的hash扫描

/*+ HASH_SJ */: 转化一个EXISTS子查询为一个semi-join的hash扫描

/*+ INDEX(TABLENAME INDEXNAME) */: 明确的指定使用某个表的某个索引, 指定了这个优化参数, 优化器对这个表将不考虑进行全表扫描或用这个表的其它索引. 如果对表指定了多个索引, 那么优化器将会考查每一个索引的成本, 选择成本最小, 或者选择多个索引联合使用, 然后将结果再组合起来, 当这都建立在, 优化器认为这样作是成本最低的情况下. 如果没有指定索引, 优化器会考查表上每一个可以使用的索引.

/*+ INDEX_ASC(tablename indexname) */: 这个优化参数在缺省时与 INDEX 优化参数是一样的, 除非你的数据库中的索引扫描的缺省行为被改变了. 缺省行为是对索引进行正序扫描

/*+ INDEX_COMBINE(tablename indexname) */ : 如果不指定索引, 那么优化器将使用bitmap索引进行布尔组合, 而不管是不是成本最低的. 如果没有bitmap索引的话, 也会强制的转化一个索引为bitmap索引来进行布尔组合. 如果指定了索引,那也与上叙一样, 但会使用指定的索引.

/*+ INDEX_DESC(tablename indexname) */: 与 INDEX 优化参数一样, 不过将会以逆序进行索引扫描.

/*+ INDEX_FFS(tablename indexname) */: 会用索引快速扫描代替全表扫描.

/*+ MERGE_AJ */: 转化一个not in子查询为一个 merge anti-join 查询

/*+ MERGE_SJ */: 转化一个exists子查询为一个merge semi-join 查询

/*+ AND_EQUAL(tablename indexname indexname.....) */: 明确指定建立一种执行计划, 这个计划会把根据指定索引得到列进行组合. 这个优化参数必须指定至少二个索引, 但不能多于五个

/*+ USE_CONCAT */: 将where语句中的or运行转化为union all形的操作, 如果这个操作的成本比没有这个操作的成本低的话

/*+ ORDERED */: 指定oracle根据from后面表的顺序进行join操作

/*+ STAR */: 指定实行star plan, star plan将最大的表放在join操作的最后, 并进行nested loops join. 这个优化参数所用的语句,必须至少有三个表

/*+ USE_NL(TABLENAME) */: 在很多情况下 nested loops join比sort-merge join要快的返回第一行记录, 造成更快的反应速度,这个参数就是指定表要进行 nested loops join, 而不是sort-merge join

/*+ USE_MERGE(TABLENAME) */: 指定表要进行sort-merge join

/*+ USE_HASH(TABLENAME) */: 指定表进行hash join

/*+ DRIVING_SITE(TABLENAME) */: 指定表是在其它服务器上的

Sunday, September 10, 2006

有关成本统计数据新旧对执行计划的影响

有时候执行计划一直不是你想要的, 但oracle又一直用这样的执行计划, 那么可能是因为oracle中对表的成本统计数据太旧了. 这时, 可以运行 analyze table 表名 compute statistics 来对表的成本统计数据进行刷新.

什么是buffer(sort)

BUFFER (SORT)

Description

Performs a memory sort on a row source

Versions

This operation was introduced in Oracle 9.0.1 though it may have been used in limited circumstances in some earlier versions

This operation is implemented in the following versions


9.0.1
9.2.0

Examples

Example 1

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table and index definitions

    CREATE TABLE t1 (c1 NUMBER);
CREATE INDEX T1_C1 ON T1(C1);

At least one of the tables must be analysed

    ANALYZE TABLE t1 COMPUTE STATISTICS;

The statement

    SELECT t1.c01, t2.c01
FROM t1;

generates the following execution plan

   0     SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN (CARTESIAN)
2 1 TABLE ACCESS (FULL) OF 'T1'
3 1 BUFFER (SORT)
4 3 TABLE ACCESS (FULL) OF 'T2'
Example 2

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table and index definitions

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);

In Oracle 9.0.1 and above the statement

    SELECT c1,c2 FROM t1
CONNECT BY c1 = PRIOR c2
START WITH c1 = 0;

generates the following execution plan

   0      SELECT STATEMENT Optimizer=CHOOSE
1 0 CONNECT BY (WITH FILTERING)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'T1'
4 2 TABLE ACCESS (BY USER ROWID) OF 'T1'
5 1 NESTED LOOPS
6 5 BUFFER (SORT)
7 6 CONNECT BY PUMP
8 5 TABLE ACCESS (FULL) OF 'T1'

Thursday, September 07, 2006

有关 ORA-01779无法修改与非键值保存表对应的列 的解释

当对视图进行update时, 会出现ora-01779错误, 这个错误是因为要修改的字段在视图中不能保证物理上唯一,多出现在与多表关联时, 一个表因与另一个表的关联, 原表中的字段产生了多条记录的原故. 具体可以见 http://forums.oracle.com/forums/message.jspa?messageID=1266445
下面是其中一个回帖:
For a table to be key preserved in a view, the primary key of that table, if it were present in the view, ciould serve as a primary key of the view.

In your case, m.id cannot be a PK of the view because there could be more than one row in c pointing to the same row in m. Table c is key preserved (thus updateable) in the inner join version because each row of c can only appear once in the view.