DBVis/PL SQL: IF EXIST - how to structure correctly if possible

Question

Using the DBVis SQL commander (as I am using "begin" and "end" in this code, I believe this code is executing in a PL SQL manner) I am trying to execute an sql script I have written to be my new "cleaning trigger"

I am trying to use IF EXISTS statements in this script to achieve cleaning ONLY when a certain column does actually contain data (APPROVED or REJECT)

This script aims to NOT use the insert (to other tables) statements (these will set off other triggers on other tables in an undesired way if they are used, even if there is no data for them to insert) unless there actually is (not null) data in a certain column:

begin

IF EXISTS (SELECT * 
        FROM HUB_SEGMENTS 
        where APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED')
        THEN
INSERT INTO
 HUB_APPROVED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
         SELECT 
         HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE 
         FROM 
         HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED';

INSERT INTO
 HUB_REJECTED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
         SELECT 
         HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE 
         FROM 
         HUB_SEGMENTS WHERE APP_OR_REJECT = 'REJECTED';     

DELETE
FROM
    "TESTDEMO".HUB_SEGMENTS
WHERE
    APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED';
    DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables Executed');


ELSE 

    DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables Not Executed');

end;

I unfortunately get the following error message when I try and run this:

[Code: 6550, SQL State: 65000] ORA-06550: line 33, column 4:PLS-00103: Encountered the symbol ";" when expecting one of the following: if

I am pretty sure that this is down to the way that I have structured the IF EXIST

Of course, it may be that using IF EXIST in DBVis SQL commander,in my PL SQL code, isn't permissible - I haven't seen much documentation for this specific statement from oracle. If so, would there be a another way I might try and accomplish what I am trying to do here?

Any guidance would be greatly appreciated

score 1 · Answer 1 · answered Jan 07 '20 at 23:38

You don't need the if exists I beleive Just turn

IF EXISTS (SELECT * 
        FROM HUB_SEGMENTS 
        where APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED')
        THEN
INSERT INTO
 HUB_APPROVED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
         SELECT 
         HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE 
         FROM 
         HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED';

To

INSERT INTO
 HUB_APPROVED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
         SELECT 
         HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE 
         FROM 
         HUB_SEGMENTS where (APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED');

GMB · Answer 2 · 2020-01-08T03:22:08.110

This is the structure of your code:

BEGIN
    IF EXISTS (SELECT... FROM HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED')
        THEN
            INSERT INTO HUB_APPROVED SELECT ... FROM FROM HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED';
            INSERT INTO HUB_REJECTED SELECT ... FROM FROM HUB_SEGMENTS WHERE APP_OR_REJECT = 'REJECTED';
            DELETE FROM HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED';
        ELSE
            ...;
END;

You can easily see that indenting skips on level before the final END: you are missing an END IF that closes the IF block. Oracle is trying to tell you that by giving you the line that corresponds to the final end; and saying: Encountered the symbol ; when expecting one of the following: if.

BEGIN
    IF EXISTS (SELECT... FROM HUB_SEGMENTS WHERE APP_OR_REJECT IN ('APPROVED', 'REJECTED');
        THEN
            INSERT INTO HUB_APPROVED SELECT ... FROM FROM HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED';
            INSERT INTO HUB_REJECTED SELECT ... FROM FROM HUB_SEGMENTS WHERE APP_OR_REJECT = 'REJECTED';
            DELETE FROM HUB_SEGMENTS WHERE APP_OR_REJECT IN ('APPROVED', 'REJECTED');
        ELSE
            ...;
    END IF;             --> here
END;

Bonus: you can use IN instead of these ORed conditions.

Side note: if performance does not matter, then, as suggested by @zip, don't bother with the IF block. You can directly run the two INSERTs and the DELETE statement one after the other. If there is not data that corresponds to the search criteria in HUB_SEGMENTS, then nothing will inserted or deleted anyway.

score 0 · Accepted Answer · answered Jan 14 '20 at 17:44

thanks for the helpful comments - those would have worked if I was using non-oracle SQL (probably?). Unfortunately running a PL/SQL script with "exists" on DBVis pointed at an oracle db causes an error - Exists does not work in PL/SQL scripts (but should in single SQL statements apparently).

I found using a variable, in conjunction with IF, solved my issue:


Declare 
        v_count NUMBER;

BEGIN


select COUNT(*)
        INTO v_count
        FROM HUB_SEGMENTS
        WHERE APP_OR_REJECT is not null; --for each row where %new.columnname is not new --=> this would be far less "expensive" sql query, need to figure out how to syntax this 

IF v_count >0
        THEN

                 INSERT INTO
                 HUB_APPROVED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
                 SELECT 
                 HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE 
                 FROM 
                 HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED';


        INSERT INTO 
         HUB_REJECTED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
         SELECT 
         HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE 
         FROM 
         HUB_SEGMENTS WHERE APP_OR_REJECT = 'REJECTED';     

DELETE
FROM
    "TESTDEMO".HUB_SEGMENTS
WHERE
    APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED';

    DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables Executed');

ELSE 
    DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables NOT executed');    

END IF;


end;

This managed to work for what I was attempting to achieve (not going to the Insert/Delete statements unless there was actually data in a specific column, in order to avoid accidentally setting off other triggers, weirdly)

I was told by a colleague that doing something along the lines of "for each row where %new.columnname is not new =>..." would be a far less cpu intensive sql query, but I need to figure out how to syntax this. For now, the above code works in a PL/SQL, pointed at an oracle Database, in DBVis.

If I find the "for each row..." way, I'll post it here as a comment

DBVis/PL SQL: IF EXIST - how to structure correctly if possible

3 Answers3