[20211230]完善sql_id脚本.txt

--//工作需要，有时候要抽取sql语句脚本。我自己经常使用两个脚本：sqlid.sql 和 sql_id.sql。

$ cat sqlid.sql
column sql_fulltext format a160
column sqltext format a255
set head off

prompt
prompt --sql_id = &&1
prompt
select to_char(replace(sql_fulltext,chr(13),'')) sqltext  from v$sql where  sql_id = '&&1' and rownum<=1
union
select to_char(replace(sql_text,chr(13),'')) sqltext  from dba_hist_sqltext where  sql_id = '&&1' and rownum<=1;
set head on

$ cat sql_id.sql
COLUMN SQL_FULLTEXT FORMAT A180
COLUMN SQLTEXT FORMAT A255

SELECT SQL_ID,HASH_VALUE,REPLACE(SQL_FULLTEXT,CHR(13),'') SQLTEXT FROM GV$SQLAREA WHERE SQL_ID='&1' AND ROWNUM=1;
PROMPT VIEW DBA_HIST_SQLTEXT
SELECT SQL_ID ,REPLACE(SQL_TEXT,CHR(13),'0')  SQLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;

--//为什么要有两个，主要原因一些开发仅仅使用chr(13)作为换行在PB下。导致我在sqlplus下经常看到的sql语句是乱的。
--//如果sql语句超长大于4000，我会直接使用sql_id.sql，但是输出两遍非常不好,必须重新改写sql_id脚本。

--//另外我不想输出两次，采用sqlid.sql脚本，必须使用union但是必须加to_char.如果不加会报错。

--//不加to_char使用union的情况：
SYS@127.0.0.1:17101/dyhis> @sql_id d3bxwwrcjp3tt
sql_id = d3bxwwrcjp3tt
select replace(sql_fulltext,chr(13),'') sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

--//修改成union all可以通过，但是不是我需要的。但是无法解决sql语句超长问题。

> @sqlid d3bxwwrcjp3tt
select sql_id,hash_value,to_char(replace(sql_fulltext,chr(13),'')) sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1
                         *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4552, maximum: 4000)

--//单独执行如下没有问题。
> select sql_id,hash_value,replace(sql_fulltext,chr(13),'') sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1;
> select sql_id,hash_value,sql_fulltext sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1;

--//根据需要修改如下：
$ cat sql_id.sql
--COLUMN SQL_FULLTEXT FORMAT A180
--COLUMN SQLTEXT FORMAT A255
--
--SELECT SQL_ID,HASH_VALUE,REPLACE(SQL_FULLTEXT,CHR(13),'') SQLTEXT FROM GV$SQLAREA WHERE SQL_ID='&1' AND ROWNUM=1;
--PROMPT VIEW DBA_HIST_SQLTEXT
--SELECT SQL_ID ,REPLACE(SQL_TEXT,CHR(13),'0')  SQLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;

--SELECT SQL_ID,TO_CHAR(SQL_FULLTEXT) SQLTEXT FROM GV$SQLAREA WHERE SQL_ID='&1' AND ROWNUM=1
--UNION
--SELECT SQL_ID,TO_CHAR(SQL_TEXT) SQLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;

SET LINESIZE 32767
--SET LINESIZE 4000
VAR V_SQL_FULLTEXT CLOB
COL SQL_FULLTEXT FOR A4000 WORD_WRAP

SET FEEDBACK OFF
SET SERVEROUTPUT ON

PROMPT
PROMPT --SQL_ID = &&1

DECLARE
    V_SQL_FULLTEXT   CLOB;
    V_COUNT          NUMBER;
BEGIN
    SELECT COUNT(*) INTO V_COUNT  FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM=1;
    IF  V_COUNT=1
    THEN
        SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;
        DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
    ELSE
        SELECT COUNT(*)  INTO V_COUNT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
        IF  V_COUNT=1
        THEN
            SELECT REPLACE (SQL_TEXT||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
            DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
        END IF;
    END IF;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        NULL;
END;
/

PROMPT
SET SERVEROUTPUT OFF
SET FEEDBACK 6
SET LINESIZE 277

--//这样基本可以满足我工作需求。实际上使用replace也有现在，最大不能超过32767，一般很少出现怎么超长的sql语句。
--//另外必须设置SET LINESIZE 32767 ， 不然输出的sql语句仅仅输出受linesize的限制。