Fill variable from query in Oracle Package-CodePudding

I want populate variable from query in oracle package.

In package I'm define:

   g_my_value number;
   g_my_value2 number;
   
   PROCEDURE initialize_par;

In package body:

create or replace PACKAGE BODY my_pkg AS 
    PROCEDURE initialize_par AS
    BEGIN
      Select parameter_value into g_my_value from tbl_parameters where name = 'my_value'; 
      Select parameter_value into g_my_value2 from tbl_parameters where name = 'my_value2'; 
    END initialize_par;

Before use variable in other procedure in this package I need call Exec initialize_par to set value in variable.

Is it good option? Is it possible set value in variable without procedure initialize_par?

CodePudding user response：

You can initialise a package (without a procedure) using a BEGIN/END initialiser block in the procedure body:

CREATE PACKAGE package_name IS
  g_my_value number;
  g_my_value2 number;
END;
/

CREATE PACKAGE BODY package_name
IS
BEGIN
  Select parameter_value
  into   g_my_value
  from   tbl_parameters
  where  name = 'my_value'; 

  Select parameter_value
  into   g_my_value2
  from   tbl_parameters
  where  name = 'my_value2'; 
END;
/

Given the sample parameters:

CREATE TABLE tbl_parameters (name, parameter_value ) AS
  SELECT 'my_value',  42     FROM DUAL UNION ALL
  SELECT 'my_value2',  3.141 FROM DUAL;

then:

BEGIN
  DBMS_OUTPUT.PUT_LINE(package_name.g_my_value);
  DBMS_OUTPUT.PUT_LINE(package_name.g_my_value2);
END;
/

Outputs:

42
3.141

fiddle