CREATE OR REPLACE PACKAGE BODY pkgrun IS

  PROCEDURE start_testrun (name_run_v       IN VARCHAR2)
  IS
    
    id_run_v       run.id%TYPE;  --id_run_v NUMBER;
    
    CURSOR c_blocksinrun(id_run_v NUMBER) IS
      SELECT id_block
      FROM blocks_in_run 
      WHERE id_run = id_run_v
      ORDER BY run_sequence;
    
  BEGIN
  
    SELECT id
    INTO id_run_v
    FROM run
    WHERE name = name_run_v;
    
    FOR r_blocksinrun IN c_blocksinrun (id_run_v) LOOP
    
      start_block(id_run_v, r_blocksinrun.id_block);
    
    END LOOP;
  
  END start_testrun;
--
-------------------------------------------------------------  
  PROCEDURE start_block (id_run_v           IN NUMBER,
                         id_block_v         IN NUMBER)
  IS
    
    CURSOR c_blockcommand(id_block_v NUMBER) IS
      SELECT c.id, c.command, c.returntype, c.testtype, c.testvalue
      FROM command c
      WHERE c.id_block = id_block_v
      ORDER BY c.run_sequence;
    
  BEGIN
  
    FOR r_blockcommand IN c_blockcommand(id_block_v) LOOP
      
      start_command(r_blockcommand.testtype, r_blockcommand.command, r_blockcommand.testvalue, r_blockcommand.id, id_run_v, r_blockcommand.returntype);
      
    END LOOP;
  
  END start_block;
--
-------------------------------------------------------------
  PROCEDURE start_command (testtype_v       IN VARCHAR2,
                           command_v        IN VARCHAR2,
                           testvalue_v      IN VARCHAR2, 
                           id_command_v     IN NUMBER,
                           id_run_v         IN NUMBER,
                           returntype_v     IN VARCHAR2)
  IS
  
    testresult_v             VARCHAR2(100);
    testresult_number_v      NUMBER;
    testresult_cursor_v      t_cursor;
  
  BEGIN
    
    IF testtype_v = '' THEN
    
      EXECUTE IMMEDIATE command_v;
    
    ELSE
      
      IF returntype_v = 'NUMBER' THEN
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_number_v;
        testresult_v := to_char(testresult_number_v);
      
      ELSIF returntype_v = 'CURSOR' THEN
      
        OPEN testresult_cursor_v FOR SELECT command_v FROM dual;
          testresult_v := to_char(testresult_cursor_v%ROWCOUNT);
        CLOSE testresult_cursor_v;
      
      ELSE
      
        EXECUTE IMMEDIATE command_v
        INTO testresult_v;
      
      END IF;
      
      testresult_v := to_char(testresult_v);
      test_value(testtype_v, testvalue_v, testresult_v, id_command_v, id_run_v);
    
    END IF;
    
  END start_command;
--
-------------------------------------------------------------  
  PROCEDURE test_value (testtype_v          IN VARCHAR2,
                        testvalue_v         IN VARCHAR2,
                        testresult_v        IN VARCHAR2,
                        id_command_v        IN NUMBER,
                        id_run_v            IN NUMBER)
  IS
    
    result_v     result.test_result%TYPE;  --result_v VARCHAR2(10);
    
  BEGIN
    
    IF testtype_v = 'ROWCOUNT' THEN
    
      IF testresult_v = testvalue_v THEN
        
        result_v := 'OK';
        
      ELSE
      
        result_v := 'NOT OK';
      
      END IF;
    
    ELSIF testtype_v = 'VALUE' THEN
    
      IF testresult_v = testvalue_v THEN
        
        result_v := 'OK';
        
      ELSE
      
        result_v := 'NOT OK';
      
      END IF;
    
    ELSIF testtype_v = 'IS_NULL' THEN
    
      IF testresult_v IS NULL THEN
        
        result_v := 'OK';
        
      ELSE
        
        result_v := 'NOT OK';
        
      END IF;
    
    ELSIF testtype_v = 'IS_NOT_NULL' THEN
    
      IF testresult_v IS NOT NULL THEN
      
        result_v := 'OK';
      
      ELSE
      
        result_v := 'NOT OK';
      
      END IF;
    
    END IF;
    
    log_result(id_command_v, id_run_v, result_v);
    
  END test_value;
--
-------------------------------------------------------------  
  PROCEDURE log_result (id_command_v        IN NUMBER, 
                        id_run_v            IN NUMBER, 
                        result_v            IN VARCHAR2) 
  IS
    
  BEGIN
      
    INSERT INTO result(id, id_command, id_run, date_testrun, test_result)
    VALUES (RESULT_SEQ.nextval, id_command_v, id_run_v, sysdate, result_v);
  
  END log_result;
  
END;
/
list
show errors