Re: Please check this syntax

From: Josh White <whitegoose_at_inorbit.com>
Date: 2 Apr 2005 20:04:07 -0800
Message-ID: <aafea0a8.0504022004.73cbf408@posting.google.com>

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1112454910.979041_at_yasure>...
> To quote Jim Kennedy, "don't write generic code it will bite you."
>
> That someone may wish to have you write 30 more is called job
> security. Code them properly and do not use native dynamic SQL.
> NDS may be simple ... but you pay a price in debugging and in
> the use of resources.
>
> I can no longer, from my newreader see the code to determine if it
> is syntactically correct but the EXECUTE IMMEDIATE would never have
> generated an error so if the point was to generate an error in a
> specific condition your code wouldn't have accomplished that.
>
> I would suggest reposting your code and, more importantly, describing
> the business rule.
>
> Finally ... you obviously have a computer handy so why don't you also
> have Oracle on it?

Here my code again:

CREATE OR REPLACE TRIGGER iah_iu_Comp
BEFORE INSERT OR UPDATE
ON COMP
REFERENCING NEW AS newRow OLD AS oldRow
FOR EACH ROW DECLARE
Err_Num NUMBER;
Err_Desc VARCHAR2(500);

v_compType VARCHAR2(4);
v_sql VARCHAR2(500);
v_bgtNo VARCHAR2(24);

BEGIN

Get the type of asset SELECT CT.COMPCODE INTO v_compType FROM COMPTYPE CT WHERE CT.COMPTYPE = :newRow.COMPTYPE;
Building Assets must have a budget number IF v_compType = 'BLDG' THEN
- Build SQL to get budget number v_sql := 'SELECT C.BGTNO FROM COMP' || v_compType || ' C WHERE C.COMPKEY = :newRowCompKey';

EXECUTE IMMEDIATE v_sql INTO v_bgtNo USING :newRow.COMPKEY;

      IF v_bgtNo IS NULL THEN
         RAISE_APPLICATION_ERROR(-20100, '##Budget Number field must
be populated##');
      END IF;

   END IF; EXCEPTION
  WHEN OTHERS THEN
    Err_Num := SQLCODE;
    Err_Desc := '##Error (' || Err_Num || ' - ' || SUBSTR(SQLERRM,1,99) || ') Occured in trigger iah_iu_Comp. Please contact support.##';

RAISE_APPLICATION_ERROR (-20100, Err_Desc);

END;

Like I said before the trigger is supposed to: 1. Determine which table the BGTNO is stored in 2. Raise an application error is the BGTNO is null Performance is not an issue due the conditions under which this trigger will fire.

You said this code will never generate an error - can you please explain why? Similar code in T-SQL would work.

I am on the other side of the country to my company's Oracle CDs until the end of next week so unfortunately I'm writing this blind in the meantime.

Thanks again. Received on Sat Apr 02 2005 - 22:04:07 CST