SQL and PL/SQL practice

--Hierarchical queries
--Top down
select lpad(ename,length(ename)-1+level,'****') from emp connect by prior empno=mgr start with mgr is null;

--Down--up
SELECT LPAD (' ', LEVEL * 3 - 3)||ENAME ENAME
      FROM EMP
CONNECT BY PRIOR mgr = empno
START WITH MGR=7788;


--Write a query to print all prime numbers less than or equal to . Print your result on a single line, and use the ampersand () character as your separator (instead of a space).

with t as (    select level n
                 from dual
           connect by level <= 1000)
select listagg(N,'&') within group(order by n) from (select n,
       case
          when exists
                  (select null
                     from t t2
                    where t2.n > 1 and t2.n < t.n and t.n / t2.n = trunc (t.n / t2.n)) then 'no prime'
          else 'prime'
       end is_prime
  from t t where n<>1 ) where is_prime='prime';

P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* 
* * 
* * * 
* * * * 
* * * * *

Write a query to print the pattern P(20).

select lpad('* ',level*2,'* ') from dual connect by rownum< 21;

--P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):

* * * * * 
* * * * 
* * * 
* * 
*

Write a query to print the pattern P(20).

select * from (select lpad('* ',level*2,'* ')   t from dual connect by rownum< 21) order by length(t) desc;

--Analytical queries
Query to find out departments those are having maximum number of employees. In other words you can say the customer who placed maximum number of orders.

select deptno from emp group by deptno having count(*)=(select max(count(*)) from emp e group by e.deptno);

--order by analytical clause
select distinct deptno from (select deptno,dense_rank() over (order by cnt desc) rnk from(select deptno,count(*) over (partition by deptno) cnt from emp order by cnt desc)) where rnk=1

--Analytical functions those do not need ordering of the elements.
--sum
select a.*,sum(sal) over (partition by deptno) part_tot_sal from emp a;

--In the absence of partition analytical functions work on entire set of rows.
--count
select a.*,count(ename) over (partition by deptno) part_tot_emp from emp a;

--avg
select a.*,trunc(avg(sal) over (partition by deptno)) part_avg_sal from emp a;

--min
select a.*,min(sal) over (partition by deptno) part_min_sal from emp a;

--max
select a.*,max(sal) over (partition by deptno) part_max_sal from emp a;

--Analytical function those require order by clause. Notice that you don't really need to give input parameter to these functions
--row_number()--I can be used in TOP-N(top 5,last 5 or like 90-100 number of records from rows)
select a.*,row_number() over (order by sal desc NULLS LAST) part_max_sal from emp a;

--Numbering with partition
select a.*,row_number() over (partition by deptno order by sal desc NULLS LAST) part_max_sal from emp a;

--rank()--To define rank-- In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.
--dense_rank()
select a.*,row_number() over (order by sal desc NULLS LAST) row_order,rank() over (order by sal desc NULLS LAST) rank_order ,dense_rank() over (order by sal desc NULLS LAST) dense_rank_order from emp a;

--LEAD,LAG--Note that it needs column on which it needs to be applied
select a.*,row_number() over (order by sal desc NULLS LAST) row_order,rank() over (order by sal desc NULLS LAST) rank_order ,dense_rank() over (order by sal desc NULLS LAST) dense_rank_order,
lead(sal,1,0) over(partition by deptno order by sal desc nulls last) next_sal,lag(sal,1,0) over (partition by deptno order by sal desc nulls last) prev_sal from emp a;

--FIRST_VALUE and LAST_VALUE
select a.*,first_value(sal) over (partition by deptno order by sal desc nulls last) first_sal,last_value(sal) over (partition by deptno order by sal desc nulls last) last_sal from emp a;

-- How each employee's salary compare with the average salary of the first
-- year hires of their department?
--This usage FIRST
SELECT empno, deptno, TO_CHAR(hiredate,'YYYY') HIRE_YR, sal,
TRUNC(
AVG(sal) KEEP (DENSE_RANK FIRST
ORDER BY TO_CHAR(hiredate,'YYYY') )
OVER (PARTITION BY deptno)
     ) AVG_SAL_YR1_HIRE
