(PLSQL) Oracle is running​ an old version of the executed procedure

I had a problem with Oracle. I have solved it already (I am going to show how) but I would appreciate any explanation about the reason for the malfunction.

THE CONTEXT

I've declared a package of procedures to be executed in the following way.

Script to declare the procedures (package_example_spec.sql):

    CREATE OR REPLACE PACKAGE package_example AS

        PROCEDURE example1;        
        PROCEDURE example2;

    END package_example;
    /

Script to implement the procedures (package_example_body.sql):

    CREATE OR REPLACE PACKAGE BODY package_example IS

            PROCEDURE example1 AS 
            ...
            END example1;

            PROCEDURE example2 AS
            ...
            END example2;

    END package_example;
    /

Script to execute the procedures:

    @@package_example_spec.sql
    @@package_example_body.sql
    BEGIN
        example1;
    END;
    /
    BEGIN
        example2;
    END;
    /

THE PROBLEM

At first, everything was running smoothly.

Then, after maybe a day, I started noticing that sometimes Oracle was not running the new edits of procedure implementations, but somehow, even if the old version of the procedure was not present anymore, it was running that old version (I noticed it thanks to DBMS_OUTPUT.PUT_LINE statements). In other words, I was making changes and recompiling to the script, but Oracle didn't see the new changes I compiled, it was seeing an old compilation of those procedures. Still, Oracle was going to recover "automatically" from this problem, I just needed to wait, and sometimes I just solved it by disconnecting and reconnecting to the schema.

But, after a few days, this problem got exasperating: nothing I could do was to have Oracle execute the current version of my procedures, and the last time (before I solved it) the "lag" lasted for a few hours.

NON-FUNCTIONING ATTEMPTS

Something relevant I did was:

• Compiling the new procedures every time I change them (I know this is basic, but someone might wonder if I did it)
• Tried cleaning cache: I ran the command alter system flush shared_pool;
• Killing every Oracle process running (I did it through Task Manager) and restarting the system
• Though I was working through SQL Plus, I also tested the same scripts on sqldeveloper. I also manually changed and recompiled the procedure inside the proper package body option on the left menu (inside sqldeveloper).
• Trying disconnecting and reconnecting with the schema and restarting the application, even multiple times (both SQL Plus and sqldeveloper)

THE SOLUTION

I accidentally solved it by starting to call the procedures in a different way, I removed the "@@" and did this instead:

BEGIN
    package_example.example1;
END;
/
BEGIN
    package_example.example2;
END;
/

Now the problem is not occurring anymore.

QUESTIONS

Do any of you know why it did happen? Was there a way of solving the problem while keeping the "@@" way of executing the procedures?

Thank you.

why it did happen?

The code:

BEGIN
  example1;
END;
/
BEGIN
  example2;
END;
/

Is NOT calling the procedures from the package; it is calling standalone procedures that happen to have the same name as the procedures in the package.

If you want to call the packages from the procedure then you need to qualify the procedure name with the package name beforehand so you DO want to use:

BEGIN
  package_example.example1;
  package_example.example2;
END;
/

---

For example, if you have the code:

CREATE PROCEDURE example1 IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('A');
END;
/

CREATE PACKAGE package_example IS
  PROCEDURE example1;
END;
/

CREATE PACKAGE BODY package_example IS
  PROCEDURE example1 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('B');
  END;
END;
/

Then you can query the data dictionary:

SELECT object_name, object_type
FROM   USER_OBJECTS
WHERE  OBJECT_NAME IN ('EXAMPLE1', 'PACKAGE_EXAMPLE');

and see that you have 3 objects in the schema:

A standalone procedure, the package and the package body.

When you use:

BEGIN
  DBMS_OUTPUT.ENABLE();
  example1;
END;
/

Then the output is:

A

Which corresponds to the standalone procedure.

If you use:

BEGIN
  DBMS_OUTPUT.ENABLE();
  package_example.example1;
END;
/

Then the output is:

B

Which corresponds to the package procedure.

---

If you should not have a standalone procedure (i.e. if you created it initially to test the procedure before implementing it in the package) then you can drop the procedure:

DROP PROCEDURE example1;

and you should find that:

BEGIN
  DBMS_OUTPUT.ENABLE();
  example1;
END;
/

Starts raising an exception but the other code using the package identifier will still work.

---

Was there a way of solving the problem while keeping the "@@" way of executing the procedures?

@@ is an SQL*Plus command to run a script.

What you are doing is recreating the package specification with the first script and then recreating the package body with the second script and then executing the standalone procedure with the BEGIN/END block (as already said, you are not executing the procedures from the package).

You only need to create the package specification and body once - you do not need to do it every time that you try to run the procedure.

So the answer of "how to keep the @@ way of executing the procedures" is that you do NOT want to keep the @@ script calls.

The first time you want to create the package you can run the script:

@@package_example_spec.sql
@@package_example_body.sql

That will call those scripts and create the package in the database and you do not need to run them again.

When you want to call the package procedures, just use:

BEGIN
  package_example.example1;
  package_example.example2;
END;
/

fiddle