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
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;
/