Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Unable to compile compilable code

    I was trying an experiment in order to help Analysts start out with a base trigger which complies with our standards. The following code creates the appropriate code for a trigger, but the database treats the entirety of the following as one line of code, which you can see if you query USER_SOURCE. Is there something I can do in order to have the db recognize the CHR(13)? I even tried CHR(13)||CHR(10).

    Code:
    SQL> declare 
      2   plsql varchar2(4000);
      3  begin
      4  
      5   plsql :=          'CREATE OR REPLACE TRIGGER FORBESC.DORK_BT'||CHR(13);
      6   plsql := plsql || ' BEFORE INSERT OR UPDATE OR DELETE'||CHR(13);
      7   plsql := plsql || ' ON FORBESC.DORK'||CHR(13);
      8   plsql := plsql || ' FOR EACH ROW'||CHR(13);
      9   plsql := plsql || 'DECLARE'||CHR(13);
     10   plsql := plsql || '  UserID            VarChar2(30) := USER;'||CHR(13);
     11   plsql := plsql || '  Null_Date         Date := TO_DATE(''01-jan-1900'',''DD-MON-YYYY'');'||CHR
    (13);
     12   plsql := plsql || 'BEGIN'||CHR(13);
     13   plsql := plsql || '  /* << apply datestamps, timestamps and userid stamps >> */'||CHR(13);
     14   plsql := plsql || '  /* MAINTAINED STAMP COLUMNS */'||CHR(13);
     15   plsql := plsql || '  if updating'||CHR(13);
     16   plsql := plsql || '   or (inserting and nvl(:new.MOD_DT,null_date) = null_date) then'||CHR(13)
    ;
     17   plsql := plsql || '    :new.MOD_DT := sysdate;'||CHR(13);
     18   plsql := plsql || '  end if;'||CHR(13);
     19   plsql := plsql || '  if updating'||CHR(13);
     20   plsql := plsql || '   or (inserting and nvl(:new.MOD_ID,'' '') = '' '') then'||CHR(13);
     21   plsql := plsql || '    :new.MOD_ID := userid;'||CHR(13);
     22   plsql := plsql || '  end if;'||CHR(13);
     23   plsql := plsql || '  /* INSERT ONLY STAMP COLUMNS */'||CHR(13);
     24   plsql := plsql || '  if (inserting and nvl(:new.CREATE_DT,null_date) = null_date) then'||CHR(1
    3);
     25   plsql := plsql || '    :new.CREATE_DT := sysdate;'||CHR(13);
     26   plsql := plsql || '  end if;'||CHR(13);
     27   plsql := plsql || '  if (inserting and nvl(:new.CREATE_ID,'' '') = '' '') then'||CHR(13);
     28   plsql := plsql || '    :new.CREATE_ID := userid;'||CHR(13);
     29   plsql := plsql || '  end if;'||CHR(13);
     30   plsql := plsql || 'END;';
     31   
     32   insert into hold_sql values (plsql); 
     33  
     34   begin
     35     execute immediate plsql;
     36   exception 
     37     when others then
     38       null;
     39   end;
     40    
     41   plsql := 'alter trigger forbesc.dork_bt compile';
     42   execute immediate plsql;
     43   
     44  end;
     45  /
    ERROR:
    ORA-24344: success with compilation error
    ORA-06512: at line 42
    
    
    
    Warning: PL/SQL compilation errors.
    If I omit the CHR() call altogether, the trigger compiles, but it doesn't view properly in TOAD. It looks like one long line of code there.

    -Chuck
    Last edited by chuck_forbes; 06-13-06 at 14:59.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    sometimes I think TOAD causes more problems than it helps.
    why can't you use sqlplus and run the create statement normally?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    When you use execute immediate, the statement is treated as one continuous string. If you want to have line breaks, then use sql*plus. Why do you want to dynamically create triggers. If you want the analysts to be able to make triggers, then create a script and run it via sql*plus. Pushing everything through TOAD is just plain silly!!!!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    So let me get this straight. You're saying that I should use TOAD, because it's your favorite tool of all time?

    Really the problem in TOAD resulted when I tried to view the outcome of a successful run of this script, not run it. It looked like:

    Code:
    CREATE OR REPLACE TRIGGER FORBESC.DORK_BT BEFORE INSERT OR UPDATE OR DELETE ON FORBESC.DORK FOR EACH ROW DECLARE UserID            VarChar2(30) := sso.userid; Null_Date         Date := TO_DATE('01-jan-1900','DD-MON-YYYY');BEGIN  /* << apply datestamps, timestamps and userid stamps >> */  /* MAINTAINED STAMP COLUMNS */  if updating    or (inserting and nvl(:new.MOD_DT,null_date) = null_date) then      :new.MOD_DT := sysdate;  end if;  if updating
    ...
    I'll get something else working, I'm sure, even if it's just outputting this same information to DBMS_OUTPUT or something, then copying and pasting.
    -cf

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    why not open the Procs tab in toad and view the trigger (once created) from
    there?

    also, in toad, cut/paste that one line into the sql-window.
    One in the window, highlight the line of code, r-click, select formatting,
    then select format code.

    TOAD will format it for you according to your settings.
    it only takes about 2 seconds.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Oooo, that's right. Good tip.
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •