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
-~----------~----~----~----~------~----~------~--~---

Reply via email to