This discussion is archived
10 Replies Latest reply: Oct 4, 2012 11:44 AM by Maxo RSS

Extra line break in triggers.

Maxo Newbie
Currently Being Moderated
As of the new 3.2 release, triggers get an extra line break inserted after the FOR EACH ROW line.

For example, if I create the following trigger.

create or replace trigger foo_trig
before update
on foo_table
for each row
declare
null;
end foo_trig;

Then the next time I open the trigger it shows up in the worksheet as follows.

create or replace trigger foo_trig
before update
on foo_table
for each row

declare
null;
end foo_trig;

If I keep saving and reopening this trigger the number of spaces will be incremented each time. A review of the source from the USER_SOURCE view shows that the line break is not there initially. The worksheet is inserting it when the object is opened.

Edited by: Maxo on Sep 26, 2012 8:30 AM
  • 1. Re: Extra line break in triggers.
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Maxo,

    1/You are missing a begin.
    2/I cannot reproduce.
    3/Is there an invisible character such as a carriage return \r embedded in the source? This has caused extra newline problems elsewhere.

    -Turloch
    SQLDeveloper team
  • 2. Re: Extra line break in triggers.
    Maxo Newbie
    Currently Being Moderated
    1. Sorry about that. That's only an error in my example code, not in my real code.
    2. Ok.
    3. I don't think so. I have tried removing all carriage returns in the area and readding them to make sure, but it did not make a difference. I see this problem on all triggers, but not on any other objects.

    I have a video of me reproducing the issue which can be found at http://maxolasersquad.com/out.ogv
    Is there any other information I can provide to help pinpoint this issue?
  • 3. Re: Extra line break in triggers.
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Maxo,

    I will try the video at home. (This will give me 1/)
    1/Where are you seeing the trigger sql text - in the sql tab of the table or the triggers tab?
    I can then track down where the text is coming from.
    2/What version of the database is this against?

    -Turloch
    SQLDeveloper Team

    Text can be auto formatted see: (There is another bug logged against trigger formatting so you may want this off).
    tools->preferences->database->sqlformatter->autoformat
    If the formatter is the problem send in the text of the trigger and the settings: tools->preferences->database->sqlformatter->import and export all the settings
  • 4. Re: Extra line break in triggers.
    Maxo Newbie
    Currently Being Moderated
    I am viewing the trigger code by expanding the Trigger node in the Connections list. The database is Oracle 11.2.0.3.0.
    One of my coworkers is going to upgrade his sqldeveloper today and see if he is having the same issue.
  • 5. Re: Extra line break in triggers.
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Maxo,

    What platform are you using for database and SQLDeveloper?
    The raw text comes from (a where clause for the schema will be required if there is more than one FOO_TRIG')
    -select text from sys.dba_source where name = 'FOO_TRIG' order by line;
    to look for extra 'invisible' characters:
    -select dump(text,1016) from sys.dba_source where name = 'FOO_TRIG' order by line;
    -select dump(text,1017) from sys.dba_source where name = 'FOO_TRIG' order by line;
    with create or replace added at the top.
    do those have the extra newline?
    -does right click -> quick ddl -> to worksheet have the extra newline?

    I am looking for a reproducible test case so I can submit a bug report.

    -Turloch
    SQLDeveloper Team
  • 6. Re: Extra line break in triggers.
    Maxo Newbie
    Currently Being Moderated
    I am using Ubuntu 12.04. I asked a coworker who is running Windows XP to try and he was having the same issue with the latest release of sqldeveloper.
    The following is the results. I used user_source, but I don't think that should make any difference. It is broken out into separate posts to appease the character limit gods.
  • 7. Re: Extra line break in triggers.
    Maxo Newbie
    Currently Being Moderated
    select text from user_source where name = 'IO_INSERT_A_TRG' order by line;
    TEXT
    ----------------------------------------------------
    trigger oasis_inventory.io_insert_a_trg


    after insert


    on oasis_inventory.installed_option


    for each row


    declare


    l_io oasis_inventory.installed_option%ROWTYPE;


    begin


    l_io.io_id := :new.io_id;


    l_io.io_voa_id := :new.io_voa_id;


    l_io.io_si_id := :new.io_si_id;


    l_io.io_status := :new.io_status;


    l_io.io_sinst_id := :new.io_sinst_id;


    l_io.io_svg_id := :new.io_svg_id;


    oasis_inventory.inventory_pkg.recache_inventory_p(l_io);


    end io_insert_a_trg;


    select dump(text,1016) from user_source where name = 'IO_INSERT_A_TRG' order by line;

    DUMP(TEXT,1016)
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Typ=1 Len=40 CharacterSet=AL32UTF8: 74,72,69,67,67,65,72,20,6f,61,73,69,73,5f,69,6e,76,65,6e,74,6f,72,79,2e,69,6f,5f,69,6e,73,65,72,74,5f,61,5f,74,72,67,a
    Typ=1 Len=15 CharacterSet=AL32UTF8: 20,20,61,66,74,65,72,20,69,6e,73,65,72,74,a
    Typ=1 Len=38 CharacterSet=AL32UTF8: 20,20,6f,6e,20,6f,61,73,69,73,5f,69,6e,76,65,6e,74,6f,72,79,2e,69,6e,73,74,61,6c,6c,65,64,5f,6f,70,74,69,6f,6e,a
    Typ=1 Len=15 CharacterSet=AL32UTF8: 20,20,66,6f,72,20,65,61,63,68,20,72,6f,77,a
    Typ=1 Len=8 CharacterSet=AL32UTF8: 64,65,63,6c,61,72,65,a
    Typ=1 Len=49 CharacterSet=AL32UTF8: 20,20,6c,5f,69,6f,20,6f,61,73,69,73,5f,69,6e,76,65,6e,74,6f,72,79,2e,69,6e,73,74,61,6c,6c,65,64,5f,6f,70,74,69,6f,6e,25,52,4f,57,54,59,50,45,3b,a
    Typ=1 Len=6 CharacterSet=AL32UTF8: 62,65,67,69,6e,a
    Typ=1 Len=28 CharacterSet=AL32UTF8: 20,20,6c,5f,69,6f,2e,69,6f,5f,69,64,20,3a,3d,20,3a,6e,65,77,2e,69,6f,5f,69,64,3b,a
    Typ=1 Len=36 CharacterSet=AL32UTF8: 20,20,6c,5f,69,6f,2e,69,6f,5f,76,6f,61,5f,69,64,20,3a,3d,20,3a,6e,65,77,2e,69,6f,5f,76,6f,61,5f,69,64,3b,a
    Typ=1 Len=34 CharacterSet=AL32UTF8: 20,20,6c,5f,69,6f,2e,69,6f,5f,73,69,5f,69,64,20,3a,3d,20,3a,6e,65,77,2e,69,6f,5f,73,69,5f,69,64,3b,a
    Typ=1 Len=36 CharacterSet=AL32UTF8: 20,20,6c,5f,69,6f,2e,69,6f,5f,73,74,61,74,75,73,20,3a,3d,20,3a,6e,65,77,2e,69,6f,5f,73,74,61,74,75,73,3b,a
    Typ=1 Len=40 CharacterSet=AL32UTF8: 20,20,6c,5f,69,6f,2e,69,6f,5f,73,69,6e,73,74,5f,69,64,20,3a,3d,20,3a,6e,65,77,2e,69,6f,5f,73,69,6e,73,74,5f,69,64,3b,a
    Typ=1 Len=36 CharacterSet=AL32UTF8: 20,20,6c,5f,69,6f,2e,69,6f,5f,73,76,67,5f,69,64,20,3a,3d,20,3a,6e,65,77,2e,69,6f,5f,73,76,67,5f,69,64,3b,a
    Typ=1 Len=59 CharacterSet=AL32UTF8: 20,20,6f,61,73,69,73,5f,69,6e,76,65,6e,74,6f,72,79,2e,69,6e,76,65,6e,74,6f,72,79,5f,70,6b,67,2e,72,65,63,61,63,68,65,5f,69,6e,76,65,6e,74,6f,72,79,5f,70,28,6c,5f,69,6f,29,3b,a
    Typ=1 Len=20 CharacterSet=AL32UTF8: 65,6e,64,20,69,6f,5f,69,6e,73,65,72,74,5f,61,5f,74,72,67,3b


    select dump(text,1017) from user_source where name = 'IO_INSERT_A_TRG' order by line;

    DUMP(TEXT,1017)
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Typ=1 Len=40 CharacterSet=AL32UTF8: t,r,i,g,g,e,r, ,o,a,s,i,s,_,i,n,v,e,n,t,o,r,y,.,i,o,_,i,n,s,e,r,t,_,a,_,t,r,g,^J
    Typ=1 Len=15 CharacterSet=AL32UTF8: , ,a,f,t,e,r, ,i,n,s,e,r,t,^J
    Typ=1 Len=38 CharacterSet=AL32UTF8: , ,o,n, ,o,a,s,i,s,_,i,n,v,e,n,t,o,r,y,.,i,n,s,t,a,l,l,e,d,_,o,p,t,i,o,n,^J
    Typ=1 Len=15 CharacterSet=AL32UTF8: , ,f,o,r, ,e,a,c,h, ,r,o,w,^J
    Typ=1 Len=8 CharacterSet=AL32UTF8: d,e,c,l,a,r,e,^J
    Typ=1 Len=49 CharacterSet=AL32UTF8: , ,l,_,i,o, ,o,a,s,i,s,_,i,n,v,e,n,t,o,r,y,.,i,n,s,t,a,l,l,e,d,_,o,p,t,i,o,n,%,R,O,W,T,Y,P,E,;,^J
    Typ=1 Len=6 CharacterSet=AL32UTF8: b,e,g,i,n,^J
    Typ=1 Len=28 CharacterSet=AL32UTF8: , ,l,_,i,o,.,i,o,_,i,d, ,:,=, ,:,n,e,w,.,i,o,_,i,d,;,^J
    Typ=1 Len=36 CharacterSet=AL32UTF8: , ,l,_,i,o,.,i,o,_,v,o,a,_,i,d, ,:,=, ,:,n,e,w,.,i,o,_,v,o,a,_,i,d,;,^J
    Typ=1 Len=34 CharacterSet=AL32UTF8: , ,l,_,i,o,.,i,o,_,s,i,_,i,d, ,:,=, ,:,n,e,w,.,i,o,_,s,i,_,i,d,;,^J
    Typ=1 Len=36 CharacterSet=AL32UTF8: , ,l,_,i,o,.,i,o,_,s,t,a,t,u,s, ,:,=, ,:,n,e,w,.,i,o,_,s,t,a,t,u,s,;,^J
    Typ=1 Len=40 CharacterSet=AL32UTF8: , ,l,_,i,o,.,i,o,_,s,i,n,s,t,_,i,d, ,:,=, ,:,n,e,w,.,i,o,_,s,i,n,s,t,_,i,d,;,^J
    Typ=1 Len=36 CharacterSet=AL32UTF8: , ,l,_,i,o,.,i,o,_,s,v,g,_,i,d, ,:,=, ,:,n,e,w,.,i,o,_,s,v,g,_,i,d,;,^J
    Typ=1 Len=59 CharacterSet=AL32UTF8: , ,o,a,s,i,s,_,i,n,v,e,n,t,o,r,y,.,i,n,v,e,n,t,o,r,y,_,p,k,g,.,r,e,c,a,c,h,e,_,i,n,v,e,n,t,o,r,y,_,p,(,l,_,i,o,),;,^J
    Typ=1 Len=20 CharacterSet=AL32UTF8: e,n,d, ,i,o,_,i,n,s,e,r,t,_,a,_,t,r,g,;


    The following is what I get from Quick DDL.
    CREATE OR REPLACE TRIGGER "OASIS_INVENTORY"."IO_INSERT_A_TRG"
    after insert
    on oasis_inventory.installed_option
    for each row
    declare
    l_io oasis_inventory.installed_option%ROWTYPE;
    begin
    l_io.io_id := :new.io_id;
    l_io.io_voa_id := :new.io_voa_id;
    l_io.io_si_id := :new.io_si_id;
    l_io.io_status := :new.io_status;
    l_io.io_sinst_id := :new.io_sinst_id;
    l_io.io_svg_id := :new.io_svg_id;
    oasis_inventory.inventory_pkg.recache_inventory_p(l_io);
    end io_insert_a_trg;
    /
    ALTER TRIGGER "OASIS_INVENTORY"."IO_INSERT_A_TRG" ENABLE;

    In all of this I don't see any anomalies.
  • 8. Re: Extra line break in triggers.
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Maxo,

    I am not seeing your issue - but there is a second code path for a non dba user :
    which concatenated description and trigger_body from all_triggers - between description and trigger_body is where you see the extra newline. I will try on 11.2 and with a declare not a begin as you have.

    -Turloch
    SQLDeveloper Team
  • 9. Re: Extra line break in triggers.
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Maxo,

    I see the bug now using released SQLDeveloper 3.2.10.09.57 and non dba user.
    Bug 14712359 - EXTRA LINE BREAK IN TRIGGERS.

    -Turloch
    SQLDeveloper Team
  • 10. Re: Extra line break in triggers.
    Maxo Newbie
    Currently Being Moderated
    Great. I eagerly await the next release. I'm something of a formatting nazi, so this kind of small stuff really bugs me. I appreciate you looking into this for me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points