This content has been marked as final. Show 8 replies
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.
Best would be to log a service request at Oracle support against PRO*C product as there's no dedicated forum for these kind of issues1 person found this helpful
Ok, thanks for your help. I was hoping there would be some kind of nice workaround, but I guess that's not always possible.
Unfortunately not and OMWB is not longer developed as it has been replaced by SQL Developer migration workbench.
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?.