[20240314]建立完善sqlpatch.sql脚本.txt

--//以前写的脚本sql语句只能从v$sql视图抽取,但是有一些情况不在共享池,要从DBA_HIST_SQLTEXT抽取.
--//纠正一些拼写错误.加入一些版本信息.如果在共享池改写为gv$sqlarea视图.

 $ cat sqlpatchz.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   sqlpatch.sql
-- Purpose:     sql patch
--
-- Author:      lfree
--
-- Usage:
--     @sqlpatch <sql_id> 'hint_text'
--
-- Example:
--     @sqlpatch <sql_id> bind_aware
--     @sqlpatch <sql_id> cursor_sharing_exact
--
--
--------------------------------------------------------------------------------
prompt
prompt input @sqlpatch <sql_id> 'hint_text' oracle_version(11 or 12)
prompt drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');;
prompt display sql patch message , run @spext &1
prompt

define noprint='noprint'
set term off
col tpt_version_old  &noprint new_value _tpt_version_old
col tpt_version_new  &noprint new_value _tpt_version_new

col tpt_comment1 &noprint new_value _tpt_comment1
col tpt_comment2 &noprint new_value _tpt_comment2

col tpt_noprint      &noprint new_value _tpt_noprint

SELECT decode(cnt, 1,'',0,'--') tpt_comment1
   ,decode(cnt,1,'--',0,'') tpt_comment2
  FROM (SELECT count(*) cnt FROM gv$sqlarea WHERE sql_id = '&1' AND rownum = 1);

WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 11  THEN '' ELSE '--' END tpt_version_new
  FROM version;

set term on

declare
   v_sql CLOB;
   patch_name   VARCHAR2 (100);
begin
&&_tpt_comment1 select sql_fulltext into v_sql from gv$sqlarea   where sql_id='&1' and rownum=1;
&&_tpt_comment2 select sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='&1' and rownum=1;

&&_tpt_version_old   sys.dbms_sqldiag_internal.i_create_patch(
&&_tpt_version_old      sql_text  => v_sql,
&&_tpt_version_old      hint_text => '&2',
&&_tpt_version_old      name      => 'sqlpatch_&1');

&&_tpt_version_new   patch_name :=
&&_tpt_version_new       sys.DBMS_SQLDIAG.create_sql_patch
&&_tpt_version_new       (
&&_tpt_version_new          sql_text    => v_sql
&&_tpt_version_new         ,hint_text   => '&2'
&&_tpt_version_new         ,name        => 'sqlpatch_&1'
&&_tpt_version_new       );

end;
/