Re: Question on Package Variables

On Jan 24, 2:37 pm, Galen Boyer <galen_bo..._at_yahoo.com> wrote:
> CREATE OR REPLACE PACKAGE p_tst
> IS
>        v_var int := 1;
> END;
> /
>
> SQLPLUS> select p_tst.v_var from dual;
>
> The above fails with an error of p_tst not a function (or something to
> that affect).
>
> Is this based on the variable only being available in the PLSQL engine
> and not available in SQL or something else?
>
> --
> Galen Boyer

First of all : You can't call procedures in a select. Functions are thingies that return something, until Bill Gates misused the procedure concept in T-SQL to return a cursor as if it where a function result.
Procedures don't return anything except for OUT parameters. Programming Fundamentals 101, lesson 1.
Secondly:
You can't access PL/sql variables outside pl/sql even if they are in a package spec. You'll need a (guess what) *function* to return them. So the proper method to use PL/sql flags (or anything similar) is create or replace package util is
flag number;

procedure set_flag(i in number);
function get_flag returns number;

end;
/

create or replace package body util is

procedure set_flag(i in number) is
begin
flag := i;
end;

function get_flag returns number is
begin
return i;
end;

end;
/

Would that be in any Steven Feuerstein book. I bet it is!!!

--
Sybrand Bakker
Senior Oracle DBA