How to define global variable in PL/SQL in Oracle?
Create new package with your variable in package specification, like this:
CREATE PACKAGE my_public_package IS
my_var Number;
END;
Now you can access variable in any code with access to my_public_package
...
my_public_package.my_var := 10;
...
How can I define a global variable in PL/SQL which will be available in all functions / procedures / packages?
You could use a Global Application Context variable.
An application context is a set of name-value pairs that Oracle Database stores in memory. The application context has a label called a namespace, for example, empno_ctx for an application context that retrieves employee IDs. Inside the context are the name-value pairs (an associative array): the name points to a location in memory that holds the value. An application can use the application context to access session information about a user, such as the user ID or other user-specific information, or a client ID, and then securely pass this data to the database. You can then use this information to either permit or prevent the user from accessing data through the application. You can use application contexts to authenticate both database and nondatabase users.
If you want that the variable value should be same across all the sessions for any PL/SQL object accessing the variable, then use a database table to store the value.
For example, as T.Kyte suggests here
CREATE TABLE global_value(x INT);
INSERT INTO global_value VALUES (0);
COMMIT;
CREATE OR replace PACKAGE get_global
AS
FUNCTION Val
RETURN NUMBER;
PROCEDURE set_val (
p_x IN NUMBER );
END;
/
CREATE OR replace PACKAGE BODY get_global
AS
FUNCTION Val
RETURN NUMBER
AS
l_x NUMBER;
BEGIN
SELECT x
INTO l_x
FROM global_value;
RETURN l_x;
END;
PROCEDURE Set_val(p_x IN NUMBER)
AS
PRAGMA autonomous_transaction;
BEGIN
UPDATE global_value
SET x = p_x;
COMMIT;
END;
END;
/