How does the PL/SQL context work?

Question

i have some doubts about the PL/SQL context, are there:

1. the PL/SQL context is static ?
2. the PL/SQL context is sync ?
3. if a procedure was called two times at the same time, the first one takes 20 seconds to complete.. will the second one wait this 20 seconds to start its execution ?

thanks.

Answer 1

Each database session that references a package has an independent instance of the package. All package state (i.e. global package variables) is distinct to each session.

There is no synchronization between multiple sessions invoking the same package procedures or functions -- except what might occur as a natural side effect of the database operations that they perform and the locking required to achieve them.

Answer 2

Your questions are a bit difficult to understand. I'll try to answer it any way.

1. PL/SQL is a procedural language. So I wouldn't talk about instances. The code only exists once, the package variables exist once per session and the local variables exist once per call of the procedure or function. You cannot access aonther session's variables or memory.

2. All calls to PL/SQL code are synchronuous. There are no concepts like multi-threading or shared memory (in PL/SQL). Note however, that Oracle is a multi-user system. So other sessions might be changing data in the database at the same time. And many of these changes a temporarily hidden from you due to transaction isloation. But it doesn't influence any variables in memory.

3. Procedures never block unless they try to change the same database row as another session. But this isn't related to any piece of PL/SQL code and can also be experienced with an SQL command run from a different tool.