Re: Help needed with strange constraint problem

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/01/23
Message-ID: <5c8tlg$rc_at_shadow.CSUFresno.EDU>#1/1

In article <ADm1VvoON4_at_dwayne.FreeNet.Kiev.UA>, Dwayne K. King <dwayne_at_dwayne.FreeNet.Kiev.UA> wrote:
> I am having a very weird problem with a stored procedure. The
> procedure returns a BOOLEAN. Running it from SQL*Plus (many times)
> generates the expected behaviour. The procedure is fairly complex
> and uses dynamic sql to enable and disable constraints as it goes.
>
> The problem is this. Under Plus, all works perfectly. However,
> when I call it from a form (using exactly the same syntax), it craps
> out and generates a "form raised unhandled constraint ORA-2298 ..."
> message. This is the famous Unable to ENABLE constraint exception.
>
> Two questions.
> 1) how can I see _which_ constraint
> 2) why is this even happening? Three people have looked at this
> with me and walk away shrugging. Same parameters, same data, one
> way works, one way fails.

I sure can't answer #2, but you can add this code to your stored procedure to see the message better. (I think the full error message should return the constraint name, so this should pass it back to you.)

    BEGIN

      RTRN_MSG := NULL;
      <do everything here>
    EXCEPTION WHEN OTHERS THEN
      RTRN_MSG := SUBSTR('<your procedure identifier>: '||SQLERRM,1,100);

    END; When you call your procedure from a form, include RTRN_MSG as an in/out variable, Varchar2(100). Call your procedure like this:

  YOUR_PROCEDURE(variables....,RTRN_MSG);   IF RTRN_MSG IS NOT NULL THEN
    MESSAGE(RTRN_MSG);
    MESSAGE(' ',NO_ACKNOWLEDGE); <--Puts msg into an alert box     RAISE FORM_TRIGGER_FAILURE;
  END IF; HTH
Steve Cosner Received on Thu Jan 23 1997 - 00:00:00 CET