It's about the code, I don't know why it displays "before 5 after 5".
CREATE OR REPLACE PROCEDURE plch_test
AS
BEGIN
NULL; -- 5 lines of code
END;
/
CREATE OR REPLACE TRIGGER plch_bca
BEFORE CREATE OR ALTER
ON SCHEMA
DECLARE
l_line_count PLS_INTEGER;
BEGIN
SELECT COUNT(1)
INTO l_line_count
FROM user_source src
WHERE src.name = ora_dict_obj_name
AND src.type = ora_dict_obj_type;
--
dbms_output.put_line('Before '||l_line_count);
END;
/
CREATE OR REPLACE TRIGGER plch_aca
AFTER CREATE OR ALTER
ON SCHEMA
DECLARE
l_line_count PLS_INTEGER;
BEGIN
SELECT COUNT(1)
INTO l_line_count
FROM user_source src
WHERE src.name = ora_dict_obj_name
AND src.type = ora_dict_obj_type;
--
dbms_output.put_line('After '||l_line_count);
END;
/
What will be displayed if I then issue this statement:
CREATE OR REPLACE PROCEDURE plch_test
AS
BEGIN
NULL;
-- five
-- more
-- lines
-- of
-- comment
END;
/
So you're expecting the output to be "before 5 after 10" because there's 10 lines in the updated procedure?
If so, the reason you don't is because Oracle hasn't written the change to the data dictionary at the time the after schema trigger fires.
You can see this by checking the last_ddl_time of the object in the after statement trigger.
SQL> create or replace procedure plch_test
2 as
3 begin
4 null; -- 5 lines of code
5 end;
6 /
Procedure created.
SQL>
SQL> create or replace trigger plch_bca before
2 create or alter on schema
3 declare
4 l_line_count pls_integer;
5 begin
6 select count ( 1 )
7 into l_line_count
8 from user_source src
9 where src.name = ora_dict_obj_name
10 and src.type = ora_dict_obj_type;
11 --
12 dbms_output.put_line ( 'Before '||l_line_count ) ;
13 end;
14 /
After 13,09:34:30
Trigger created.
SQL> create or replace trigger plch_aca after
2 create or alter on schema
3 declare
4 l_line_count pls_integer;
5 changed_dt date;
6 begin
7 select count ( 1 )
8 into l_line_count
9 from user_source src
10 where src.name = ora_dict_obj_name
11 and src.type = ora_dict_obj_type;
12
13 begin
14 select last_ddl_time into changed_dt from user_objects
15 where object_name = ora_dict_obj_name
16 and object_type = ora_dict_obj_type;
17 exception
18 when no_data_found then null;
19 end;
20 --
21 dbms_output.put_line ( 'After '||l_line_count || ',' ||
22 to_char(changed_dt, 'hh24:mi:ss')) ;
23 end;
24 /
Before 23
Trigger created.
SQL>
SQL> select to_char(last_ddl_time, 'hh24:mi:ss') from user_objects
2 where object_name = 'PLCH_TEST';
TO_CHAR(
--------
09:35:06
SQL>
SQL> exec dbms_lock.sleep(15);
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace procedure plch_test
2 as
3 begin
4 null;
5 -- five
6 -- more
7 -- lines
8 -- of
9 -- comment
10 end;
11 /
Before 5
After 5,09:35:06
Procedure created.
SQL> select to_char(last_ddl_time, 'hh24:mi:ss') from user_objects
2 where object_name = 'PLCH_TEST';
TO_CHAR(
--------
09:35:22
Notice that the times output from the trigger and the query are different.
Note: you need to handle the no_data_found exception for user_objects. Without this you can't create new objects! They're present at the time the after trigger fires, so you'll get a NDF. Unless you handle this, the exception will propagate to the client, the statement will fail and your new object won't exist!
SQL> create or replace trigger plch_aca after
2 create or alter on schema
3 declare
4 l_line_count pls_integer;
5 changed_dt date;
6 begin
7 select count ( 1 )
8 into l_line_count
9 from user_source src
10 where src.name = ora_dict_obj_name
11 and src.type = ora_dict_obj_type;
12
13 select last_ddl_time into changed_dt from user_objects
14 where object_name = ora_dict_obj_name
15 and object_type = ora_dict_obj_type;
16 --
17 dbms_output.put_line ( 'After '||l_line_count || ',' ||
18 to_char(changed_dt, 'hh24:mi:ss')) ;
19 end;
20 /
Before 23
Trigger created.
SQL>
SQL> create or replace procedure p_new
2 as
3 begin
4 null; -- 5 lines of code
5 end;
6 /
Before 0
create or replace procedure p_new
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'CHRIS.PLCH_ACA'
ORA-01403: no data found
ORA-06512: at line 11
SQL>
SQL> select to_char(last_ddl_time, 'hh24:mi:ss') from user_objects
2 where object_name = 'P_NEW';
no rows selected