Re: Using variables in SQL*PLUS.

In article <78kj5e$1aq_at_news3.euro.net>, "Filip Vantomme" <filip.vantomme_at_euronet.be> writes:
> Is there anyway to use variables in SQL-views.
>
> How should they be declared ?
> How are they to be filled ?
>
> Thanks in advance.
>
> Please also mail replies to filip.vantomme_at_euronet.be.
>
>
Filip,
You may use packaged functions something like this:

CREATE OR REPLACE PACKAGE p
IS

   PROCEDURE set_deptno (deptno_in IN NUMBER);

   FUNCTION get_deptno RETURN NUMBER;
   PRAGMA RESTRICT_REFERENCES (get_deptno, WNDS, WNPS); END p;
/
CREATE OR REPLACE PACKAGE BODY p
IS

   deptno_pv NUMBER;

   PROCEDURE set_deptno (deptno_in IN NUMBER)    IS BEGIN deptno_pv := deptno_in; END;

   FUNCTION get_deptno RETURN NUMBER
   IS BEGIN RETURN (deptno_pv); END;
END p;
/

CREATE OR REPLACE VIEW emp_by_dept AS

   SELECT * FROM emp WHERE deptno = p.get_deptno /

EXEC p.set_deptno(10);

SELECT ename, deptno FROM emp_by_dept
/

EXEC p.set_deptno(20);
SELECT ename, deptno FROM emp_by_dept
/