8 Replies Latest reply: Feb 29, 2012 3:12 PM by Pnauduri-Oracle RSS

    Informix ESQL "SERIAL" to Oracle Pro*C "Number(10)/Sequence/Trigger"

    906163
      Hi,

      I'm currently working on a codebase that had ESQL code written for Informix and am trying to convert it to use Pro*C to talk to Oracle instead. The code was run through the Oracle Migration Workbench, which purports to be able to handle the "SERIAL" data type from Informix. However, when I try and pass through a create table statement with a "SERIAL" column in it, the tool returns no warnings or errors in the popup after it finishes, but when I check the output of the conversion tool it has simply replaced the word "SERIAL" with the string "ERROR(SERIAL)" and does not add any sequences or triggers as this page says it should: http://docs.oracle.com/html/B16022_01/ch2.htm#i1027259

      After I encountered this, I decided to manually code the sequences and triggers, seeing as I had a nice code example to work on. The sequence is straightforward enough, but when I try to implement the trigger:

      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 seems to pick up the "CREATE" keyword, and then sees ":new.JOB_ID" as a host variable and then errors out telling me that I can't do it because host variables cannot be used in create statements.

      A few questions then:

      1) Is there some trick to getting the OMWB tool to behave correctly?
      2) Is there some way to creating a trigger without adding ":new.JOB_ID" as a host variable?
      3) I imagine that the trailing "/" in that code which I gather is required for "create trigger" to work will upset the rest of my C code. How is this supposed to be implemented?

      Thanks