Hi,
Toad 12.12 x64.
Rebuild table, in trigger definition is changed (formatted) so when you compare source and new version it shows differences.
it has no functional but formatting differences.
However, it should stay as it was-without formatting from Toad.
Possible in new release? Or at least to have a option “leave sorce as is” or “do not format source” …you know what I mean.
thx
Damir
Over a year now I have not yet resolve issue with toad schema browser is running slow in oracle 12c RAC database but running ok with oracle 10g RAC database. Does anyone come up with any resolution to this issue yet.
Please open new thread. This has nothing with mine topic.
Damir, as far as I can tell, the trigger formatting is not changed. The only difference that I could find is the addition of the schema name before the table name in the trigger description. I have made a change so that we don’t add this unless necessary (that is, unless you are rebuilding a table in a different schema)
Hi John,
Here is example where trigger source is different (and not only in schema name part):
org:
create or replace TRIGGER PURGE_NOT_USED_PREPAID_RE
BEFORE INSERT ON APE1_REJECTED_EVENT
FOR EACH ROW
when (NEW.cycle_code = '80')
DECLARE
vResult NUMBER(9);
BEGIN
vResult := 0;
IF
(:NEW.resource_type = 'I') THEN
SELECT COUNT() INTO vResult FROM XX
WHERE COL1 = :NEW.resource_value OR COL1 = 'PREBOB' OR COL1 = 'B';
ELSIF
(:NEW.resource_type = 'C') THEN
SELECT COUNT() INTO vResult FROM YY
WHERE col1 = :NEW.resource_value or col1 = 'PREBOB' or col1 = 'B';
END IF;
IF (vResult > 0) THEN
:NEW.RESOURCE_TYPE := 'PRE';
END IF;
END;
/
here is generated one
-- Rebuild triggers for the new table.
DROP TRIGGER Q383211.PURGE_NOT_USED_PREPAID_RE;
CREATE OR REPLACE TRIGGER Q383211.PURGE_NOT_USED_PREPAID_RE
BEFORE INSERT ON Q383211.APE1_REJECTED_EVENT
FOR EACH ROW
WHEN (
NEW.cycle_code = '80'
** )**
DECLARE
vResult NUMBER(9);
BEGIN
vResult := 0;
IF
(:NEW.resource_type = 'I') THEN
SELECT COUNT() INTO vResult FROM XX
WHERE COL1 = :NEW.resource_value OR COL1 = 'PREBOB' OR COL1 = 'B';
ELSIF
(:NEW.resource_type = 'C') THEN
SELECT COUNT() INTO vResult FROM YY
WHERE col1 = :NEW.resource_value or col1 = 'PREBOB' or col1 = 'B';
END IF;
IF (vResult > 0) THEN
:NEW.RESOURCE_TYPE := 'PRE';
END IF;
END;
/
SHOW ERRORS;
or as original picture from Toad (read only script part)
Brg
Damir
and later when I was comparing line by line from source and “old source” … i got more than dozen of differences (all lines were different by line number compassion) …and seems to be only one.
Oh, the WHEN part, OK, yeah, I see that Toad is “fixing” the whitespace around it. I’ll fix that.
Hi John,
Thx for reply.
Indeed Toad is adding extra lines as you said.
But for space I could not reproduce your case.
Played with spaces in several ways in your example and do not see that Oracle is having problems. If you could show pics or real test where you saw spacing problem, so I can see what did I missed.
I am on 11.2.0.3 x64 DB.
Brg
Damir
Brg,**
________________________**
Damir Vadas
http://damir-vadas.blogspot.com
It does not matter what you have in your life, but who!
Razmislite o čuvanju okoliša prije nego odštampate ovaj e-mail
Please consider the environment before printing this email
On Wed, Jul 26, 2017 at 3:29 PM, John Dorlon bounce-jdorlon@toadworld.com wrote:
Reply by John Dorlon
Oh, the WHEN part, OK.
Oracle doesn't always preserve the formatting around that part, but I see that Toad is adding a line feed before and after. I'll remove it.
As a test, try creating this trigger with different spacing and line feeds around the "(1=1) " part. Then look in dba_triggers.WHEN_CLAUSE. You'll see that the spacing is not always preserved.
CREATE OR REPLACE TRIGGER JDORLON.TRG_TEST1
BEFORE DELETE
ON JDORLON.DEPT
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (1=1)
begin
null;
end;
/
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
try this trigger. add spaces or lines before or after 1=1, but inside the parens. Prior versions of Toad would remove all that whitespace and then define its own whitespace… Now it will be preserved.
Oracle eliminates the whitespace outside the parens.
CREATE OR REPLACE TRIGGER JDORLON.DEPT_TEST
BEFORE DELETE
ON JDORLON.DEPT
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (1=1)
begin
null;
end;
/
I have made a test. Here are the results:
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 27 15:38:48 2017
Copyright © 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Elapsed: 00:00:00.02
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>CREATE OR REPLACE TRIGGER TRG_TEST1
15:38:49 2 BEFORE DELETE
15:38:49 3 ON DEPARTMENTS
15:38:49 4 REFERENCING NEW AS New OLD AS Old
15:38:49 5 FOR EACH ROW
15:38:49 6 WHEN (1 = 1)
15:38:49 7 BEGIN
15:38:49 8 NULL;
15:38:49 9 END;
15:38:49 10 /
Trigger created.
Elapsed: 00:00:00.02
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>select ‘"’|| WHEN_CLAUSE|| ‘"’ from user_triggers where table_name=‘DEPARTMENTS’ and trigger_name =‘TRG_TEST1’;
“1 = 1”
Elapsed: 00:00:00.02
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>CREATE OR REPLACE TRIGGER TRG_TEST2
15:38:49 2 BEFORE DELETE
15:38:49 3 ON DEPARTMENTS
15:38:49 4 REFERENCING NEW AS New OLD AS Old
15:38:49 5 FOR EACH ROW
15:38:49 6 WHEN ( 1 = 1 )
15:38:49 7 BEGIN
15:38:49 8 NULL;
15:38:49 9 END;
15:38:49 10 /
Trigger created.
Elapsed: 00:00:00.03
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>select ‘"’|| WHEN_CLAUSE|| ‘"’ from user_triggers where table_name=‘DEPARTMENTS’ and trigger_name =‘TRG_TEST2’;
" 1 = 1 "
Elapsed: 00:00:00.02
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>CREATE OR REPLACE TRIGGER TRG_TEST3
15:38:49 2 BEFORE DELETE
15:38:49 3 ON DEPARTMENTS
15:38:49 4 REFERENCING NEW AS New OLD AS Old
15:38:49 5 FOR EACH ROW
15:38:49 6 WHEN ( 1 =1)
15:38:49 7 BEGIN
15:38:49 8 NULL;
15:38:49 9 END;
15:38:49 10 /
Trigger created.
Elapsed: 00:00:00.04
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>select ‘"’|| WHEN_CLAUSE|| ‘"’ from user_triggers where table_name=‘DEPARTMENTS’ and trigger_name =‘TRG_TEST3’;
" 1 =1"
Elapsed: 00:00:00.02
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>CREATE OR REPLACE TRIGGER TRG_TEST4
15:38:49 2 BEFORE DELETE
15:38:49 3 ON DEPARTMENTS
15:38:49 4 REFERENCING NEW AS New OLD AS Old
15:38:49 5 FOR EACH ROW
15:38:49 6 WHEN (1= 1 )
15:38:49 7 BEGIN
15:38:49 8 NULL;
15:38:49 9 END;
15:38:49 10 /
Trigger created.
Elapsed: 00:00:00.05
15:38:49 Q383211@SMOBCST1>
15:38:49 Q383211@SMOBCST1>select ‘"’|| WHEN_CLAUSE|| ‘"’ from user_triggers where table_name=‘DEPARTMENTS’ and trigger_name =‘TRG_TEST4’;
"1= 1 "
Elapsed: 00:00:00.02
15:38:49 Q383211@SMOBCST1>
Do not understand part “Oracle removes space outside of parenthesis”
However, if all is clear to you…I can find this thread closed.
15:43:34 Q383211@SMOBCST1>select * from user_source where name =‘TRG_TEST4’;
NAME TYPE LINE TEXT
TRG_TEST4 TRIGGER 1 TRIGGER TRG_TEST4
TRG_TEST4 TRIGGER 2 BEFORE DELETE
TRG_TEST4 TRIGGER 3 ON DEPARTMENTS
TRG_TEST4 TRIGGER 4 REFERENCING NEW AS New OLD AS Old
TRG_TEST4 TRIGGER 5 FOR EACH ROW
TRG_TEST4 TRIGGER 6 WHEN (1= 1 )
TRG_TEST4 TRIGGER 7 BEGIN
TRG_TEST4 TRIGGER 8 NULL;
TRG_TEST4 TRIGGER 9 END;
9 rows selected.
Elapsed: 00:00:00.00
15:43:34 Q383211@SMOBCST1>
