--#--------------------------------------------------------------------------#
--# sqlexp_put.sql 
--#
--# USAGE : @sqlexp_put flag1 flag2 flag3
--# WHERE   flag1 : (u)pdate tables with ALL NEW explain output for SQL source flaged in flag2.
--#                 (l)oad ONLY NEW sql and explain output for SQL source flaged in flag2.
--#                 (n)o update of explain plans, just a report will be printed.
--#                 (r)un an explain report for SQL source flaged in flag2.
--#                 (s)how 'stored' explain plan for SQL source flaged in flag2.
--#         flag2 : (o)ld sql, SQL is sourced from table sqlexp_sqltext.
--#                 (n)ew sql, SQL is sourced from file defined by 'utlput_file_name'.
--#                 (s)tatement_id, SQL is sourced from table sqlexp_sqltext for statement_id
--#                    as defined in flag3.
--#         flag3 : (%), process ALL statement_ids.
--#                 (any valid statement_id) process supplied statement_id.
SET ECHO OFF
SET VERIFY OFF
SET LINESIZE 132
SET PAGESIZE 40
SET LONG 9999
SET SERVEROUT ON 

DECLARE 

utlput_file_handle      UTL_FILE.FILE_TYPE;
utlput_path             VARCHAR2(255) := '<valid utl_path>';
utlput_file_name        VARCHAR2(255) := 'sqlexp_put.lst';
utlrpt_file_handle      UTL_FILE.FILE_TYPE;
utlrpt_path             VARCHAR2(255) := '<valid utl_path>';
utlrpt_file_name        VARCHAR2(255) := 'sqlexp_rpt.lst';
utlerr_file_handle      UTL_FILE.FILE_TYPE;
utlerr_path             VARCHAR2(255) := '<valid utl_path>';
utlerr_file_name        VARCHAR2(255) := 'sqlexp_err.lst';
found_sql               VARCHAR2(30);
sqltext                 VARCHAR2(1000);
sql_stmt                VARCHAR2(1200);
sql_cursor              NUMBER;
ret                     NUMBER;
diff_count              NUMBER;
total_sql               NUMBER;
new_sql                 NUMBER;
nochange_sql            NUMBER;
change_sql              NUMBER;
eof                     NUMBER;
new_sqltext             NUMBER;
c                       NUMBER;
c80                     NUMBER;
bad_explain             NUMBER;
search_id               VARCHAR2(30);

CURSOR c0 IS 
          SELECT STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME,
                 OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, POSITION, OTHER 
            FROM plan_table WHERE STATEMENT_ID = 'sqlexp';
CURSOR c1 IS 
          SELECT TEXT_SEQ, TEXT_DATE, SQL_TEXT
            FROM sqlexp_sqltext
           WHERE to_char(text_seq) like search_id
           ORDER by TEXT_SEQ;
CURSOR po IS
          SELECT level,lpad('..............................',level)||
                 RPAD(DECODE(operation,NULL,' ',operation)||' '||
                 DECODE(options,NULL,' ',options)||' '||
                 DECODE(object_name,NULL,' ',object_name),64-level,' ')             query,
                 RPAD(DECODE(to_char(id),NULL,' ',to_char(id)),4,' ')               rid,
                 RPAD(DECODE(to_char(parent_id),NULL,' ',to_char(parent_id)),6,' ') parent_id,
                 RPAD(DECODE(to_char(position),NULL,' ',to_char(position)),4,' ')   position
            FROM sqlexp_plan_table
           START WITH id = 0 AND statement_id = found_sql
          CONNECT BY prior id = parent_id AND statement_id = found_sql
           ORDER BY id;
CURSOR pn IS
          SELECT level,lpad('..............................',level)||
                 RPAD(DECODE(operation,NULL,' ',operation)||' '||
                 DECODE(options,NULL,' ',options)||' '||
                 DECODE(object_name,NULL,' ',object_name),64-level,' ')             query,
                 RPAD(DECODE(to_char(id),NULL,' ',to_char(id)),4,' ')               rid,
                 RPAD(DECODE(to_char(parent_id),NULL,' ',to_char(parent_id)),6,' ') parent_id,
                 RPAD(DECODE(to_char(position),NULL,' ',to_char(position)),4,' ')   position
            FROM plan_table
           START WITH id = 0 AND statement_id = 'sqlexp'
          CONNECT BY PRIOR id = parent_id AND statement_id = 'sqlexp'
           ORDER BY id;

