Which type of binding does PL/SQL use?

Question

I came across the following question.

PL/SQL uses which of the following

• (A) No Binding
• (B) Early Binding
• (C) Late Binding
• (D) Deferred Binding

But could not find any satisfying answers.

Can anybody give an explanation for this?

Answer 1

(E) All of the Above

This is a ridiculous multiple choice question. The terms early, late, and deferred binding are ambiguous. And PL/SQL can be run in many different ways, including in SQL.

Here are my (arguably incorrect) definitions of the choices:

1. No binding - Variables have no types.
2. Early Binding - Variable types are fixed at compile time.
3. Late Binding - Variable types are flexible and may be set at run time.
4. Deferred Binding - Multiple variables types are defined at compile time, but only one of them is chosen at run time.

Now we have to match those choices to different PL/SQL contexts: static SQL and PL/SQL, empty anonymous blocks, remote procedures, dynamic SQL and PL/SQL, adaptive cursor sharing, FILTER operations, object-oriented PL/SQL, ANY* types, and I'm probably missing some more.

(A) No binding

An empty anonymous block doesn't have any variables so nothing is bound. I'm not sure if this really fits the definition of no binding, it seems kind of like an edge case. In some languages there is always an object and something must always be bound, but not in PL/SQL.

(B) Early Binding

Regular SQL and PL/SQL use early binding - variables are given a type and they must stick to it. Type mismatches will either throw a compiler error or require implicit conversion.

Remote procedure calls with REMOTE_DEPENDENCIES_MODE set to "TIMESTAMP" is arguably early binding. The timestamp is set at compile time when everything is checked. It is still checked at run time, but it's a simple and fast check.

(C) Late Binding

Dynamic SQL and PL/SQL use late binding because the code is not even compiled until run time. This applies both to DBMS_SQL and execute immediate.

Object-Oriented PL/SQL uses late binding. The type is set at compile time but a different subtype may be used at run time.

ANYTYPE, ANYDATA, and ANYDATASET also use late binding since they can be created at run time, or retrieved and executed at run time.

Remote procedure calls with REMOTE_DEPENDENCIES_MODE set to "SIGNATURE" is arguably late binding. The signature is checked at both compile time and run time, and allows for a tiny bit of flexibility in types.

(D) Deferred Binding

Some Oracle SQL features create multiple code paths but only execute one of them. Adaptive cursor sharing and FILTER operations will create multiple ways to run the same SQL statement, and the appropriate version is chosen at run time.

---

Invoker's Rights and Definer's Rights

Invoker's rights and definer's rights also complicate this question. But I think that ultimately they don't make a difference, and that both of them are still early binding. The compiler still decides the type at compile time. Although you can use invoker's rights to stealthily change a type at run time it will only generate an error because it doesn't match the expected type.

For example, let's say there are two schemas that have the same table and column names, but different types:

create table user1.test_table(a number);
insert into suer1.test_table values(1);

create table user2.test_table(a date);
insert into user2.test_table values(sysdate);

If you create this function on USER1 it looks like the type of V_VALUE is dynamic and can change with the user.

create or replace function user1.test_function return varchar2 authid current_user is
    v_value test_table.a%type;
begin
    select a into v_value from test_table;
    return to_char(v_value);
end;
/

The code compiles using the types from USER1 and works fine when run by USER1. However, when USER2 runs it this error is generated: ORA-00932: inconsistent datatypes: expected NUMBER got DATE.

This leads me to believe that invoker's and definer's rights do not affect the binding. They both use early binding in static SQL and PL/SQL.

Answer 2

You can find the answer in a very very old documentation related to Oracle 8:
https://docs.oracle.com/cd/A58617_01/server.804/a58236/05_ora.htm

Efficiency versus Flexibility

Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle schema objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.

How a database language implements binding affects runtime efficiency and flexibility. Binding at compile time, called static or early binding, increases efficiency because the definitions of schema objects are looked up then, not at run time. On the other hand, binding at run time, called dynamic or late binding, increases flexibility because the definitions of schema objects can remain unknown until then.

Designed primarily for high-speed transaction processing, PL/SQL increases efficiency by bundling SQL statements and avoiding runtime compilation. Unlike SQL, which is compiled and executed statement-by-statement at run time (late binding), PL/SQL is processed into machine-readable p-code at compile time (early binding). At run time, the PL/SQL engine simply executes the p-code.

But Oracle in subsequent versions removed the whole chapter Interaction with Oracle from the documentation.

So, according to the above the answer is: (B) Early Binding - for sure for Oracle version 8, and perhaps in futher versions.