REM aeid.sql
REM (c)Go-Faster Consultancy Ltd. 2009
REM Procedure to add identification comment to SQL statements in Application Engine programs.
REM Thus, source can be easily located for statements found with database utilites and traces
REM 10.9.2009 - initial version
REM 17.9.2009 - added MERGE statement
REM 22.9.2009 - added database type join and subquery to supress duplicates

spool aeid

set serveroutput on echo on
--rollback;

DECLARE
 CURSOR stmt_cursor IS
 SELECT s.ae_applid||'.'||s.ae_section||'.'||s.ae_step||'.'||s.ae_stmt_type identifier
 ,      t.sqlid, t.sqltype, t.market, t.dbtype, t.effdt, t.seqnum, t.sqltext
 FROM 	psaestmtdefn s
 ,	pssqldefn q
 ,	pssqltextdefn t
 WHERE  q.sqlid = t.sqlid
 AND    q.sqltype = t.sqltype
 AND    q.sqltype = 1 --app engine step
 AND    q.sqlid = RPAD(s.ae_applid,12,' ')||RPAD(s.ae_section,8,' ')||RPAD(s.ae_step,8,' ')||s.ae_stmt_type
 AND    t.dbtype = s.dbtype
 AND    s.dbtype IN(' ','2')
 AND    s.dbtype = (
  SELECT MAX(s1.dbtype)
  FROM   psaestmtdefn s1
  WHERE  s1.ae_applid = s.ae_applid
  AND    s1.ae_section = s.ae_section
  AND    s1.market = s.market
  AND    s1.dbtype IN(' ','2')
  AND    s1.effdt = s.effdt
  AND    s1.ae_step = s.ae_step
  AND    s1.ae_stmt_type = s.ae_stmt_type
  )
 AND    t.dbtype = (
  SELECT MAX(t1.dbtype)
  FROM   pssqltextdefn t1
  WHERE  t1.sqlid = t.sqlid
  AND    t1.sqltype = t.sqltype
  AND    t1.market = t.market
  AND    t1.dbtype IN(' ','2'))
/*----------------------------------------------------------------------------------------------------
  --change the criteria to specify which application engine programs are to be commented
  ----------------------------------------------------------------------------------------------------*/
 AND    s.ae_applid LIKE 'GPGB_EDI%'
 AND    1=2
/*----------------------------------------------------------------------------------------------------*/
 UNION ALL
 SELECT t.sqlid identifier
 ,      t.sqlid, t.sqltype, t.market, t.dbtype, t.effdt, t.seqnum, t.sqltext
 FROM 	pssqldefn q
 ,	pssqltextdefn t
 WHERE  q.sqlid = t.sqlid
 AND    q.sqltype = t.sqltype
 AND    q.sqltype = 0 --SQL
 AND    t.dbtype = (
  SELECT MAX(t1.dbtype)
  FROM   pssqltextdefn t1
  WHERE  t1.sqlid = t.sqlid
  AND    t1.sqltype = t.sqltype
  AND    t1.market = t.market
  AND    t1.dbtype IN(' ','2'))
/*----------------------------------------------------------------------------------------------------
  --change the criteria to specify which SQL objects are to be commented
  ----------------------------------------------------------------------------------------------------*/
 AND    t.sqlid like 'GPGB_EDI%'
 AND    1=2
/*----------------------------------------------------------------------------------------------------*/
 ;

 c_stmt stmt_cursor%ROWTYPE;
 l_stmt          CLOB;
 l_ustmt         VARCHAR2(32767);
 l_pos_id        NUMBER;
 l_pos_bracket   NUMBER;
 l_pos_limit     NUMBER;
 l_pos_comma1    NUMBER;
 l_pos_comma2    NUMBER;
 l_pos_sep       NUMBER;
 l_comma_num     NUMBER; --position number of comma in %InsertSelect macro
 l_updated       BOOLEAN;
 l_continue      BOOLEAN;
 l_msg           VARCHAR2(80);
 l_count_stmts   NUMBER := 0;
 l_count_updates NUMBER := 0;
 l_count_error   NUMBER := 0;
 l_count_warn    NUMBER := 0;
 l_count_info    NUMBER := 0;
 l_first_recname VARCHAR2(100);
 l_first_alias   VARCHAR2(10);
 l_first_column  VARCHAR2(20);
 l_foundit       NUMBER;
 l_strlen 	     INTEGER := 0;
 l_status        VARCHAR2(1); /*E=Error, W=Warning, I=Infomation*/