PROCEDURE open_utl_files IS
BEGIN
  IF '&2' != 'o' THEN
    utlput_file_handle := UTL_FILE.FOPEN(utlput_path, utlput_file_name, 'r');
  END IF;
  utlrpt_file_handle := UTL_FILE.FOPEN(utlrpt_path, utlrpt_file_name, 'w');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('File location or filename was invalid. '||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('The open_mode parameter in FOPEN was invalid. '||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
  WHEN UTL_FILE.INVALID_OPERATION THEN
    DBMS_OUTPUT.PUT_LINE('The file could not be opened or operated on as requested. '||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
END open_utl_files;

PROCEDURE open_errors_file IS
BEGIN
utlerr_file_handle := UTL_FILE.FOPEN(utlerr_path, utlerr_file_name, 'a');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('File location or filename was invalid. '||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('The open_mode parameter in FOPEN was invalid. '||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
  WHEN UTL_FILE.INVALID_OPERATION THEN
    utlerr_file_handle := UTL_FILE.FOPEN(utlerr_path, utlerr_file_name, 'w');
END open_errors_file;

PROCEDURE delete_from_plan_table IS
BEGIN
  sql_stmt   := 'delete from plan_table where statement_id = ''sqlexp''';
  sql_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sql_cursor, sql_stmt, DBMS_SQL.NATIVE);
  ret        := DBMS_SQL.EXECUTE(sql_cursor);
  DBMS_SQL.CLOSE_CURSOR(sql_cursor); 
  COMMIT;
END delete_from_plan_table;

PROCEDURE generate_explain_plan IS
BEGIN
  bad_explain := 0;
  sql_stmt   := 'explain plan set statement_id = ''sqlexp'' into plan_table for '||sqltext;
  sql_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sql_cursor, sql_stmt, DBMS_SQL.NATIVE);
  ret        := DBMS_SQL.EXECUTE(sql_cursor);
  DBMS_SQL.CLOSE_CURSOR(sql_cursor); 
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'*** UNABLE to generate explain plan ***');
    UTL_FILE.PUT_LINE(utlerr_file_handle,sqltext);
    UTL_FILE.FFLUSH(utlerr_file_handle);
    bad_explain := 1;
END generate_explain_plan;

PROCEDURE print_new_explain_plan IS
BEGIN
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'NEW Explain Plan                                                ID   Parent Pos');
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'--------------------------------------------------------------- ---- ------ ---');
  FOR rpn IN pn LOOP
    UTL_FILE.PUT_LINE(utlrpt_file_handle,rpn.query||rpn.rid||' '||rpn.parent_id||' '||rpn.position);
  END LOOP;
  UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
  UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
  UTL_FILE.FFLUSH(utlrpt_file_handle);
END print_new_explain_plan;

PROCEDURE print_old_explain_plan IS
BEGIN
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'OLD Explain Plan                                                ID   Parent Pos');
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'--------------------------------------------------------------- ---- ------ ---');
  FOR rpo IN po LOOP
    UTL_FILE.PUT_LINE(utlrpt_file_handle,rpo.query||rpo.rid||' '||rpo.parent_id||' '||rpo.position);
  END LOOP;
  UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
  UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
  UTL_FILE.FFLUSH(utlrpt_file_handle);
END print_old_explain_plan;

PROCEDURE print_80col_sqltext IS
BEGIN
  FOR c80 IN 1..1040 LOOP
    IF MOD(c80, 80) = 0 THEN
      c := c80 - 79;
      IF SUBSTR(sqltext,c,80) IS NOT NULL THEN
        UTL_FILE.PUT_LINE(utlrpt_file_handle,SUBSTR(sqltext,c,80));
      END IF;
    END IF;
  END LOOP;
END print_80col_sqltext;

PROCEDURE report_sql_and_new_explain IS
BEGIN
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'SQL :');
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'-----');
  print_80col_sqltext;
  UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
  print_new_explain_plan;
END report_sql_and_new_explain;

