plsql - Sequence and Trigger PL/SQL script for Automatic ID generation on a table -

can me fix code. doesn´t run on pl/sql (sqlplus @script.sql), giving sp2-0552: bind variable "new" not declared.

script.sql

  prompt creating table systemdatalog;  declare      counter1 integer;     counter2 integer;  begin     select count(*) counter1 all_tables table_name='systemdatalog' , owner='mzmesdb';      if counter1 = 1          drop table systemdatalog;     end if;      select count(*) counter2 all_sequences sequence name='seqsystemdatalog';      if counter2 = 1          drop sequence seqsystemdatalogid;     endif;      create table "mzmesdb"."systemdatalog" ( "id" integer not null ,                           "datetime" date not null ,                           "type" varchar2(64) not null,                           "severity" integer not null,                          "source" varchar2(64) not null,                           "user" varchar2(64) not null,                          "message" varchar2(1024), primary key ("id") validate );       create sequence seqsystemdatalogid;      create or replace trigger trigsystemdatalogid         before insert on systemdatalog         each row             begin                 select seqsystemdatalogid.nextval :new.id dual;             end trigsystemdatalogid; end; / 

thanks in advance kind of help.

[definitive code]

prompt creating table systemdatalog;

declare counter1 integer; counter2 integer;

begin select count(*) counter1 all_tables table_name='systemdatalog' , owner='mzmesdb';

if counter1 = 1      execute immediate 'drop table systemdatalog'; end if;  select count(*) counter2 all_sequences sequence_name='seqsystemdatalog';  if counter2 = 1     execute immediate 'drop sequence seqsystemdatalogid'; end if;  execute immediate 'create table "mzmesdb"."systemdatalog" (                      "id" integer not null ,                      "datetime" date not null ,                      "type" varchar2(64) not null,                      "severity" integer not null,                     "source" varchar2(64) not null,                      "user" varchar2(64) not null,                     "message" varchar2(1024), primary key ("id") validate )';  execute immediate 'create sequence seqsystemdatalogid';  execute immediate 'create or replace trigger trigsystemdatalogid            before insert on systemdatalog            each row            begin             select seqsystemdatalogid.nextval :new.id dual;            end trigsystemdatalogid;'; 

end;

one problem mixing pl/sql (an anonymous pl/sql block) , ddl. while can write dml statements (select/insert/update/delete) in pl/sql cannot write ddl (create/drop etc) directly in pl/sql.

you need either run ddl statements outside anonymous pl/sql block or use execute immediate '<statement>';.

as error on :new think disappear when fix other issue. if doesn't, try adding:

set scan off

in beginning of sql*plus file.