Forum Stats

  • 3,757,169 Users
  • 2,251,204 Discussions
  • 7,869,749 Comments

Discussions

Oracle Data Modeler - DDL Script preview

hi,

may i have an help about ddl script appearance, when i select DDL preview from a table on Phisycal Design i see this for trigger syntax, there are all LF in bad position and the code was write in one row format:

i.e. an example

CREATE OR REPLACE TRIGGER USER.TR00_MY_TAB

BEFORE INSERT OR UPDATE ON USER.MY_TAB

FOR EACH ROW

ENABLE DECLARE

V_SYSTIMESTAMP TIMESTAMP(6) DEFAULT SYSTIMESTAMP;

BEGIN

IF NOT ( INSERTING AND USER = ‘XXXXX’ AND UPPER ( SYS_CONTEXT ( ‘USERENV’ , ‘OS_USER’ ) ) = ‘ORACLE’ AND UPPER ( SYS_CONTEXT (

‘USERENV’ , ‘HOST’ ) ) IN ( ‘XXXXX’ , ‘XXXXX’ ) AND UPPER ( SYS_CONTEXT ( ‘USERENV’ , ‘DB_NAME’ ) ) IN ( ‘XXXXX’ ,

‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ ) ) THEN IF INSERTING THEN : NEW . ID_TMST_INS : = V_SYSTIMESTAMP ;

END IF ; : NEW . ID_TMST_UPD : = V_SYSTIMESTAMP ; : NEW . ID_DEN_FUNZIONE : = TRIM ( : NEW . ID_DEN_FUNZIONE ) ; : NEW . ID_DEN_OPERATORE : =

TRIM ( : NEW . ID_DEN_OPERATORE ) ; : NEW . ID_TXT_COMMENTO : = TRIM ( : NEW . ID_TXT_COMMENTO ) ; END IF ; EXCEPTION WHEN OTHERS

THEN RAISE_APPLICATION_ERROR ( – 20599 , ‘Errore Trigger: ‘ | | $$PLSQL_UNIT | | ‘ alla linea: ‘ | | $$PLSQL_LINE | | CHR ( 10 ) | |

TRIM ( SQLERRM ) ) ; END ;

/

————————————

i prefer this layout

CREATE OR REPLACE TRIGGER USER.TR00_MY_TAB BEFORE

INSERT OR UPDATE ON USER.MY_TAB

FOR EACH ROW

ENABLE

DECLARE

V_SYSTIMESTAMP TIMESTAMP(6) DEFAULT SYSTIMESTAMP;

BEGIN

IF NOT ( INSERTING AND USER = ‘XXXXXX’

AND UPPER(SYS_CONTEXT(‘USERENV’, ‘OS_USER’)) = ‘ORACLE’

AND UPPER(SYS_CONTEXT(‘USERENV’, ‘HOST’)) IN ( ‘XXXXXX’, ‘XXXXXX’ )

AND UPPER(SYS_CONTEXT(‘USERENV’, ‘DB_NAME’)) IN

( ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’ )

) THEN

IF INSERTING THEN

:NEW.ID_TMST_INS := V_SYSTIMESTAMP;

END IF;

:NEW.ID_TMST_UPD := V_SYSTIMESTAMP;

:NEW.ID_DEN_FUNZIONE := TRIM(:NEW.ID_DEN_FUNZIONE);

:NEW.ID_DEN_OPERATORE := TRIM(:NEW.ID_DEN_OPERATORE);

:NEW.ID_TXT_COMMENTO := TRIM(:NEW.ID_TXT_COMMENTO);

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20599, ‘Errore Trigger: ‘ || $$PLSQL_UNIT || ‘ alla linea: ‘

|| $$PLSQL_LINE || CHR(10) || TRIM(SQLERRM));

END;

/

thanks

Tagged:

Answers

  • User_NRYK9
    User_NRYK9 Member Posts: 9 Red Ribbon

    hi,  

    it’s possible in Oracle Data Modeler have/ create an xml tamplate to customize the layout /appearance of a ddl script before it was generate?

    The view of a Trigger syntax in a DDL script preview of a Tables in Phisycal Model isn't the same when i 'm lookin for the same code from the Trigger list, as i post in a precedent post.


  • User_NRYK9
    User_NRYK9 Member Posts: 9 Red Ribbon

    Hi i observe that if i use in the body of a trigger the syntax clause DECLARE thats give the problem about indentation that i note when i produce the DDL Preview for a Table from a Phisycal model Design.

    Using in a trigger body the syntax structure

    DECLARE

    BEGIN

    END;