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

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

CodePudding user response：

A package body has functions and procedures, not standalone code A procedure cannot be invoked in a select statement, that needs to be done in a pl/sql block. A package variable can be initialised when it is declared in the package specification or in a subprogram. Putting it all together in an example:

koen>create or replace PACKAGE sample_pkg AS
  2    g_var VARCHAR2(100) := 'initial value'; 
  3    PROCEDURE init;
  4    FUNCTION ret_g_var RETURN VARCHAR2;
  5  END sample_pkg;
  6* /

Package SAMPLE_PKG compiled

koen>create or replace PACKAGE BODY sample_pkg AS 
  2   
  3    PROCEDURE init
  4    IS
  5    BEGIN
  6      g_var := 'Hello !';
  7    END;
  8    FUNCTION ret_g_var return VARCHAR2
  9    IS
 10    BEGIN
 11      RETURN g_var;
 12    END;
 13  
 14  END;
 15* /

Package Body SAMPLE_PKG compiled

koen>set serveroutput on size 999999
koen>select sample_pkg.ret_g_var from dual;

       RET_G_VAR 
________________ 
initial value    

koen>begin
  2    dbms_output.put_line(sample_pkg.g_var);
  3    sample_pkg.init;
  4    dbms_output.put_line(sample_pkg.g_var);
  5  end;
  6* /
initial value
Hello !


PL/SQL procedure successfully completed.

koen>select sample_pkg.ret_g_var from dual;

   RET_G_VAR 
____________ 
Hello !      

koen>

CodePudding user response：

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