PROCEDURE report_sql_and_old_explain IS
BEGIN
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'SQL : (statement_id = '||found_sql||')');
  UTL_FILE.PUT_LINE(utlrpt_file_handle,'-----');
  print_80col_sqltext;
  UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
  print_old_explain_plan;
END report_sql_and_old_explain;

PROCEDURE get_sqltext_id IS
BEGIN
  new_sqltext := 0;
  SELECT TO_CHAR(text_seq) INTO found_sql FROM sqlexp_sqltext WHERE sql_text = sqltext;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    new_sqltext := 1;
    IF '&1' = 'u' THEN
      IF bad_explain = 0 THEN
        SELECT TO_CHAR(sqlexp_seq.nextval) INTO found_sql FROM DUAL;
        INSERT INTO sqlexp_sqltext VALUES(found_sql,sysdate,sqltext);
        FOR r0 IN c0 LOOP
          INSERT INTO sqlexp_plan_table 
               VALUES (found_sql, r0.TIMESTAMP, r0.REMARKS, r0.OPERATION, r0.OPTIONS, r0.OBJECT_NODE, 
                      r0.OBJECT_OWNER, r0.OBJECT_NAME, r0.OBJECT_INSTANCE, r0.OBJECT_TYPE, r0.OPTIMIZER, 
                      r0.SEARCH_COLUMNS, r0.ID, r0.PARENT_ID, r0.POSITION, r0.OTHER);
        END LOOP;
      END IF;
    ELSIF '&1' = 'l' THEN
      IF bad_explain = 0 THEN
        SELECT TO_CHAR(sqlexp_seq.nextval) INTO found_sql FROM DUAL;
        INSERT INTO sqlexp_sqltext VALUES(found_sql,sysdate,sqltext);
        FOR r0 IN c0 LOOP
          INSERT INTO sqlexp_plan_table 
               VALUES (found_sql, r0.TIMESTAMP, r0.REMARKS, r0.OPERATION, r0.OPTIONS, r0.OBJECT_NODE, 
                      r0.OBJECT_OWNER, r0.OBJECT_NAME, r0.OBJECT_INSTANCE, r0.OBJECT_TYPE, r0.OPTIMIZER, 
                      r0.SEARCH_COLUMNS, r0.ID, r0.PARENT_ID, r0.POSITION, r0.OTHER);
        END LOOP;
      END IF;
    END IF;
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'NEW SQL :');
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'---------');
    print_80col_sqltext;
    UTL_FILE.PUT_LINE(utlrpt_file_handle,' ');
    COMMIT;
    print_new_explain_plan;
    new_sql := new_sql + 1;
END get_sqltext_id;

PROCEDURE check_for_diff_explain_plan IS
BEGIN
  SELECT COUNT(*) INTO diff_count FROM plan_table b
   WHERE b.statement_id = 'sqlexp'
     AND b.id           > 0
     AND NOT EXISTS (SELECT statement_id
                       FROM sqlexp_plan_table a
                      WHERE a.statement_id = found_sql
                        AND DECODE(a.operation,NULL,' ',a.operation)     = DECODE(b.operation,NULL,' ',b.operation)
                        AND DECODE(a.options,NULL,' ',a.options)         = DECODE(b.options,NULL,' ',b.options)
                        AND DECODE(a.object_name,NULL,' ',a.object_name) = DECODE(b.object_name,NULL,' ',b.object_name)
                        AND DECODE(a.id,NULL,999999,a.id)                = DECODE(b.id,NULL,999999,b.id)
                        AND DECODE(a.parent_id,NULL,999999,a.parent_id)  = DECODE(b.parent_id,NULL,999999,b.parent_id)
                        AND DECODE(a.position,NULL,999999,a.position)    = DECODE(b.position,NULL,999999,b.position) );
  IF diff_count > 0 THEN
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'EXPLAIN PLAN has CHANGED for SQL : (statement_id = '||found_sql||')');
    print_80col_sqltext;
    print_old_explain_plan;
    print_new_explain_plan;
    IF '&1' = 'u' THEN
      UPDATE sqlexp_sqltext SET TEXT_DATE = sysdate where TO_CHAR(text_seq) = found_sql;
      DELETE FROM sqlexp_plan_table where statement_id = found_sql;
      FOR r0 IN c0 LOOP
        INSERT INTO sqlexp_plan_table 
             VALUES (found_sql, r0.TIMESTAMP, r0.REMARKS, r0.OPERATION, r0.OPTIONS, r0.OBJECT_NODE, 
                    r0.OBJECT_OWNER, r0.OBJECT_NAME, r0.OBJECT_INSTANCE, r0.OBJECT_TYPE, r0.OPTIMIZER, 
                    r0.SEARCH_COLUMNS, r0.ID, r0.PARENT_ID, r0.POSITION, r0.OTHER);
      END LOOP;
      COMMIT;
    END IF;
    change_sql   := change_sql + 1;
  ELSE
    nochange_sql := nochange_sql + 1;
  END IF;
