Informix "SERIAL" to Oracle NUMBER/Sequence/Trigger in Pro*C

Question

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

Why don't you create the trigger with a SQL script? I don't see a need to do this from within a C program. — , Dec 16 '11 at 16:33
The existing ESQL program is written in C, and my task is to convert that existing code wholesale to now use Pro*C. Unfortunately, I don't have the authority to change that much about how it works. — confused_person, Dec 19 '11 at 10:31

score 1 · Answer 1 · answered Dec 21 '11 at 15:51

I managed to resolve the issue by using an "EXEC SQL EXECUTE IMMEDIATE" statement.

char sql_buf[4096+1];
snprintf(sql_buf, 4096, <sql>);
EXEC SQL IMMEDIATE :sql_buf;

This bypasses the preprocessor and therefore allows the statement through without complaint.

score 0 · Answer 2 · answered Oct 22 '12 at 15:51

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.

Informix "SERIAL" to Oracle NUMBER/Sequence/Trigger in Pro*C

2 Answers2