Re: Bind Variable in Procedure

In message <42d0a9af$1_2_at_rain.i-cable.com>, lee@?.?.invalid writes
>Hi,
>
>A simple silly question from a beginner - I cannot find related info
>explicitly explained in the textbook.
>
>Is iSQL*PLUS bind variable limited to anonymous block? My personal
>interpretation is that named block could be called by any host environments,
>and we cannot guarantee the availability of host variables in these
>environments, so it is not allowed in named block.
>
>This anonymous block is ok:
>/* in sqlplus issues: variable var1 number
>* /
>DECLARE
>BEGIN
> dbms_output.put_line('value is '|| :var1);
>END;
>
>If I turn it into a function:
>
>CREATE OR REPLACE FUNCTION somefunc RETURN number
>BEGIN
> dbms_output.put_line('value is '|| :var1);
> return 1;
>END;
>
>It produces error complaining :var is invalid.
>
>Thx for answering such a simple question.
>
>

Named blocks use 'normal' variables like any other programming language.

CREATE OR REPLACE FUNCTION somefunc(var1 in number) RETURN number BEGIN
    dbms_output.put_line('value is '|| var1);     return 1;
END; TO call this from sql*sql plus you need to do something like this

variable var1 number
variable result1 number
begin
  :number:=somefunc(:var1);
end;

To call it from another PL/SQL block

declare
 var2 number:=2;
 result1 number;
 begin
   result1:=somefunc(var2);
 end;

(All code untested)

-- 
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>