you just need use the select into , the cursor would cost more time. On Sep 16, 10:43 pm, Chris <christopherc...@hotmail.com> wrote: > CREATE OR REPLACE TRIGGER check_bom_exists_trig > BEFORE INSERT OR UPDATE ON XXMEL_SPARES_BOM_LOOKUP > FOR EACH ROW > DECLARE > -- bom must exist in msib before it can be inserted > new_target VARCHAR2(50); > new_source VARCHAR2(50); > > v_target_exists VARCHAR2(1) := 'N'; > v_source_exists VARCHAR2(1) := 'N'; > v_error_text VARCHAR2(1000); > > invalid_item EXCEPTION; > > CURSOR check_target_cur IS > SELECT msib.segment1 > FROM mtl_system_items_b msib > WHERE msib.segment1 = :NEW.target_bom > AND msib.organization_id IN (168,169); > > CURSOR check_source_cur IS > SELECT msib.segment1 > FROM mtl_system_items_b msib > WHERE msib.segment1 = :NEW.source_bom > AND msib.organization_id IN (26,148); > > BEGIN > FOR check_target_r IN check_target_cur > LOOP > v_target_exists := 'Y'; > END LOOP; > > FOR check_source_r IN check_source_cur > LOOP > v_source_exists := 'Y'; > END LOOP; > > IF v_target_exists = 'N' > OR v_source_exists = 'N' > THEN > RAISE invalid_item; > END IF; > > EXCEPTION > WHEN invalid_item > THEN > IF v_target_exists = 'N' AND v_source_exists = 'Y' THEN > v_error_text := 'The target BOM does not exist in Oracle'; > ELSIF v_source_exists = 'N' AND v_target_exists = 'Y' THEN > v_error_text := 'The source BOM does not exist in Oracle'; > ELSIF v_target_exists = 'N' AND v_source_exists = 'N' THEN > v_error_text := 'Neither the source BOM nor the target BOM exist > is Oracle'; > > END IF; > > RAISE_APPLICATION_ERROR(-20012, v_error_text); > > END;
--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---