Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Triggers invalid after every failed execution of nested procedure

    Hi,

    I am having an issue which is a bit inconvenient. I have an AFTER UPDATE trigger which calls a stored procedure. If the sp fails, the trigger goes into invalid state and I have to recompile it after I fix the SP code.

    Is there a way to avoid ORA-04098? Is there a way to automatically validate the trigger next time it is activated after update?

  2. #2
    Join Date
    Sep 2013
    Posts
    46
    Oracle 11g

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If the sp fails ...
    What does it mean? How does it "fail"? As far as I can tell, in your case, a trigger gets invalidated if it calls an invalid procedure, not if a procedure raises an exception (i.e. "fails").

    Therefore, make the procedure valid, which will also make the trigger valid.

  4. #4
    Join Date
    Sep 2013
    Posts
    46
    If the proc fails due to syntax errors of dynamically prepared statements. If proc fails, trigger becomes invalid then I have to manually re-compile the trigger once underlying proc has been resolved.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If the proc fails due to syntax errors of dynamically prepared statements.

    We have met the enemy, and they is us!

    If you rigorously tested your code before deployment it would not fail.
    Doing dynamic SQL inside a trigger sounds like unprofessional kludge designed by an amateur.
    This problem is one of your own making.
    Would you like some cheese to accompany your whine?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2013
    Posts
    46
    Quote Originally Posted by anacedent View Post
    >
    Doing dynamic SQL inside a trigger sounds like unprofessional kludge designed by an amateur.
    This problem is one of your own making.
    Would you like some cheese to accompany your whine?
    Thanks. What can you suggest other than using dynamic SQL inside the stored procedure? There are input parameters being passed from the trigger for the SP call. Any help is appreciated! Here's the trigger:

    Code:
    create or replace 
    TRIGGER GET_DM_TABLE_STATUS_AUR
      AFTER UPDATE OF STATUS
      ON DM_TABLE_STATUS
      REFERENCING
        NEW AS NEW
      FOR EACH ROW
        WHEN (NEW.STATUS='ETL_F') 
        DECLARE
    --DECLARE LOCAL VARIABLES
      V_DEP_TABLE VARCHAR2(40);
      V_TAR_TABLE VARCHAR2(40);
      V_DEP_SCHEMA VARCHAR2(10);
      CURSOR c1
    IS
      SELECT DEP_SCHEMA,DEP_TABLE
      FROM DM_DEPENDENCY 
      WHERE TAR_TABLE=:new.tbl;
    BEGIN
    
    V_TAR_TABLE:=:NEW.TBL;
    
    OPEN c1;
    LOOP
       FETCH c1 INTO V_DEP_SCHEMA, V_DEP_TABLE;
       EXIT WHEN c1%NOTFOUND;   
    	begin
    		USP_GET_SFDC_ID('CONV_NL',V_DEP_SCHEMA,V_DEP_TABLE,'CONV_NL',V_TAR_TABLE);
    	end;
    END LOOP;
    
    CLOSE c1;
    
    
    END;

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There's nothing "dynamic" in this code.

  8. #8
    Join Date
    Sep 2013
    Posts
    46
    pfb code for USP_GET_SFDC_ID:


    Code:
    CREATE OR REPLACE PROCEDURE USP_GET_SFDC_ID
    (
    in_schema     IN VARCHAR2,
    in_dep_schema IN VARCHAR2,
    in_dep_table  IN VARCHAR2,
    in_tar_schema IN VARCHAR2,
    in_tar_table  IN VARCHAR2 
    )
    	AS
    			PRAGMA AUTONOMOUS_TRANSACTION;
    			v_in_schema				VARCHAR2(10);
    			v_in_tar_schema			VARCHAR2(10);
    			v_in_dep_schema			VARCHAR2(10);
    			v_in_tar_table			VARCHAR2(40);
    			v_in_dep_table			VARCHAR2(40);
    			v_tar_col				VARCHAR2(40);
    			v_tar_id_col			VARCHAR2(40);
    			v_dep_col				VARCHAR2(40);
    			v_dep_id_col			VARCHAR2(40);
    			v_dyn_sql				VARCHAR2(1000);
    			v_cnt_sql				VARCHAR2(500);
    			v_count					NUMBER;
    			l_exst					NUMBER;
    			
    BEGIN
    
    	v_in_schema:=in_schema;
    	v_in_tar_schema:=in_tar_schema;
    	v_in_dep_schema:=in_dep_schema;
    	v_in_tar_table:=in_tar_table;
    	v_in_dep_table:=in_dep_table;
    	v_count:=0;
    
    	v_cnt_sql:='SELECT count(*) 	
                  FROM '||v_in_schema||'.DM_TABLE_STATUS 
                  WHERE 
                    TBL='''||v_in_dep_table||''' 
                    AND
                    STATUS=''OSI_F''';
    		
    		
    	EXECUTE IMMEDIATE v_cnt_sql INTO l_exst;
    	
    	dbms_output.put_line(v_cnt_sql);	
    	dbms_output.put_line(l_exst);
    	
    	<<statusLoop>> LOOP
    
    	
    --IF STATUS IS NOT YET "OSI_F" THEN WAIT (5 MINUTES) AND THEN CHECK AGAIN. KEEP CHECKING 3 TIMES UNTIL STATUS IS UPDATED TO OSI_F
    
      IF
        l_exst>0
      THEN
            dbms_output.put_line('l_exst is > 0');
    --GET ALL VALUES INTO VARIABLE FOR USE IN LOGIC
            v_cnt_sql:='SELECT count(*)
                        FROM '||v_in_schema||'.DM_DEPENDENCY 
                        WHERE 
    											TAR_TABLE = '''||v_in_tar_table||''' 
    											AND 
    											DEP_TABLE = '''||v_in_dep_table||'''';
            EXECUTE IMMEDIATE v_cnt_sql into l_exst;
    			
            IF 
              l_exst > 0
            THEN
                  v_dyn_sql:='SELECT TAR_COL, TAR_ID_COL, DEP_COL, DEP_ID_COL 
                        			FROM '||v_in_schema||'.DM_DEPENDENCY 
                      				WHERE 
                    						TAR_TABLE = '''||v_in_tar_table||''' 
                    						AND 
                      					DEP_TABLE = '''||v_in_dep_table||'''';
                  EXECUTE IMMEDIATE v_dyn_sql INTO v_tar_col, v_tar_id_col, v_dep_col, v_dep_id_col;
                  dbms_output.put_line(v_dyn_sql);					
    --SET DM_DEPENDENCY.SP_STATUS TO "STARTED" ONCE STARTED
                  v_dyn_sql:='UPDATE '||v_in_schema||'.DM_DEPENDENCY 
                            	SET STATUS=''STARTED'' 
                            	WHERE 
                          				TAR_TABLE='''||v_in_tar_table||'''
                          				AND
                        					DEP_TABLE = '''||v_in_dep_table||'''';
                  EXECUTE IMMEDIATE v_dyn_sql;
                  dbms_output.put_line(v_dyn_sql);
                  COMMIT;
    				--UPDATE VIEW_DM_TABLE_STATUS SET STATUS='OPI_S' WHERE TBL=in_tar_table;
            --COMMIT;
    
                  v_cnt_sql:='SELECT count(x.'||v_dep_col||')
                      				FROM   '||v_in_dep_schema||'.'||v_in_dep_table||' x, 
                                  		'||v_in_tar_schema||'.'||v_in_tar_table||' a
                              WHERE  
                                a.'||v_tar_id_col||' = x.'||v_dep_id_col||'
                                AND 
                                x.'||v_dep_col||' is not null';
                  dbms_output.put_line(v_cnt_sql);
    
                  EXECUTE IMMEDIATE v_cnt_sql INTO l_exst;
                  dbms_output.put_line(l_exst);
    
                  IF 
                  	l_exst > 0
                  THEN
    			
                      v_dyn_sql:='UPDATE '||v_in_tar_schema||'.'||v_in_tar_table||' a
                                SET a.'||v_tar_col||' = (SELECT x.'||v_dep_col||'
                                                          FROM   '||v_in_dep_schema||'.'||v_in_dep_table||' x
                                                          WHERE  
                                                          a.'||v_tar_id_col||' = x.'||v_dep_id_col||'
                                                          AND 
                                                          x.'||v_dep_col||' is not null) 
                                WHERE  a.'||v_tar_id_col||' = (SELECT '||v_dep_id_col||'
                                                                FROM   '||v_in_dep_schema||'.'||v_in_dep_table||' x
                                                                WHERE  
                                                                  a.'||v_tar_id_col||' = x.'||v_dep_id_col||'
                                                                  AND 
                                                                  x.'||v_dep_col||' is not null)';
    	
                      dbms_output.put_line('Update started at '||CURRENT_TIMESTAMP);
                      dbms_output.put_line(v_dyn_sql);
    						
                      EXECUTE IMMEDIATE v_dyn_sql;
                      COMMIT;
    						
                      dbms_output.put_line('Update completed successfully at '||CURRENT_TIMESTAMP);
    
    --IF SUCCESSFUL THEN SET SP_STATUS TO "SUCCESS"
                      v_dyn_sql:='UPDATE '||v_in_schema||'.DM_DEPENDENCY 
                                  SET STATUS=''SUCCESS'' 
                                  WHERE 
                                    TAR_TABLE='''||v_in_tar_table||'''
                                    AND
                                    DEP_TABLE = '''||v_in_dep_table||'''';
    	
                      EXECUTE IMMEDIATE v_dyn_sql;
                      dbms_output.put_line(v_dyn_sql);
                      COMMIT;
    --EXIT statusLoop	
                      EXIT;
    					
                  ELSE
                      dbms_output.put_line('--sfdc_id unavailable--');
                      EXIT;
                  END IF;
    
    				--UPDATE VIEW_DM_TABLE_STATUS SET STATUS='OPI_F' WHERE TBL=in_tar_table;
            --COMMIT;
            ELSE
                EXIT;
            END IF;
    	ELSE
          v_count := v_count + 1;
    --IF WAITING MORE THAN SOME TIME (5 MINUTES * 3 IN THIS CASE) THEN EXIT
          IF 
            v_count > 3 
          THEN
    --FAILED TO FINISH IN 15 MINUTES.
              v_dyn_sql:='UPDATE '||v_in_schema||'.DM_DEPENDENCY 
                    			SET STATUS=''ERROR''
                      		WHERE 
                        		TAR_TABLE='''||v_in_tar_table||'''
                      			AND
                        		DEP_TABLE = '''||v_in_dep_table||'''';
              EXECUTE IMMEDIATE v_dyn_sql;
              dbms_output.put_line(v_dyn_sql);
              COMMIT;
    --EXIT statusLoop;
              EXIT;
          ELSE
    --Sleep 300 seconds(5 minutes), may remove this sleep..
              dbms_lock.sleep(300);
          END IF;
    	END IF;
    END LOOP statusLoop;
    END USP_GET_SFDC_ID;
    	/
    Table structures for DM_TABLE_STATUS and DM_DEPENDENCY:

    Code:
    CREATE TABLE DM_DEPENDENCY
       (	"TAR_TABLE" VARCHAR2(30 CHAR), 
    	"TAR_COL" VARCHAR2(30 CHAR), 
    	"TAR_ID_COL" VARCHAR2(30 CHAR), 
    	"DEP_SCHEMA" VARCHAR2(10 CHAR) NOT NULL ENABLE, 
    	"DEP_TABLE" VARCHAR2(30 CHAR), 
    	"DEP_COL" VARCHAR2(30 CHAR), 
    	"DEP_ID_COL" VARCHAR2(30 CHAR), 
    	"STATUS" VARCHAR2(10 CHAR)
       );
    
    CREATE TABLE DM_TABLE_STATUS
       (	"TBL" VARCHAR2(40 CHAR) DEFAULT NULL NOT NULL ENABLE, 
    	"STATUS" VARCHAR2(5 CHAR) DEFAULT 'NS' NOT NULL ENABLE, 
    	 CONSTRAINT "DM_TABLE_STATUS_UK1" UNIQUE ("TBL");

  9. #9
    Join Date
    Sep 2013
    Posts
    46
    The code works fine. Only fails when user inserts table names which do not exist, thus dynamic sql's fail. I guess the best resolution to the problem is make sure user inserts non-typo's.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    While I'm not thrilled with your code, then check to see if the table exists using all_tables view
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Sep 2013
    Posts
    46
    Hi Bill, thanks, I added the following at the start of the loop and moved variable set to inside the loop after the new lines below:

    --START OF LOOP
    <<statusLoop>> LOOP
    SELECT COUNT(*)
    INTO l_exst
    FROM ALL_TABLES
    WHERE
    TABLE_NAME=IN_DEP_TABLE
    AND
    OWNER=IN_DEP_SCHEMA;

    IF
    l_exst > 0
    THEN

    SELECT COUNT(*) INTO l_exst
    FROM ALL_TABLES
    WHERE
    TABLE_NAME=IN_TAR_TABLE
    AND
    OWNER=IN_TAR_SCHEMA;

    IF
    l_exst > 0
    THEN
    Could you please share a better approach if you have one in mind? Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •