Informix“串行” Pro*C 中的 Oracle 编号/序列/触发器
我正在尝试将一些 Informix ESQL 转换为 Oracle Pro*C。在现有的 Informix 代码中，“SERIAL”数据类型用于指示自动递增的列。根据 Oracle 文档，Informix 的 Oracle Migration Workbench 应该能够处理此问题，并解释说它将“SERIAL”数据类型转换为具有关联 Oracle 序列和触发器的“NUMBER”。但是，当尝试运行该工具时，它只是将“SERIAL”一词替换为“ERROR(SERIAL)”，因此我一直在尝试手动添加触发器/序列。
他们的例子在这里： http://docs.oracle.com/html/B16022_01/ch2 .htm#sthref112 显示了一种可以完成此操作的方法。该序列似乎相当简单，但是当尝试创建如下所示的触发器时：
CREATE TRIGGER clerk.TR_SEQ_11_1
BEFORE INSERT ON clerk.JOBS FOR EACH ROW
BEGIN
SELECT clerk.SEQ_11_1.nextval INTO :new.JOB_ID FROM dual; END;
Pro*C 预处理器在此处选择“CREATE”关键字，并决定不允许我使用主变量“:new”。 JOB_ID”，因为主变量不能与“CREATE”语句结合使用。
我的问题是，是否有某种方法可以创建一个触发器，将 Oracle 序列链接到特定列，而不使用主机变量来指定列名？ Oracle 文档似乎表明他们的迁移工具应该能够应对，这意味着必须有某种方法可以做到这一点。然而，我发现的所有触发器使用的示例都使用主机变量，这会导致预处理器抱怨。
感谢您抽出时间。
（注意：我在上面的示例中使用了 Oracle 文档中示例中的触发器/序列/列名称。）
I'm trying to convert some Informix ESQL to Oracle Pro*C. In the existing Informix code the "SERIAL" data type was used to indicate automatically incrementing columns. According to the Oracle documentation, the Oracle Migration Workbench for Informix should be able to handle this, and it explains that it converts the "SERIAL" data type into a "NUMBER" with an associated Oracle sequence and trigger. However, when trying to run the tool it simply replaces the word "SERIAL" with "ERROR(SERIAL)", so I've been trying to manually add in the trigger/sequence.
Their example here: http://docs.oracle.com/html/B16022_01/ch2.htm#sthref112 shows a way that this can be done. The sequence appears to be fairly straight forward, however when trying to create a trigger like so:
CREATE TRIGGER clerk.TR_SEQ_11_1
BEFORE INSERT ON clerk.JOBS FOR EACH ROW
BEGIN
SELECT clerk.SEQ_11_1.nextval INTO :new.JOB_ID FROM dual; END;
The Pro*C preprocessor picks up the "CREATE" keyword here, and decides that I'm not allowed to use the host variable ":new.JOB_ID", because host variables cannot be used in conjunction with "CREATE" statements.
My question is, is there some way to create a trigger that links an Oracle sequence to a particular column without using a host variable to specify the column name? The Oracle documentation seems to indicate that their migration tool should be able to cope, which means there must be some way of doing this. However all the examples of the trigger use that I have found all use the host variable which causes the preprocessor to complain.
Thank you for your time.
(Note: I've used the trigger/sequence/column names from the example in the Oracle documentation in the example above.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我设法使用“EXEC SQL EXECUTE IMMEDIATE”语句解决了该问题。
这绕过了预处理器，因此允许语句通过而不会有任何抱怨。
I managed to resolve the issue by using an "EXEC SQL EXECUTE IMMEDIATE" statement.
This bypasses the preprocessor and therefore allows the statement through without complaint.
如果不使用“主机变量”指定列名，就不可能创建将 Oracle 序列链接到特定列的触发器。顺便说一下，它不是“主机变量”——只是参考。例如，相同的触发器可能会在更新和插入时触发，因此您必须指定所引用的内容：新变量或旧变量。您可以在 MS-SQL 中执行此操作，但不能在 Oracle 中执行此操作。
It is impossible to create a trigger that links an Oracle sequence to a particular column without using a "host variable" to specify the column name. By the way it isn't "host variable" - just reference. The same trigger may fire on update and insert for example, so you have to specify what you are referencing: new or old variables. You can do it in MS-SQL but not in Oracle.