BEGIN
 UPDATE psversion
 SET version = version + 1
 WHERE objecttypename IN ('SYS','SRM');

 UPDATE pslock
 SET version = version + 1
 WHERE objecttypename IN ('SYS','SRM');

 --increment version numbers -- to start transaction

 OPEN stmt_cursor;
 LOOP
  FETCH stmt_cursor INTO c_stmt;
  EXIT WHEN stmt_cursor%NOTFOUND;

  l_continue := TRUE;
  l_updated := FALSE;
  l_status := '';
  l_msg := ''; --reset message
  l_stmt := c_stmt.sqltext;
  l_count_stmts := l_count_stmts + 1;

  IF dbms_lob.getlength(l_stmt) >= 32000 THEN 
   l_msg := 'Statement Too long!';
   l_status := 'E';
   l_continue := FALSE;
  END IF;

  l_ustmt := UPPER(l_stmt);

  IF l_continue AND l_ustmt LIKE '_INSERTSELECT%' THEN
   --find = 
   l_pos_id      := INSTR(l_ustmt,'=');
   l_pos_bracket := INSTR(l_ustmt,'(');
   l_pos_limit   := INSTR(l_ustmt,')');
   l_comma_num   := 1;
   l_pos_comma1  := INSTR(l_ustmt,',',1,1); --position of first comma

   IF l_continue AND (l_pos_id = 0 OR l_pos_id > l_pos_limit) THEN --can't find an =
    --l_msg := 'No column to comment in %InsertSelect';
    --l_continue := FALSE; --no explicitly set column into which to slip comment
    --find table being inserted into
    l_first_recname := LTRIM(RTRIM(SUBSTR(l_ustmt,l_pos_bracket+1,l_pos_comma1-l_pos_bracket-1)));    
    --find first column of table
    SELECT fieldname INTO l_first_column
    FROM   psrecfielddb
    WHERE  recname = l_first_recname AND fieldnum = 1;
    --dbms_output.put_line('Insert into '||l_first_recname||'.'||l_first_column);

    LOOP
      --find table name in second parameter, and table alias
      l_pos_comma1  := INSTR(l_ustmt,',',1,l_comma_num); --position of comma
      l_pos_comma2  := INSTR(l_ustmt,',',1,l_comma_num+1); --position of next comma
      --dbms_output.put_line('('||l_pos_comma1||','||l_pos_comma2||'<'||l_pos_limit||')');
      IF l_pos_comma2 = 0 OR l_pos_comma2 > l_pos_limit THEN
        l_pos_comma2 := l_pos_limit;
      END IF;
      IF l_pos_comma2 <= l_pos_comma1 THEN
        l_msg := 'Could not match column';
        l_status := 'E';
        l_continue := FALSE;
        EXIT;
      ELSE
        l_first_recname := LTRIM(RTRIM(SUBSTR(l_ustmt,l_pos_comma1+1,l_pos_comma2-l_pos_comma1-1)));
        l_pos_sep := INSTR(l_first_recname,' ');
        l_first_alias := SUBSTR(l_first_recname,l_pos_sep+1);
        l_first_recname := SUBSTR(l_first_recname,1,l_pos_sep-1);
        --dbms_output.put_line('Test for '||l_first_recname||'.'||l_first_column);
        --find first column of table
        BEGIN
          SELECT COUNT(*)
          INTO   l_foundit
          FROM   psrecfielddb
          WHERE  recname = l_first_recname AND fieldname = l_first_column;
        END;
        IF l_foundit > 0 THEN
          EXIT;
        ELSE
          l_comma_num := l_comma_num + 1;
        END IF;
      END IF;
    END LOOP;

    IF l_continue THEN
      --add dummy parameter that will carry the hint
      l_stmt := SUBSTR(l_stmt,1,l_pos_limit-1)||', '||l_first_column
                                ||' = /*ID-'||c_stmt.identifier||'*/ '
                                ||l_first_alias||'.'||l_first_column||SUBSTR(l_stmt,l_pos_limit);
      l_updated := TRUE;
     END IF;
   ELSE
    IF l_continue AND SUBSTR(l_ustmt,l_pos_id,7) = '= /*ID-' THEN
     --l_msg := '%InsertSelect already identified';
     l_continue := FALSE; 
    ELSIF l_continue AND SUBSTR(l_ustmt,l_pos_id,5) = '= /*+' THEN
     l_msg := '%InsertSelect already hinted';
     l_status := 'E';
     l_continue := FALSE; 
    ELSIF l_continue AND SUBSTR(l_ustmt,l_pos_id,6) = '= /* +' THEN
     l_msg := '%InsertSelect already hinted';
     l_status := 'E';
     l_continue := FALSE; 
    ELSIF l_continue AND SUBSTR(l_ustmt,l_pos_id,4) = '= /*' THEN
     l_msg := '%InsertSelect already commented';
     l_status := 'E';
     l_continue := FALSE; 
    ELSE
     l_pos_id := l_pos_id + 1;
     l_stmt := SUBSTR(l_stmt,1,l_pos_id-1)||' /*ID-'||c_stmt.identifier||'*/ '||SUBSTR(l_stmt,l_pos_id);
     l_updated := TRUE;
    END IF;
   END IF;
  ELSE

   l_strlen := 7;
   l_pos_id := INSTR(l_ustmt,'UPDATE ');
   IF l_pos_id = 0 THEN
    l_pos_id := INSTR(l_ustmt,'INSERT ');
   END IF;
   IF l_pos_id = 0 THEN
    l_pos_id := INSTR(l_ustmt,'DELETE ');
   END IF;
   IF l_pos_id = 0 THEN
    l_pos_id := INSTR(l_ustmt,'SELECT ');
   END IF;
   IF l_pos_id = 0 THEN
    l_strlen := 6;
    l_pos_id := INSTR(l_ustmt,'MERGE ');
   END IF;
   IF l_pos_id = 0 THEN
    IF SUBSTR(l_ustmt,1,13) = '%UPDATESTATS(' THEN
     l_msg := 'Cannot add comment to %UpdateStats';
     l_status := 'I';
    ELSE
     l_msg := 'Cannot find Query or DML statement';
     l_status := 'E';
    END IF;
    l_continue := FALSE; --can't find select, insert, or delete statement
   ELSE
    l_pos_id := l_pos_id+l_strlen;
    IF SUBSTR(l_ustmt,l_pos_id,3) = '/*+' THEN
     l_msg := 'SQL Contains hint';
     l_status := 'E';
     l_continue := FALSE; --do nothing at the moment there is a hint - could search for end of hint and then for subsequent id comment
    ELSIF SUBSTR(l_ustmt,l_pos_id,5) = '/*ID-' THEN
     --l_msg := 'SQL already identified';
     l_continue := FALSE; --do nothing there is already an identifying comment
    ELSE -- add comment
     l_stmt := SUBSTR(l_stmt,1,l_pos_id-1)||'/*ID-'||c_stmt.identifier||'*/ '||SUBSTR(l_stmt,l_pos_id);
     l_updated := TRUE;
    END IF;
   END IF;

  END IF;

  IF l_continue THEN
   IF l_updated THEN
    --update the sql statement
    dbms_output.put_line(c_stmt.identifier);
    dbms_output.put_line(l_stmt);
    UPDATE pssqltextdefn
    SET    sqltext = l_stmt
    WHERE  sqlid   = c_stmt.sqlid
    AND    sqltype = c_stmt.sqltype
    AND    market  = c_stmt.market
    AND    dbtype  = c_stmt.dbtype
    AND    effdt   = c_stmt.effdt
    AND    seqnum  = c_stmt.seqnum;  
 
    --increment version number of pssqldefn 
    UPDATE pssqldefn
    SET    version = (SELECT version FROM psversion WHERE objecttypename = 'SRM')
    WHERE  sqlid = c_stmt.sqlid
    AND    sqltype = c_stmt.sqltype;

    l_count_updates := l_count_updates + 1;
   END IF;
  ELSIF l_msg IS NOT NULL THEN
   dbms_output.put_line(c_stmt.identifier||': '||l_msg);
   IF l_status = 'E' THEN
    l_count_error := l_count_error + 1;
   ELSIF l_status = 'W' THEN
    l_count_warn := l_count_warn + 1;
   ELSIF l_status = 'I' THEN
    l_count_info := l_count_info + 1;
   END IF;
  END IF;

 END LOOP;
 CLOSE stmt_cursor;

 dbms_output.put_line('Number of statements:'||l_count_stmts);
 dbms_output.put_line('Number of updates   :'||l_count_updates);
 dbms_output.put_line('Number of errors    :'||l_count_error);
 dbms_output.put_line('Number of warnings  :'||l_count_warn);
 dbms_output.put_line('Number of infos     :'||l_count_info);
END;
/

--rollback;
spool off