Re: How to check numerical dependencies in PL/SQL?

From: Peter Schneider <pschneider.ctj_at_metronet.de>
Date: 1997/08/21
Message-ID: <MPG.e65c688ce9bcfdd98968a_at_pop-news.metronet.de>#1/1


Jens M. Felderhoff <jmf_at_informatik.uni-koblenz.de> wrote:

> I have to check numerical dependencies (ND) in PL/SQL.

[SNIP]
>
> Here is a pseudo-PL/SQL fragment from a trigger that tries to check
> the constraint:
>
> [...]
> SELECT COUNT(*)
> INTO valcount
> FROM SELECT DISTINCT a2,a3
> FROM rel
> WHERE a1 = :new.a1;
> IF valcount NOT BETWEEN 2 AND 3
> THEN raise_application_error(-20001, "ND violation");
> END IF;
> [...]
>
> However, it is only possible to specify a table reference in the
> SELECT-INTO statement instead of a subquery.
>
> Does anybody have an idea how such a constraint check could be
> implemented?
>
> N.B.: The usage of cursors or views should be avoided if possible.
>

[SNIP] Hi Jens,

using DBMS_SQL does the trick:

DECLARE
   stmt VARCHAR2(2000);

   hndl INTEGER;
   exec INTEGER;
   cnt  INTEGER;

BEGIN    stmt := 'SELECT count(*) cnt ' ||

           'FROM ( SELECT DISTINCT job, deptno FROM emp )';

   hndl := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE(hndl, stmt, DBMS_SQL.V7);    DBMS_SQL.DEFINE_COLUMN(hndl, 1, cnt);    exec := DBMS_SQL.EXECUTE_AND_FETCH(hndl);    DBMS_SQL.COLUMN_VALUE(hndl, 1, cnt);
   DBMS_SQL.CLOSE_CURSOR(hndl);

   DBMS_OUTPUT.PUT_LINE('Count: ' || to_char(cnt));

   EXCEPTION WHEN others THEN

      IF DBMS_SQL.IS_OPEN(hndl) THEN
         DBMS_SQL.CLOSE_CURSOR(hndl);
      END IF;
      RAISE;

END; Although it seems to be too much code for such a problem, it's the only way I could figure out to overcome that SELECT .. INTO restriction on subqueries. And, putting it into a package and using some parameters even makes it reusable for some similar purposes.

BTW, I interpreted your preference not to use cursors such that you just didn't want to loop through your result set and getting cursor%ROWCOUNT afterwards, which really would be a waste of server resources. Using dynamic SQL in the way stated above is as quick as your original statement (w/o select into) would have been.

Hope this helps,

Peter

-- 
Peter Schneider
pschneider.ctj_at_metronet.de
Received on Thu Aug 21 1997 - 00:00:00 CEST

Original text of this message