FROM emp
WHERE deptno IN (20, 10)
ORDER BY deptno, empno, HIRE_YR;


--Find total employee count under manager down the whole hierarchy path
   
select ename, count (*) - 1 cnt
  from (select connect_by_root ename ename
          from emp
        connect by  prior empno=mgr)
group by ename having count(*) > 1
order by cnt desc;

--Bulk collection practice


CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name
       name,
       object_id
FROM   all_objects;

create table bulk_collect_test1 as select * from bulk_collect_test where 1=2;

DECLARE
   CURSOR cur
   IS
      SELECT * FROM bulk_collect_test;

   TYPE t_cur IS TABLE OF cur%rowtype;

   v_cur     t_cur;
   l_limit   NUMBER:=100;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur BULK COLLECT INTO v_cur LIMIT l_limit;

      FORALL i IN 1 .. v_cur.COUNT
         INSERT INTO bulk_collect_test1
              VALUES (v_cur (i).owner, v_cur (i).name, v_cur (i).object_id);
              exit when v_cur.count<l_limit;
   END LOOP;
   close cur;
   --Another way by using direct select. In this way we cannot apply limit
   select * bulk collect into v_cur from bulk_collect_test;
 
 
        FORALL i IN 1 .. v_cur.COUNT
         INSERT INTO bulk_collect_test1
              VALUES v_cur (i);
 
   commit;
END;
/

--SQL%BULK_ROWCOUNT example
--It gives granular information of rows affected by each iteration of forall statement
--above code can be re-written by using SQL%BULK_ROWCOUNT
DECLARE
   TYPE t_array_tab IS TABLE OF VARCHAR2 (30);

   l_array   t_array_tab
                := t_array_tab ('SCOTT',
                                'SYS',
                                'SYSTEM',
                                'DBSNMP',
                                'BANANA');
BEGIN
   FORALL i IN l_array.FIRST .. l_array.LAST
      DELETE FROM bulk_collect_test1
            WHERE owner = l_array (i);


   FOR i IN l_array.FIRST .. l_array.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            'Records deleted for owner: '
         || RPAD(l_array (i),15,' ')
         || ' is: '
         || SQL%BULK_ROWCOUNT (i));
   END LOOP;

   COMMIT;
END;
/

--exception   handling in forall

create table id_tab(id number not null);

DECLARE
   TYPE t_table IS TABLE OF id_tab%ROWTYPE;

   v_tab           t_table := t_table ();
   l_error_count   NUMBER;

   --Create exception and bind it with expected error code so that we can handle a named exception
   dml_error       EXCEPTION;
   PRAGMA EXCEPTION_INIT (dml_error, -24381);
