the problem is that when other users execute other procedure they will have to wait until the executing procedure or function finishes.This is FALSE.
Any number of users can run call the same function/procedure at the same time.
You can see this by creating a procedure that outputs the current time in 1s intervals 10 times:
create or replace procedure p (
sess varchar2
) as
begin
for i in 1 .. 10 loop
dbms_output.put_line (
sess || ' ' || to_char ( sysdate, 'HH24:MI:SS ' )
) ;
dbms_lock.sleep ( 1 ) ;
end loop;
end p;
/
Then run it in two separate sessions. Session 1:
SQL> exec p('S1');
S1 03:47:44
S1 03:47:45
S1 03:47:46
S1 03:47:47
S1 03:47:48
S1 03:47:49
S1 03:47:50
S1 03:47:51
S1 03:47:52
S1 03:47:53
And session 2:
SQL> exec p('S2');
S2 03:47:45
S2 03:47:46
S2 03:47:47
S2 03:47:48
S2 03:47:49
S2 03:47:50
S2 03:47:51
S2 03:47:52
S2 03:47:53
S2 03:47:54
Notice how the times are overlapping? So they both execute it at the same time.
The exception is if you do something blocking inside the PL/SQL. Such as updating/deleting the same rows.
But if you're just running queries or procedural code, they can run at the same time.