Re: Question on Package Variables
From: shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 24 Jan 2008 18:53:49 +0100
Message-ID: <4798d0b4$0$85779$e4fe514c@news.xs4all.nl>
>> 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!!!
Date: Thu, 24 Jan 2008 18:53:49 +0100
Message-ID: <4798d0b4$0$85779$e4fe514c@news.xs4all.nl>
"Galen Boyer" <galen_boyer_at_yahoo.com> schreef in bericht news:uzluvyqp0.fsf_at_rcn.com...
> On Thu, 24 Jan 2008, sybrandb_at_gmail.com wrote:
>> 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.
> > I understand that. Wasn't asking that. >
>> 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.
> > Thanks. Wasn't asking that and wasn't asking for a patronizing response. >
>> Secondly:
>> You can't access PL/sql variables outside pl/sql even if they are in a
>> package spec.
> > I understand that. Was just wondering if there was a why. >
>> You'll need a (guess what) *function* to return them.
> > I understand that. Wasn't asking that nor for a patronizing response. >
>> 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;
>> /
> > Yep, I understand that. Wasn't asking. >
>> 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!!!
> > Not sure. > > -- > Galen Boyer
Galen, I agree. But: calling a constant from a package in a select statement in SQL is interpreted as a function / procedure call. So the (only?) way is to create a function returning this value. What I have seen is a construction like a constant _c_whatever in a package BODY and a function c_whatever returning _c_whatever. It can easily be generated...
Shakespeare Received on Thu Jan 24 2008 - 11:53:49 CST