Oracle为什么在存在称为时间戳记的字段时创建触发器失败? [英] Oracle why does creating trigger fail when there is a field called timestamp?

问题描述

我刚刚浪费了过去的两个小时，试图根据

I've just wasted the past two hours of my life trying to create a table with an auto incrementing primary key bases on this tutorial, The tutorial is great the issue I've been encountering is that the Create Target fails if I have a column which is a timestamp and a table that is called timestamp in the same table...

为什么在创建表时oracle不会将其标记为问题?

Why doesn't oracle flag this as being an issue when I create the table?

这是我输入的命令序列:

Here is the Sequence of commands I enter:

1. 创建表:

1. Creating the Table:

CREATE TABLE myTable
   (id NUMBER PRIMARY KEY,
    field1 TIMESTAMP(6),
    timeStamp NUMBER,
);

创建序列:

Creating the Sequence:

CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

创建触发器:

Creating the trigger:

CREATE OR REPLACE TRIGGER test_trigger  
BEFORE INSERT  
ON myTable  
REFERENCING NEW AS NEW  
FOR EACH ROW  
BEGIN  
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;  
END;  
/

这是我收到的错误消息:

Here is the error message I get:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

任何没有两行带有单词"timestamp"的组合都可以正常工作.我本以为语法足以区分关键字和列名.

Any combination that does not have the two lines with a the word "timestamp" in them works fine. I would have thought the syntax would be enough to differentiate between the keyword and a column name.

正如我所说的，我不明白为什么表创建得很好，但是当我尝试创建触发器时，oracle崩溃了.

As I've said I don't understand why the table is created fine but oracle falls over when I try to create the trigger...

澄清

我知道问题是存在一个称为timestamp的列，该列可能是关键字也可能不是关键字.我的问题是为什么当我尝试创建触发器时而不是在创建表时就把它驳倒了，所以我至少应该得到一个警告.

I know that the issue is that there is a column called timestamp which may or may not be a keyword. MY issue is why it barfed when I tried to create a trigger and not when I created the table, I would have at least expected a warning.

这表示使用了Oracle几个小时后，它的错误报告似乎不那么冗长，也许只是因为我使用的是Express版本.

That said having used Oracle for a few hours, it seems a lot less verbose in it's error reporting, Maybe just because I'm using the express version though.

如果这是Oracle中的错误，那么没有支持合同的人将如何报告该错误?我只是在试用Express版本，因为我必须将一些代码从MySQL迁移到Oracle.

If this is a bug in Oracle how would one who doesn't have a support contract go about reporting it? I'm just playing around with the express version because I have to migrate some code from MySQL to Oracle.

推荐答案

以下是有关metalink(227615.1)摘录的注释:

There is a note on metalink about this (227615.1) extract below:

# symptom: Creating Trigger fails
# symptom: Compiling a procedure fails
# symptom: ORA-06552: PL/SQL: %s
# symptom: ORA-06553: PLS-%s: %s     
# symptom: PLS-320: the declaration of the type of this expression is incomplete or malformed
    # cause: One of the tables being references was created with a column name that is one of the datatypes (reserved key word). Even though the field is not referenced in the PL/SQL SQL statements, this error will still be produced.

    fix:

    Workaround:

    1. Rename the column to a non-reserved word.
    2. Create a view and alias the column to a different name.

这篇关于Oracle为什么在存在称为时间戳记的字段时创建触发器失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！