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
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?
Why not defining the variable in the trigger as described in the manual:
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Empno > 0)
sal_diff := :new.sal - :old.sal;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
BTW, what's the error message you're getting?
The error concerning the host variable is: "PCC-S-02206, Host variables are not permitted withing a DDL statement".
I'm assuming that my issue is the preprocessor picking up the ":new." from looking at the documentation for precompiler messages in Pro*C here:
Also, my trigger definition is to mimic a data type that doesn't exist in Oracle. Changing "CREATE" to "CREATE OR REPLACE" changes nothing for me, neither does adding a "BEGIN DECLARE" block, and the rest of the differences between the trigger you posted and the one I posted modify what it does.
Why are you trying to create the trigger in Pro*C application?
Can you convert DDL separately, and then run CREATE TABLE, CREATE SEQUENCE and CREATE TRIGGER using SQL*Plus. Then in Pro*C application can insert/update data as in ESQL. SERIAL emulation will be hidden from the applications.
Will it work for you, or there are any reasons why you need to dynamically create tables?
http://www.sqlines.com - SQL Articles and Free Online SQL Conversion tool
The create statements are in the C code as part of a library which populates an empty database with tables, and performs various other configuration stages to set up a full environment for various applications to work on a customer's system. What the resulting database looks like depends on the customer's own configuration, and as such everything is dynamically generated. It's all fairly deeply embedded into the way the old Informix system works, and changing it would be a massive undertaking, not to mention dangerous because most of the code is about 2 decades old.
It may be possible to change it so that SQL*Plus is used to add the triggers separately, but it's not a preferable solution to me if at all avoidable.
Edit: Perhaps it's possible to just use EXEC SQL EXECUTE IMMEDIATE
Edited by: rb on 21-Dec-2011 03:40
Edited by: rb on 21-Dec-2011 03:44
Seems like OMWB did not have the capability to translate SERIAL columns properly in ESQL/C programs. Your best bet is probably to convert those DDLs into Dynamic SQLs in Pro*C. As you know OMWB cannot be enhanced anymore and we do not have the functionality incorporated in SQLDeveloper. Do you have lots of such ESQL/C programs or is it just a few?.