Oracle为什么当有一个名为timestamp的字段时创建触发器失败？

发布于 2024-07-08 03:08:52 字数 1294 浏览 12 评论 0原文

我刚刚浪费了生命中过去的两个小时试图创建一个基于本教程，该教程很棒，我遇到的问题是，如果我有一个列，则创建目标会失败是一个时间戳和一个在同一个表中称为时间戳的表...

为什么当我创建表时，oracle 不将此标记为问题？

这是我输入的命令序列：

创建表：

创建表 myTable 
     （id 号主键， 
      字段1时间戳(6), 
      时间戳编号， 
  ）；

创建序列：

创建序列 test_sequence 
  从 1 开始 
  增加 1；

创建触发器：

创建或替换触发器 test_trigger   
  插入前   
  在我的桌子上   
  以新为新   
  对于每一行   
  开始   
  SELECT test_sequence.nextval INTO :NEW.ID FROM Dual;   
  结尾;   
  /

这是我收到的错误消息：

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”一词的两行的组合都可以正常工作。我本以为语法足以区分关键字和列名。

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

澄清

我知道问题是有一个名为时间戳可能是也可能不是关键字。我的问题是为什么当我尝试创建触发器时它会崩溃，而不是当我创建表时，我至少会收到警告。

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

如果这是 Oracle 中的一个错误，没有支持合同的人将如何报告它？我只是在玩快速版本，因为我必须将一些代码从 MySQL 迁移到 Oracle。

原文

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...

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

Here is the Sequence of commands I enter:

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

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.

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...

CLARIFICATION

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.

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.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

非要怀念 2024-07-15 03:08:52

Metalink 上有关于此 (227615.1) 摘录的注释如下：

# 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.

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.

回复收藏 0 原文