Oracle procedure/function to create a trigger in table

Question

I'm trying to create a procedure that given a table name, it will create a sequence and auto incrementing trigger, all using variables based on the table name.

Code :

CREATE OR REPLACE procedure CREATE_SEQUENTIAL_TR(table_name VARCHAR)
is -- Tried using declare but it wouldn't accept
    coluna_cod varchar(100 char);
begin
    --Finding the cod column name for this table first
    --They start with "PK_CD"
    select 
        COLUMN_NAME 
    into
        coluna_cod
    from 
        ALL_TAB_COLUMNS 
    where 
        TABLE_NAME=table_name 
        and COLUMN_NAME like "PK_CD%";
    --Creating the sequence obj
    drop sequence "cod" || table_name;
    create sequence "cod" || table_name;
    --Now creating the trigger
    create or replace trigger "cod" || table_name || "tr"           
    before 
        UPDATE or INSERT on table_name
    for each row
    declare
        cod number := coluna_cod;
        tr_name varchar(100 char) := "cod" || table_name
    begin
        if UPDATING then
            if :new.cod != :old.cod then
                :new.cod := :old.cod;
            end if;
        else -- inserting
            :new.cod := tr_name.nextval();
        end if; 
    end;
end;

The complexity of this ended up quite out of the scope of my knowledge.

At the moment it is giving an error on drop sequence "cod" || table_name (Unexpected DROP symbol found) but I'm sure I have made other errors.

Can someone help me figure this logic out?

You cannot execute DDL statements in a pl/sql block. You either have to use execute immediate, or just write a script and run through sqlplus — OldProgrammer
– OldProgrammer, Commented Sep 14, 2017 at 20:26

kfinity · Accepted Answer · 2017-09-14 20:41:14Z

2

You can't put DDL statements (like drop or create or alter) directly inside a PL/SQL block. If you want to do DDL inside PL/SQL, you can do an execute immediate:

declare
begin
  drop sequence X; -- error
  execute immediate 'drop sequence X'; -- works fine 
end;
/

answered Sep 14, 2017 at 20:41

kfinity

9,1411 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle procedure/function to create a trigger in table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related