BEGIN
   FOR i IN 1 .. 100
   LOOP
      v_tab.EXTEND;
      v_tab (v_tab.LAST).id := i;
   END LOOP;

   --Creating error records as table does not allow null values
   v_tab (50).id := NULL;
   v_tab (61).id := NULL;

   BEGIN
      FORALL i IN v_tab.FIRST .. v_tab.LAST SAVE EXCEPTIONS
         INSERT INTO id_tab
              VALUES v_tab (i);
   EXCEPTION
      WHEN dml_error
      THEN
         l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
         DBMS_OUTPUT.PUT_LINE ('The number of errors: ' || l_error_count);

         FOR i IN 1 .. l_error_count
         LOOP
            DBMS_OUTPUT.PUT_LINE (
                  'Index'
               || i
               || ' Array index: '
               || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
               || ' Error: '
               || SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
         END LOOP;
   END;
END;
/

---User defined exceptions

--Define exception and use it. Note that by default error code is 1 if no binding is used.
DECLARE
   my_exception   EXCEPTION;
 --  PRAGMA EXCEPTION_INIT (my_exception, -20221);
BEGIN
   RAISE my_exception;
EXCEPTION
   WHEN my_exception
   THEN
      DBMS_OUTPUT.put_line (SQLCODE);
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

--Bind exception with error code
DECLARE
   my_exception   EXCEPTION;
   PRAGMA EXCEPTION_INIT (my_exception, -20221);
BEGIN
   RAISE my_exception;
EXCEPTION
   WHEN my_exception
   THEN
      DBMS_OUTPUT.put_line (SQLCODE);
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/


--give description of error by using RAISE_APPLICATION_ERROR
DECLARE
   my_exception   EXCEPTION;
   PRAGMA EXCEPTION_INIT (my_exception, -20221);
BEGIN
   RAISE_application_error(-20221, 'This is exception description');
EXCEPTION
   WHEN my_exception
   THEN
      DBMS_OUTPUT.put_line (SQLCODE);
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/


--Ref cursor advance options, return results as ref cursor without parameter.
CREATE table t1 (
  id           NUMBER,
  description  VARCHAR2(30),
  created_date DATE
);

INSERT INTO t1 VALUES (1, 'The value 1', SYSDATE-2);
INSERT INTO t1 VALUES (2, 'The value 2', SYSDATE-1);
INSERT INTO t1 VALUES (3, 'The value 3', SYSDATE);
COMMIT;
--Now we can create a stored procedure to return one or more resultsets containing this data.

CREATE OR REPLACE PROCEDURE get_my_results (p_id IN NUMBER DEFAULT NULL)
AS
  l_cursor_1 SYS_REFCURSOR;
  l_cursor_2 SYS_REFCURSOR;
BEGIN
  IF p_id IS NOT NULL THEN
    OPEN l_cursor_1 FOR
      SELECT description, created_date
      FROM   t1
      WHERE  id = p_id;

    DBMS_SQL.RETURN_RESULT(l_cursor_1);
  END IF;

  OPEN l_cursor_2 FOR
    SELECT COUNT(*)
    FROM   t1;

  DBMS_SQL.RETURN_RESULT(l_cursor_2);
END;
/

exec get_my_results();

--Storing collections into database

--Nested table

create type t_nemp as OBJECT(ID NUMBER, NAME VARCHAR2(30));

create type t_nemplist as table of t_nemp;

create table t_emp (id number ,emps t_nemplist) nested table emps store as emp_table;

insert into t_emp values (10,t_nemplist(t_nemp(10,'Anand'),t_nemp(11,'Ved')));

insert into t_emp values (20,t_nemplist(t_nemp(21,'Jitendra'),t_nemp(25,'Ansh')));

select * from t_emp;

--Associative arrays.
create type t_aaemp as OBJECT(ID NUMBER, NAME VARCHAR2(30));
--You notice that below line gives errors and cannot be stored in the database. It proves that associative arrays cannot be stored in database.
create type t_aaemplist as table of t_nemp index by pls_integer;

--varrays

create or replace type t_vemp as OBJECT(ID NUMBER, NAME VARCHAR2(30));

create or replace type t_vemplist as VARRAY(10) OF t_vemp;

create table t_vaemp (id number ,emps t_vemplist);

insert into t_vaemp values (10,t_vemplist(t_vemp(10,'Anand'),t_vemp(11,'Ved')));

insert into t_vaemp values (20,t_vemplist(t_vemp(21,'Jitendra'),t_vemp(25,'Ansh')));

select * from t_vaemp;


--Deadlocks

--Deadlock by bitmap indexes
create table lock1(id number);

create bitmap index lock1_idx on lock1(id);

--From session #1

insert into lock1 values(1);

--from session#2

insert into lock1 values(2);

insert into lock1 values(1);


--Back to session #1
insert into lock1 values(2);

--Bamm ...deadlock detected

--Deadloacks by foreign keys(parent-child hierarchy)

create table lock1(id number);

alter table lock1 add constraint lock1_uk unique(id);

create table lock2(id number);

alter table lock2 add constraint lock2_lock1_fk foreign key(id) references lock1(id);

--From session #1
insert into lock1 values(1);

--from session#2
insert into lock1 values(2);

insert into lock2 values(1);


--Back to session #1--any of below
insert into lock2 values(2);

insert into lock1 values(2);

--Bamn...Deadlock

--Deadlock in autonomous transaction
CREATE OR REPLACE PROCEDURE auto_deadlock_test (id IN NUMBER)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO lock2
        VALUES (id);

   COMMIT;
END;
/

BEGIN
   INSERT INTO lock1
        VALUES (1);

   auto_deadlock_test (1);
END;
/

--Bamn...Deadlock detected


--Mutating trigger error solution...real example
CREATE OR REPLACE PACKAGE state_pkg
AS
   v_is_primary_relation   md_table_relation.is_primary_relation%TYPE;
   v_from_table_name       md_table_relation.from_table_name%TYPE;
   v_to_table_name         md_table_relation.to_table_name%TYPE;
   v_from_schema_name      md_table_relation.from_schema_name%TYPE;
   v_to_schema_name        md_table_relation.to_schema_name%TYPE;
   v_schema_name           MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_SCHEMA%TYPE;
   v_table_name            MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_TABLE%TYPE;
   v_column_name           MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_COLUMN%TYPE;
   v_dimension_name        MD_HIERARCHY.DIMENSION_NAME%type;
   v_hierarchy_name        MD_HIERARCHY.HIERARCHY_NAME%type;
   v_oldispwbenabled       MD_HIERARCHY.IS_PWB_ENABLED%type;
   v_newispwbenabled       MD_HIERARCHY.IS_PWB_ENABLED%type;
END;
/

CREATE OR REPLACE TRIGGER BIU_MD_TABLE_RELATION
   BEFORE INSERT OR UPDATE OF is_primary_relation
   ON md_table_relation compound
   v_is_primary_relation   md_table_relation.is_primary_relation%TYPE;
   v_from_table_name       md_table_relation.from_table_name%TYPE;
   v_to_table_name         md_table_relation.to_table_name%TYPE;
   v_from_schema_name      md_table_relation.from_schema_name%TYPE;
   v_to_schema_name        md_table_relation.to_schema_name%TYPE;
   v_schema_name           MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_SCHEMA%TYPE;
   v_table_name            MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_TABLE%TYPE;
   v_column_name           MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_COLUMN%TYPE;
   v_dimension_name        MD_HIERARCHY.DIMENSION_NAME%type;
   v_hierarchy_name        MD_HIERARCHY.HIERARCHY_NAME%type;
   v_oldispwbenabled       MD_HIERARCHY.IS_PWB_ENABLED%type;
   v_newispwbenabled       MD_HIERARCHY.IS_PWB_ENABLED%type;
      ncount   NUMBER;
   BEFORE EACH ROW
BEGIN
   IF :NEW.is_primary_relation = 1
   THEN
      BEGIN
         state_pkg.v_from_table_name := :NEW.from_table_name;
         state_pkg.v_to_table_name := :NEW.to_table_name;
         state_pkg.v_from_schema_name := :NEW.from_schema_name;
         state_pkg.v_to_schema_name := :NEW.to_schema_name;
      END;
   END IF;
END BEFORE EACH ROW;

AFTER EACH STATEMENT IS
BEGIN
   SELECT COUNT (*)
     INTO ncount
     FROM md_table_relation
    WHERE from_table_name = state_pkg.v_from_table_name
      AND to_table_name = state_pkg.v_to_table_name
      AND from_schema_name = state_pkg.v_from_schema_name
      AND to_schema_name = state_pkg.v_to_schema_name
      AND is_primary_relation = 1;

   IF ncount > 1
   THEN
      raise_application_error
                          (-20000,
                           'Primary relation already exists on the table set'
                          );
   END IF;
END AFTER EACH STATEMENT;
END;
/


select * from emp

select * from dept

select b.deptno,max(sal) from emp a , dept b where a.deptno=b.deptno group by b.deptno

select b.deptno,max(sal) from emp b group by deptno;



select * from (
select b.ENAME,job,max(sal) over(partition by deptno) dept_max,max(sal) over(partition by job) job_max, sal  from emp b
)
where sal=job_max;


select * from (
select b.ENAME,deptno,job,dense_rank() over (partition by deptno order by sal desc) dept_rank,sal  from emp b
)
where dept_rank<6;

SELECT ENAME, deptno, job, dept_rank, sal, hiredate, CASE WHEN sal < LEAD ( sal) OVER ( PARTITION BY deptno ORDER BY sal DESC) THEN 'No' ELSE 'Yes' END seniority_salARY
  FROM (SELECT *
          FROM (SELECT b.ENAME, deptno, hiredate, job, DENSE_RANK () OVER ( PARTITION BY deptno ORDER BY sal DESC) dept_rank, sal
                  FROM emp b))
 WHERE dept_rank < 6 order by deptno, dept_rank ;


set serveroutput on;

declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1 .. 1000
      loop
          open l_rc for
          'select object_name
             from all_objects
            where object_id = ' || i;
          fetch l_rc into l_dummy;
          close l_rc;
          -- dbms_output.put_line(l_dummy);
      end loop;
      dbms_output.put_line
       (round((dbms_utility.get_time-l_start)/100, 2) ||
        ' Seconds...' );
  end;
/


declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1 .. 1000
      loop
          open l_rc for
          'select object_name
             from all_objects
            where object_id = :x'
          using i;
          fetch l_rc into l_dummy;
          close l_rc;
          -- dbms_output.put_line(l_dummy);
      end loop;
      dbms_output.put_line
       (round((dbms_utility.get_time-l_start)/100, 2) ||
        ' Seconds...' );
end;
/

drop table id12;

create table id12 (id number);

insert into id12 values(1);
insert into id12 values(2);
insert into id12 values(3);


select index_name from user_indexes where table_name='ID12';


CREATE OR REPLACE FUNCTION funct
    RETURN number
IS
BEGIN
    DBMS_OUTPUT.put_line ( 'Executed');
    RETURN 10;
END;
/

set serveroutput on ;

select nvl(id,funct) from id12;

select coalesce(id,funct, 1,funct) from id12;


select coalesce('2',1) from dual

--
select nullif(1,1) from dual;

select * from id12

CREATE TABLE t1
AS
    SELECT     LEVEL AS c1, LEVEL AS c2, RPAD ( 'x', 100) AS c3
          FROM DUAL
    CONNECT BY LEVEL <= 10000;

CREATE TABLE t2
AS
    SELECT     LEVEL AS c1, LEVEL AS c2, RPAD ( 'x', 100) AS c3
          FROM DUAL
    CONNECT BY LEVEL <= 10000;

ALTER SESSION ENABLE PARALLEL DML;

EXPLAIN PLAN
    FOR merge /*+ parallel */ into t1
  using (select c1, c2 from t2) t2
  on (t1.c1 = t2.c1)
 when matched then
          update set t1.c2 = t1.c2
 when not matched then
          insert(c1, c2) values(t2.c1, t2.c2);

create table my_objects as

insert into my_objects_temp
select /*+ APPEND */ * from my_objects


create  table my_objects_temp as
select /*+ APPEND */ * from my_objects
union all
select /*+ APPEND */ * from my_objects
union all
select /*+ APPEND */ * from my_objects

insert into my_objects
select /*+ APPEND */ * from MY_OBJECTS_GTT where rownum<100


INSERT INTO MY_OBJECTS_GTT
select * from my_objects

SELECT object_name, DBMS_ROWID.rowid_relative_fno ( ROWID) rfile#, DBMS_ROWID.rowid_block_number ( ROWID) block#,
DBMS_ROWID.rowid_row_number(ROWID) row#, rowid rd FROM my_objects;

select dbms_utility.make_data_block_address(1,48409) from dual;


select * from user_objects where object_name='MY_OBJECTS'

SELECT   objd,file#, block#, DECODE (class#,  1, 'data block',  2, 'sort block',  3, 'save undo block',  4, 'segment header',  5, 'save undo header',  6, 'free list',  7, 'extent map',  8, '1st level bmb',  9, '2nd level bmb',  10, '3rd level bmb',  11, 'bitmap block',  12, 'bitmap index block',  13, 'file header block',  14, 'unused',  15, 'system undo header',  16, 'system undo block',  17, 'undo header',  18, 'undo block') class_type, status
    FROM v$bh
   WHERE objd = 85568
ORDER BY 1, 2, 3;


select hladdr
from x$bh
where dbarfil = 1
and dbablk = 48409;

select * from MY_OBJECTS_GTT

truncate table MY_OBJECTS_GTT

drop table my_objects_temp


truncate table my_objects_temp


select owner,object_name from MY_OBJECTS order by 2 desc


create table id20 (id number unique)

create table id21 (id number, constraint id21_id20_FK foreign key(id) references id20(id))

insert into id20 values(1)


create table big as select * from all_objects

drop table small

create table small as select * from all_objects where rownum<10

create index big_idx on big(object_name)

create index small_idx on small(object_name)

alter table big modify(object_name null)


alter table small modify(object_name null)

update  small set object_name=null where rownum<2

DECLARE
    CURSOR c1
    IS
        SELECT *
          FROM emp FOR UPDATE NOWAIT;

    Z   c1%ROWTYPE;
BEGIN
    OPEN C1;

    FETCH c1 INTO Z;

    COMMIT;

    FETCH c1 INTO Z;
END;
/




create table bitmap_users as select * from all_objects;

select * from bitmap_users

create index bitmap_users_bitidx on bitmap_users(object_id);

create bitmap index bitmap_users_bitidx on bitmap_users(object_id);

alter table bitmap_users add constraint bitmap_users_pk primary key(object_id);


SET TIMING ON;

SELECT COUNT ( *)
  FROM big
 WHERE object_name IN (SELECT object_name FROM small);

SELECT COUNT ( *)
  FROM big s
 WHERE NOT EXISTS
           (SELECT 1
              FROM small b
             WHERE b.object_name = s.object_name)
           
           
select * from emp order by sal desc

select * from (select a.*, rownum rn from emp a order by sal desc) b where b.rn=1  


select a.*, rownum rn from emp a order by sal desc

select sal from (
select rownum num, c.* from (
select sal from emp order by 1 desc) c)d
where num = 1  


drop table id22

create table id22(id number primary key)

create table id23(id number, constraint id23 foreign key(id) references id22(id));


alter table id22 drop primary key

alter table id23 disable constraint  ID23

select * from user_constraints where table_name='ID23'


select * from (select ename,deptno,sal , rank() over (partition by deptno order by sal DESC) rnk from emp) a
where a.rnk=2


select e.deptno ,e.ename, max_sal from emp e,(select deptno,max(sal) max_sal from emp group by deptno) b
where e.deptno=b.deptno and e.sal=b.max_sal


select 'a' from dual
minus
select 1 from dual;


alter table id22 drop primary key cascade


create table teams(name varchar2(30));

insert into teams values('A');
insert into teams values('B');
insert into teams values('C');


begin
for i in (select rownum rn, name from teams order by rn desc)
loop
dbms_output.put_line(i.name);

for r in (select name from(select rownum rn, name from teams order by rn desc) where rn<3    )

loop



end loop;


end loop;
end;
/


select name from(select rownum rn, name from teams order by rn desc) where rn<3  

select TO_CHAR(sysdATE+2,'DAY') FROM



select * from emp a, (select deptno,avg(sal) avg_sal from emp group by deptno) b
where a.deptno=b.deptno and a.sal>b.avg_sal;

Mutating trigger error example: Note that mutating trigger error comes only with after DML triggers.  Mutating error occurs when we are performing some DML(insert,delete,update) operations and we are trying to select (or any DML operation) the affected records from the same trigger. So basically we are trying to select the records in the trigger from the table that owns the trigger. This creates inconsistency issue and Oracle throws a mutating error

create table mutat_trigger(id number)

insert into mutat_trigger values (&id);


create or replace trigger mut_trigger
after insert on mutat_trigger for each row
declare
cnt number;
begin
select count(*)into cnt from mutat_trigger ;
dbms_output.put_line(cnt);
end;
/