END check_for_diff_explain_plan;

PROCEDURE get_next_sqltext IS
BEGIN
  UTL_FILE.GET_LINE(utlput_file_handle,sqltext);
  total_sql := total_sql + 1;
EXCEPTION
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    DBMS_OUTPUT.PUT_LINE('The file handle was invalid.'||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
  WHEN UTL_FILE.READ_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('An operating system error occurred during the read operation.'||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
  WHEN UTL_FILE.INVALID_OPERATION THEN
    DBMS_OUTPUT.PUT_LINE('The file could not be opened or operated on as requested. '||substr(sqlerrm,1,30));
    DBMS_OUTPUT.PUT_LINE('    SQLCODE:  '||sqlcode);
    RETURN;
  WHEN NO_DATA_FOUND THEN
    eof := 1;
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Value Error');
END get_next_sqltext;

PROCEDURE print_statistics IS
BEGIN
    UTL_FILE.FCLOSE(utlput_file_handle);
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'Total SQL statements processed : '||total_sql);
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'New SQL found                  : '||new_sql);
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'SQL with no changes            : '||nochange_sql);
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'SQL with changes               : '||change_sql);
    UTL_FILE.FFLUSH(utlrpt_file_handle);
    UTL_FILE.FCLOSE(utlrpt_file_handle);
    UTL_FILE.FCLOSE(utlerr_file_handle);
END print_statistics;

BEGIN 
  DBMS_OUTPUT.ENABLE(1000000);
  total_sql    := 0;
  new_sql      := 0;
  nochange_sql := 0;
  change_sql   := 0;
  eof          := 0;
  open_utl_files;
  open_errors_file;
  IF '&2' = 's' THEN
    search_id := '&3';
    FOR r1 IN c1 LOOP
      total_sql := total_sql + 1;
      delete_from_plan_table;
      sqltext := r1.sql_text;
      generate_explain_plan;
      found_sql := to_char(r1.text_seq);
      IF '&1' = 'r' THEN
        report_sql_and_new_explain;
      ELSIF '&1' = 's' THEN
        report_sql_and_old_explain;
      ELSE
        check_for_diff_explain_plan;
      END IF;
    END LOOP;
  ELSIF '&2' = 'o' THEN
    search_id := '%';
    FOR r1 IN c1 LOOP
      total_sql := total_sql + 1;
      delete_from_plan_table;
      sqltext := r1.sql_text;
      generate_explain_plan;
      found_sql := to_char(r1.text_seq);
      IF '&1' = 'r' THEN
        report_sql_and_new_explain;
      ELSIF '&1' = 's' THEN
        report_sql_and_old_explain;
      ELSE
        check_for_diff_explain_plan;
      END IF;
    END LOOP;
  ELSIF '&2' = 'n' THEN
    LOOP
      get_next_sqltext;
      IF eof = 1 THEN
        EXIT;
      END IF;
      delete_from_plan_table;
      generate_explain_plan;
      IF '&1' = 'r' THEN
        report_sql_and_new_explain;
      ELSIF '&1' = 's' THEN
        get_sqltext_id;
        report_sql_and_old_explain;
      ELSE
        get_sqltext_id;
        IF new_sqltext = 0 THEN
          check_for_diff_explain_plan;
        END IF;
      END IF;
    END LOOP;
  ELSE
    UTL_FILE.PUT_LINE(utlrpt_file_handle,'Invalid parameters passed to PL/SQL block');
  END IF;
  print_statistics;
END; 
/
SET ECHO ON
SET VERIFY ON
SET SERVEROUT OFF