Re: How to check numerical dependencies in PL/SQL?
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.deReceived on Thu Aug 21 1997 - 00:00:00 CEST