Updated on 2025-09-04 GMT+08:00

Package Variables

A package variable is a global variable defined in a package. Its lifecycle covers the entire database session. Improper use may cause the following problems:

  • If a variable is completely transparent to users who have the package access permission, the variable may be shared among multiple stored procedures and modified unexpectedly.
  • The lifecycle of a package variable is at the session level. Improper operations may cause residual data and affect other stored procedures.
  • Caching a large number of package variables in a session may consume a substantial amount of memory.

Therefore, you are advised to use package variables with caution and ensure that their access and lifecycle are properly managed.

-- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE db_test DBCOMPATIBILITY 'ORA';

-- Switch to the ORA-compatible database.
gaussdb=# \c db_test 
db_test=# CREATE SCHEMA best_practices_for_procedure;
CREATE SCHEMA

db_test=# CREATE OR REPLACE PACKAGE best_practices_for_procedure.pkg1 AS
    id int;
    name varchar2(20);
    arg int;
    procedure p1();
END pkg1;
/
CREATE PACKAGE

db_test=# CREATE OR REPLACE PACKAGE BODY best_practices_for_procedure.pkg1 AS
    procedure p1() as
    BEGIN
        id := 1;
        name := 'huawei';
    arg := 20;
    END;
END pkg1;
/
CREATE PACKAGE BODY

-- Create a stored procedure and modify package variables.
db_test=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.pro1 () AS
BEGIN
    best_practices_for_procedure.pkg1.id := 2;
    best_practices_for_procedure.pkg1.name := 'gaussdb';
    best_practices_for_procedure.pkg1.arg := 18;
END;
/
CREATE PROCEDURE

-- Change the value of a package variable.
db_test=# CALL best_practices_for_procedure.pro1();
 pro1
------

(1 row)

-- In practice, it is found that the parameters have been modified.
db_test=# DECLARE
BEGIN
    dbe_output.print_line('id = ' || best_practices_for_procedure.pkg1.id || ' name = ' || best_practices_for_procedure.pkg1.name || ' arg = ' || best_practices_for_procedure.pkg1.arg);
    best_practices_for_procedure.pkg1.p1();
    dbe_output.print_line('id = ' || best_practices_for_procedure.pkg1.id || ' name = ' || best_practices_for_procedure.pkg1.name || ' arg = ' || best_practices_for_procedure.pkg1.arg);
END;
/
id = 2 name = gaussdb arg = 18
id = 1 name = huawei arg = 20
ANONYMOUS BLOCK EXECUTE

db_test=# DROP SCHEMA best_practices_for_procedure CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to package 16443
drop cascades to function best_practices_for_procedure.p1()
drop cascades to function best_practices_for_procedure.pro1()
DROP SCHEMA

db_test=#  \c postgres
gaussdb=# DROP DATABASE db_test;