sqlplsqlplsql-package

How to access Package Public variable into SELECT Statement using EXECUTE IMMEDIATE?


CREATE OR REPLACE PACKAGE sample_pkg AS
g_var VARCHAR2(5);
END sample_pkg;

/

CREATE OR REPLACE PACKAGE BODY sample_pkg AS 
BEGIN
g_var := 'hello';
dbms_output.put_line(g_var);
END;

/

SELECT sample_pkg.g_var FROM dual;

error ORA-06553: PLS-221: 'G_VAR' is not a procedure or is undefined


Solution

  • Obviously, you cannot call an internal variable of a package like that, presumably might convert to this one

    CREATE OR REPLACE PACKAGE sample_pkg AS
      g_var VARCHAR2(500) := 'hello';
      FUNCTION f_var( i_var VARCHAR2 DEFAULT g_var ) RETURN VARCHAR2;  
    END sample_pkg;
    /
    CREATE OR REPLACE PACKAGE BODY sample_pkg AS      
      FUNCTION f_var( i_var VARCHAR2 DEFAULT g_var ) RETURN VARCHAR2 IS
      BEGIN  
        RETURN i_var;
      END;  
    END;
    /
    

    and call like this :

    SELECT sample_pkg.f_var FROM dual;
    
    F_VAR
    -----
    hello
    

    or this :

    SET SERVEROUTPUT ON  
    BEGIN
      DBMS_OUTPUT.PUT_LINE(sample_pkg.f_var);
    END; 
    /
    hello
    

    Edit : Indeed, not needed; you can alternatively call the statement by using EXECUTE IMMEDIATE as desired such as

    DECLARE
       result VARCHAR2(89);
       i_val  VARCHAR2(89):='Hello World!';
    BEGIN
       EXECUTE IMMEDIATE 'SELECT sample_pkg_.f_var(:prm1) FROM dual' INTO result USING i_val; 
      DBMS_OUTPUT.PUT_LINE(result);
    END;
    /