Posts:
14
Registered:
11/29/11
|
|
|
|
Creating triggers in post-deploymen script
Posted:
Dec 6, 2011 10:13 AM
|
|
|
|
|
I'm trying to create a trigger in the post-deployment script. I can get the deployment to succeed but the trigger will not compile. Other triggers generated direct from the dbschema file are ok.
I've reviewed the deployment script that's generated and can see that the the file format is mixed, i.e. code for my trigger that won't compile has windows (CR LF) line endings, but the part of the script generated direct from the dbschema file and works ok has only line feeds.
Anyone any thoughts?
btw tried using Unix (LF) line endings without success.
|
|
|
Posts:
263
Registered:
8/22/07
|
|
|
|
Re: Creating triggers in post-deploymen script
Posted:
Dec 6, 2011 5:06 PM
in response to: steve_n
|
|
|
Steve,
I don't believe the CR-LFs are going to be an issue. After looking at your post deployment script I think it might have to do with rights. Does the user that you connect with for the deployment have rights on the "RESC"."TBLGEN_MULTI_LOC_DATA" table? How about TC2CM2 schema? Does it have rights to EXECUTE?
If that's not it, can you send me the exact error message that you get?
Also, I see that you are creating foreign keys in the post-deployment script. Have you considered defining the foreign keys in the "TC2CM2"."SSCUTOFFDATESBYCOUNTRY" and "TC2CM2"."TBLDEMOGRAPHICS" tables, themselves. You'll get automatic script generation if you do.
|
Dwight
|
|
Posts:
14
Registered:
11/29/11
|
|
|
|
Re: Creating triggers in post-deploymen script
Posted:
Dec 8, 2011 6:29 AM
in response to: Dwight
|
|
|
|
|
Dwight,
The user has the rights to execute the script, so I do not think that is the cause.
The exact error message when the trigger fires is:
SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 7 17:56:18 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> delete from resc.tblgen_multi_loc_data;
delete from resc.tblgen_multi_loc_data
*
ERROR at line 1:
ORA-04098: trigger 'TC2CM2.TD_TBLGEN_MULTI_LOC_DATA' is invalid and failed
re-validation
SQL>
I have run 3 futher tests, including 1 test where I removed the line breaks from the trigger content in the post-depolyment script and then the trigger created in the db was valid - attached are build outputs and scripts from all 3 tests.
|
|
|
Posts:
263
Registered:
8/22/07
|
|
|
|
Re: Creating triggers in post-deploymen script
Posted:
Dec 12, 2011 12:53 PM
in response to: steve_n
|
|
|
Steve,
You are correct about the mixed CRLFs and LFs. I found a reference to the problem (http://www.intertech.com/Blog/post/Executing-SQL-Scripts-with-OracleODP.aspx) that you described through Google. It is an undocumented Oracle behavior/requirement that DDL not use mixed line-endings. Through-out the development of TEx, we have strived keep the line-endings that the user enters; especially in PL/SQL code blocks. Because we compare code blocs as a single unit, arbitrarily changing them to CRLF or LF would cause all procs, funcs, packages, and triggers to be redeployed. We have tried to avoid this.
I believe one work-around would be to go through your trigger and remove all the line-endings, then add them back. That should create a uniform DDL statement.
|
Dwight
|
|
Posts:
14
Registered:
11/29/11
|
|
|
|
Re: Creating triggers in post-deploymen script
Posted:
Mar 1, 2012 9:34 AM
in response to: Dwight
|
|
|
Dwight,
Even if I run a deployment with no pre/post script I am still getting an output script file that contains mixed line endings: it appears that all the db packages are in LF endings and other statements (e.g. create table) have CR/LF endings.
Even stranger is that if I use the Export To File option from a schema comparison then the output generated there has CR/LF endings throughout.
|
|
|
|
Legend
|
|
MVP: 2501
+
pts
|
|
Guru: 2001
- 2500
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
