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

[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