Re: Strange PL/SQL error

tlach_at_PROBLEM_WITH_INEWS_GATEWAY_FILE (Thierry A. Lach) writes:
>Paul Mickel (mickel_at_sulaco.OES.ORST.EDU) wrote:
>> My co-worker and I found an interesting PL/SQL error not too long ago
>> in one of our triggers. The error is:
>> "PL/SQL Check #20112: Contact your Oracle Representative ... "
>
>I saw something similar when I attempted to use a system variable for
>the default value of a parameter similar to
>
> procedure check_yn(a_field := :SYSTEM.TRIGGER_FIELD) is
> begin
> ...
> ... fill in the code yerself...
> ...
> end;

On this procedure call there seems to be a type conflict if a_field is of type VARCHAR2. I came across this situation and and I had to do the following.

        procedure check_yn(a_field VARCHAR2 := NULL);
           tmp_a_field VARCHAR2(80)  -- or whatever the max lengh is.
        begin
          tmp_a_field := nvl(a_field, :SYSTEM.TRIGGER_FIELD);
          ...
          ... fill in the code yerself...
          ...
        end;

It is not very pretty but it works. For a while we had a version of SQL*Forms 3.0 that did not catch the default value problem at compile time. The form would suddenly throw bat to a system prompt or it produce a very pretty two or three screen stack dump. It was an extremely difficult problem to solve. I hope this helps.

Later,
Steven Whatley Received on Thu May 19 1994 - 20:33:17 CEST