can we use default in a variable with function call in plsql?

Question

I have a package, inside i declare g_suppress_all_xlog BOOLEAN := fnc_Suppress() DEFAULT NULL;

FUNCTION fnc_suppress
   RETURN BOOLEAN
IS
   v_ret       BOOLEAN := FALSE;                                  -- assume NO
   v_suppress  VARCHAR2 (3);
BEGIN
   SELECT VALUE
     INTO v_suppress
     FROM server_config
    WHERE name = 'Suppress_All_Xlog';

   IF (v_suppress = 'YES')
   THEN
      v_ret := TRUE;
   END IF;

   RETURN v_ret;
EXCEPTION
   WHEN OTHERS
   THEN
      prc_exception ();
END fnc_suppress;

I get error, how to default null and replace with the function value.

"I get error" - details would help a lot. – William Robertson Apr 10 '20 at 08:54 — William Robertson, Apr 10 '20 at 08:54

Littlefoot · Answer 1 · 2020-04-10T12:54:54.547

All you need is

g_suppress_all_xlog BOOLEAN := fnc_Suppress();

Don't set it to NULL explicitly; if you don't say anything, it will be NULL anyway.

SQL> create or replace package pkg_Test as
  2    procedure p_test;
  3    g_suppress_all_xlog boolean;
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg_Test as
  2    procedure p_test is
  3    begin
  4      dbms_output.put_line('Variable''s value = ' ||
  5        case when g_suppress_all_xlog then 'true'
  6             when not g_suppress_all_xlog then 'false'
  7             else 'null'
  8        end);
  9    end;
 10  end;
 11  /

Package body created.

SQL> exec pkg_test.p_test;
Variable's value = null

PL/SQL procedure successfully completed.

SQL>

fnc_suppress can't be part of the same package in which you declared this function, so - it must be a standalone function or part of another package.

SQL> create or replace package pkg_Test as
  2    function fnc_suppress_in_pkg return boolean;
  3    procedure p_test;
  4    g_suppress_all_xlog boolean := fnc_suppress_in_pkg();
  5  end;
  6  /

Warning: Package created with compilation errors.

SQL> show err
Errors for PACKAGE PKG_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/23     PL/SQL: Declaration ignored
4/23     PLS-00492: variable or constant initialization may not refer to
         functions declared in the same package

SQL>

Finally:

SQL> create or replace function fnc_suppress return boolean
  2    is
  3  begin
  4    return true;
  5  end;
  6  /

Function created.

SQL> create or replace package pkg_Test as
  2    procedure p_test;
  3    g_suppress_all_xlog boolean := fnc_suppress;
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg_Test as
  2    procedure p_test is
  3    begin
  4      dbms_output.put_line(case when g_suppress_all_xlog then 'true' else 'false' end);
  5    end;
  6  end;
  7  /

Package body created.

SQL> exec pkg_test.p_test;
true

PL/SQL procedure successfully completed.

SQL>

Why do you say "Boolean isn't null; it is either true or false. By default, it is false"? A boolean variable's default value is null like any other variable in PL/SQL, isn't it? — Steven Feuerstein, Apr 10 '20 at 12:51
Ah, yes! I was fooled by my own example :( Stupid me. Thank you, @Steven. Fixed. — Littlefoot, Apr 10 '20 at 12:55

score 0 · Accepted Answer · answered Apr 10 '20 at 18:05

The fnc_suppress_in_pkg() can be in the package pkg_test. It just cannot be be used for assignment in the spec, it doesn't exist yet. It seems OP wants to initialize the variable g_suppress_all_xlog initialized before any function/procedure is executed. This can be accomplished with the initialization section of the package body. (Note: My playground has a table config_settings table available so I substituted that for server_config)

create or replace package pkg_test as
    g_suppress_all_xlog boolean;

    procedure prc_exception;
    function fnc_suppress return boolean;
    -- Other procedure/functions declared here. 
end  pkg_test;      

create or replace package body pkg_test as
   procedure prc_exception is
   begin 
       null;
   end prc_exception;

   function fnc_suppress 
      return boolean
   is
      v_suppress  varchar2 (3);
   begin
      select setting
        into v_suppress
        from config_settings
       where name = 'Suppress_All_Xlog';

      return (v_suppress = 'YES');
   exception
      when others
      then
         prc_exception ();
   end fnc_suppress;

   -- Other procedure/functions defined here. 

begin   -- this the package initialization. It executes 1 time when the package is first loaded.
  g_suppress_all_xlog := fnc_suppress;
end pkg_test; 


--  Test with direct reference to g_suppress_all_xlog 
    insert into config_settings(name,setting ) values('Suppress_All_Xlog', 'YES');
    declare
       setting varchar2(30); 
    begin
       if pkg_test.g_suppress_all_xlog 
          then dbms_output.put_line('Xlog being suppressed');
          else dbms_output.put_line('Xlog being written');
       end if;
    end;

IMHO>> However declaring the variable g_suppress_all_xlog is the spec is poor practice. That makes it accessible to any process in the same session for both read (not so bad) and write (bad). A better process would be to declare it in the body so it cannot be accessed directly. I understand it purpose is to having to select each time, so add another function to simply returns its value.

can we use default in a variable with function call in plsql?

2 Answers2