On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,629 Users
  • 2,269,769 Discussions
  • 7,916,800 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;