Avoiding program name confusion schema-level and packaged

Question:
I have a package named pck_events_1 which has a function named fn_1. I have another schema-level (aka, "stand-alone") function, also named fn_1, defined in the same schema. How can I call the schema-level function from a subprogram inside the package?
Answer:
That's an awkward situation, isn't it?
You have three possible solutions:
• Change the name of one of the two programs. From a best-practices perspective, this would be my first recommendation. If, for example, you have naming conventions for packages and schema-level program units, you can likely avoid this kind of name collision. I will assume, however, that you are not able to change the name, so you can choose one of the remaining solutions.
• Prefix the call to the schema-level function with the name of the schema. This will distinguish it from the call to the packaged program. One downside to this approach is that you will have to hard-code the schema name in your code. This is generally something developers want to avoid, because schema names can change, depending on how and where the application is installed.
• Use dynamic SQL to call the schema-level function. The dynamic block executes outside the scope of the package, so an unqualified reference to the function will be directed to the schema-level function. With this approach, you avoid hard-coding the schema name. You will not, however, be able to bind variables of arguments with non-SQL datatypes, such as Boolean or a record type, using EXECUTE IMMEDIATE.
These last two options are demonstrated in the name_confusion.sql script shown in Listing 1.
Code Listing 1: name_confusion.sql script
-- Saved in name_confusion.sql
-- Must run in SCOTT schema

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE this_one (n in number)
IS
BEGIN
DBMS_OUTPUT.put_line ( 'schema-level' );
END this_one;
/

CREATE OR REPLACE PACKAGE same_name
IS
PROCEDURE this_one (n in number);

PROCEDURE that_one;
END same_name;
/

CREATE OR REPLACE PACKAGE BODY same_name
IS
PROCEDURE this_one (n in number)
IS
BEGIN
DBMS_OUTPUT.put_line ( 'package-level' );
END this_one;

PROCEDURE that_one
IS
BEGIN
this_one (10);

scott.this_one (10);

EXECUTE IMMEDIATE 'BEGIN same_name.this_one (:val); END;'
USING 10;

EXECUTE IMMEDIATE 'BEGIN this_one (:val); END;'
USING 10;
END that_one;
END same_name;
/

BEGIN
same_name.that_one;
END;
/
And now I run the script in Listing 1 in SQL*Plus and see the following results:
SQL> @name_confusion

Procedure created.
Package created.
Package body created.

package-level
schema-level
package-level
schema-level
In this example, both of the this_one programs (schema-level and packaged) have the same parameter list, so the code compiles regardless of which program is being invoked. Suppose the parameter lists are different, however, as shown in Listing 2. Here my schema-level procedure takes a single Boolean argument. In this case, as you can see in the output in Listing 2, my package will not even compile unless I qualify the program name with its schema.
Code Listing 2: name_confusion2.sql
-- Saved in name_confusion2.sql

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE this_one (b IN BOOLEAN)
IS
BEGIN
DBMS_OUTPUT.put_line ( 'schema-level' );
END this_one;
/

CREATE OR REPLACE PACKAGE same_name
IS
PROCEDURE this_one (n in number);

PROCEDURE that_one;
END same_name;
/

CREATE OR REPLACE PACKAGE BODY same_name
IS
PROCEDURE this_one (n in number)
IS
BEGIN
DBMS_OUTPUT.put_line ( 'package-level' );
END this_one;

PROCEDURE that_one
IS
BEGIN
this_one ('155');
this_one (TRUE);
END that_one;
END same_name;
/

SQL> @name_confusion2

Procedure created.

Package created.

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY SAME_NAME:

LINE/COL ERROR
---------- ------------------------------------------------------------------
13/7 PL/SQL: Statement ignored
13/7 PLS-00306: wrong number or types of arguments in call to 'THIS_ONE'
I end with this example to drive home the point that the two this_one procedures are not in any way overloaded. They are defined in different scopes, so simply having different parameter lists will not be enough to tell the PL/SQL compiler which program to use.