Error DbVisualizer creating triggers

Question

I am using the version of DbVisualizer Pro 9.5.6 and I am running the following sql command to create triggers.

CREATE OR REPLACE TRIGGER "MYDB"."TG_T01_01" BEFORE INSERT ON T01
FOR EACH ROW
BEGIN
                :NEW.FECALT_01 := SYSDATE;
                :NEW.FECMOD_01 := SYSDATE;                  
                SELECT T01Q00.NEXTVAL INTO :NEW.ID_01 FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER "MYDB"."TG_T01_02" 
BEFORE UPDATE ON T01
FOR EACH ROW
BEGIN
                :NEW.FECMOD_01 := SYSDATE;              
END;
/...

They are a list of identical triggers of the different tables of the BBDD. When I run the sql command it shows me these error messages:

14:42:07 [CREATE - 0 rows, 0.032 secs] Command processed. No rows were affected

14:42:07 [:NEW.USUMOD_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [END - 0 rows, 0.000 secs] [Code: 900, SQL State: 42000] ORA-00900: sentencia SQL no válida

14:42:07 [/ - 0 rows, 0.000 secs] [Code: 900, SQL State: 42000] ORA-00900: sentencia SQL no válida

14:42:07 [:NEW.FECMOD_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [:NEW.USUALT_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [:NEW.USUMOD_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [SELECT - 0 rows, 0.000 secs] [Code: 1008, SQL State: 72000] ORA-01008: no todas las variables han sido enlazadas

14:42:07 [END - 0 rows, 0.015 secs] [Code: 900, SQL State: 42000] ORA-00900: sentencia SQL no válida

...

After this a trigger is created in the following way:

CREATE OR REPLACE TRIGGER "MYDB"."TG_T01_01" BEFORE INSERT ON T01
    FOR EACH ROW
    BEGIN
                    :NEW.FECALT_01 := SYSDATE

Note: The trigger that has been created wrong can be edited and put the code, the trigger does work and the code is well written, so I have concluded that the most likely is that the sql interpreter of DbVisualizer considers the ; as an END.

This looks like an Oracle RDBMS question. If that's the case, please remove the MySQL tag. — Frank Schmitt, Aug 10 '18 at 06:24

score 1 · Answer 1 · answered Aug 10 '18 at 05:16

As far as I can tell, this is wrong:

SELECT 01Q00.NEXTVAL INTO :NEW.ID_01 FROM DUAL;

because it is an invalid sequence name - should have a letter as a first character, not a number. Here's an example:

SQL> create table t01
  2    (id_01       number,
  3     fecalt_01   date,
  4     fecmod_01   date
  5    );

Table created.

SQL> create sequence 01q00;
create sequence 01q00
                *
ERROR at line 1:
ORA-02277: invalid sequence name

If we disregard the error, let's create a trigger:

SQL> create or replace trigger tg_t01_01
  2  before insert on t01
  3  for each row
  4  begin
  5    :new.fecalt_01 := sysdate;
  6    :new.fecmod_01 := sysdate;
  7    select 01q00.nextval into :new.id_01 from dual;
  8  end;
  9  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER TG_T01_01:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
4/15     PL/SQL: ORA-00923: FROM keyword not found where expected

OK, let's then fix the sequence name and recreate the trigger (using a new sequence name):

SQL> create sequence seq01;

Sequence created.

SQL> create or replace trigger tg_t01_01
  2  before insert on t01
  3  for each row
  4  begin
  5    :new.fecalt_01 := sysdate;
  6    :new.fecmod_01 := sysdate;
  7    select seq01.nextval into :new.id_01 from dual;
  8  end;
  9  /

Trigger created.

SQL>

Seems to be OK, eh?

Srry, the bad sequence name is a transcripting error, I edit the question. — Alda, Aug 10 '18 at 05:22
In that case, code you posted is **valid**, as far as Oracle is concerned (as I showed in my example). I wouldn't know what causes errors as I don't use DbVisualizer , sorry. — Littlefoot, Aug 10 '18 at 05:26
yes, the code works but im triying execute the sql in dbVisualizer it dont works. My problem isnt the sql. The problem is in dbVisualizer — Alda, Aug 10 '18 at 05:29

score 0 · Answer 2 · answered Aug 10 '18 at 06:12

0

I install Oracle SQL Developer and the sql works correctly.

answered Aug 10 '18 at 06:12

Alda

9
3

score 0 · Answer 3 · answered Aug 10 '18 at 20:29

CREATE TRIGGER is what is called a "complex statement" in DbVisualizer, since it contains nested statements. Please see the Users Guide for how to execute this kind of statement:

http://confluence.dbvis.com/display/UG100/Executing+Complex+Statements

Best Regards Hans

Error DbVisualizer creating triggers

3 Answers3