[20260118]建立完善col_list.sql脚本.txt

--//增加replace V$ V_$，这样系统视图也可以使用。
--//简单测试如下：
SYS@book> @ col_list v$sql sql_id|is_

SELECT
 sql_id
,is_obsolete
,is_bind_sensitive
,is_bind_aware
,is_shareable
,is_reoptimizable
,is_resolved_adaptive_plan
,is_rolling_invalid
,is_rolling_refresh_invalid
FROM v$sql

--//源代码如下：
$ cat col_list.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name:        col_list.sql
-- Purpose:     display table of column_name list.
--
-- Author:      lfree
-- Usage:
--     @col_list owner.table_name regexp_column_list|column_lists
--     column_list format : 1,2,4,5
--
--------------------------------------------------------------------------------

set term off head off feedback off
col 2 new_value 2
col tpt_comment1 new_value _tpt_comment1
col tpt_comment2 new_value _tpt_comment2

select null "2" ,null "3" from dual where 1=2;
select
 decode('&2',null,'*','&2') "2"
,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN '--' WHEN '&2' IS NULL THEN '--' ELSE '  ' END tpt_comment1
,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN '  ' WHEN '&2' IS NULL THEN '--' ELSE '--' END tpt_comment2
from dual;

set term on

def _sp_tmpfile=&_tpt_tempdir/sp_&_tpt_tempfile..tmp

spool &_sp_tmpfile
SELECT    'SELECT'
       || CHR (10)
       || ' '
       || LISTAGG (LOWER (column_name), CHR (10) || ',') WITHIN GROUP (ORDER BY column_id)
       || CHR (10)
       || 'FROM &&1'
          c80
  FROM (  SELECT data_type
                ,column_id
                ,column_name
                ,data_type
            FROM dba_tab_cols
           WHERE     UPPER (table_name) LIKE
                                                replace(
                        UPPER (
                           CASE WHEN INSTR ('&&1', '.') > 0 THEN SUBSTR ('&&1', INSTR ('&&1', '.') + 1) ELSE '&&1' END)
                                                   ,'V$','V_$')
                 AND owner LIKE
                        CASE
                           WHEN INSTR ('&&1', '.') > 0 THEN UPPER (SUBSTR ('&&1', 1, INSTR ('&&1', '.') - 1))
                           ELSE USER
                        END ESCAPE '\'
                 AND HIDDEN_COLUMN = 'NO'
                 &_tpt_comment1 AND REGEXP_LIKE (LOWER (COLUMN_NAME), LOWER ('&&2'))
                                 &_tpt_comment2 AND COLUMN_id in ( &&2)
        ORDER BY column_id);
spool off

set head on feedback 6

get &_sp_tmpfile nolist

host &_delete &_sp_tmpfile