Sample Header Ad - 728x90

How do Ii use an IF statement in my procedure?

-3 votes
2 answers
349 views
This is my procedure
CREATE OR REPLACE PROCEDURE INSERT_ALL AS 
BEGIN
INSERT_INTO_P_ENZYME;
INSERT_INTO_P_EXPR_SYSTEM;
INSERT_INTO_P_BUFFER_SYSTEM;
INSERT_INTO_0_CONSTRUCT;
INSERT_INTO_1_PRODUCED;
INSERT_INTO_2_PURIFIED_ENZ;
IF EXISTS (SELECT * FROM  not_entered_purified) THEN 
    DBMS_OUTPUT.put_line('test'); -- 19/5      PLS-00103: Encountered the symbol "INSERT_ALL" when expecting one of the following:     if \
Errors: check compiler log



The purpose of the IF is to select from a view that will show me all the records that didn't get inserted into the database during the previous procedure (i.e. when inserting into purified enz). The test print statement will be replaced by a procedure that gives me a list of just the rows that didn't get inserted. Why don't I just create a badfile and do this in a less dumb way, you might ask? Because that doesn't tell me which records got rejected because they didn't match the criteria I set with joins. 


Trying with **END IF**
CREATE OR REPLACE PROCEDURE INSERT_ALL AS BEGIN INSERT_INTO_P_ENZYME; INSERT_INTO_P_EXPR_SYSTEM; INSERT_INTO_P_BUFFER_SYSTEM; INSERT_INTO_0_CONSTRUCT; INSERT_INTO_1_PRODUCED; INSERT_INTO_2_PURIFIED_ENZ; IF (SELECT * FROM insert_2_purified_enz ) THEN SAY_HI; END IF; END INSERT_ALL; / CREATE OR REPLACE PROCEDURE SAY_HI AS BEGIN DBMS_OUTPUT.put_line('test'); END SAY_HI; /
Error:
LINE/COL ERROR --------- ------------------------------------------------------------- 9/5 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe
Asked by ellie-lumen (271 rep)
Aug 25, 2020, 10:26 PM
Last activity: Jul 10, 2025, 01:22 PM