oracle 如何在all_mviews脚本中搜索关键字？

5w9g7ksd  于 2023-06-22  发布在  Oracle
关注(0)|答案(2)|浏览(136)

我试图在用于创建materialisd视图的脚本中搜索关键字CUSTOMER。我是通过搜索表all_mviews的QUERY列来实现这一点的。所以理论上

SELECT *
FROM ALL_MVIEWS
WHERE UPPER(QUERY) LIKE '%CUSTOMER%';

应该可以但是，因为查询不是CHAR数据类型，所以我不能这样做。我试过使用TO_CHAR(QUERY)，DMBS_LOB.INSTR(QUERY, 'CUSTOMER')，TO_LOB(QUERY)。似乎什么都不管用。理想情况下，我想用普通SQL而不是PL/SQL来做这件事。

oracle

2条答案

按热度按时间
k4ymrczo

k4ymrczo1#

你当然可以在普通SQL中查询，但是你将无法摆脱在幕后使用PL/SQL。这是因为LONG只能由PL/SQL操作。
要实现这一点，您必须编写一个PL/SQL函数，该函数 Package 有问题的字典视图（这里是all_mviews），将LONG数据类型转换为CLOB，并发出替换行。然后，您可以使用纯SQL查询查询该函数，并像往常一样对其应用搜索 predicate 。下面是一个用于19 c中可用列的版本：

CREATE OR REPLACE TYPE dba_mviews_rectype IS OBJECT (
        CONTAINER_NAME  VARCHAR2(128),
        QUERY   CLOB,
        QUERY_LEN   NUMBER,
        UPDATABLE   VARCHAR2(1),
        UPDATE_LOG  VARCHAR2(128),
        MASTER_ROLLBACK_SEG VARCHAR2(128),
        MASTER_LINK VARCHAR2(128),
        REWRITE_ENABLED VARCHAR2(1),
        REWRITE_CAPABILITY  VARCHAR2(9),
        REFRESH_MODE    VARCHAR2(9),
        REFRESH_METHOD  VARCHAR2(8),
        BUILD_MODE  VARCHAR2(9),
        FAST_REFRESHABLE    VARCHAR2(18),
        LAST_REFRESH_TYPE   VARCHAR2(8),
        LAST_REFRESH_DATE   DATE,
        LAST_REFRESH_END_TIME   DATE,
        STALENESS   VARCHAR2(19),
        AFTER_FAST_REFRESH  VARCHAR2(19),
        UNKNOWN_PREBUILT    VARCHAR2(1),
        UNKNOWN_PLSQL_FUNC  VARCHAR2(1),
        UNKNOWN_EXTERNAL_TABLE  VARCHAR2(1),
        UNKNOWN_CONSIDER_FRESH  VARCHAR2(1),
        UNKNOWN_IMPORT  VARCHAR2(1),
        UNKNOWN_TRUSTED_FD  VARCHAR2(1),
        COMPILE_STATE   VARCHAR2(19),
        USE_NO_INDEX    VARCHAR2(1),
        STALE_SINCE DATE,
        NUM_PCT_TABLES  NUMBER,
        NUM_FRESH_PCT_REGIONS   NUMBER,
        NUM_STALE_PCT_REGIONS   NUMBER,
        SEGMENT_CREATED VARCHAR2(3),
        EVALUATION_EDITION  VARCHAR2(128),
        UNUSABLE_BEFORE VARCHAR2(128),
        UNUSABLE_BEGINNING  VARCHAR2(128),
        DEFAULT_COLLATION   VARCHAR2(100),
        ON_QUERY_COMPUTATION    VARCHAR2(1)
        )
        /
        CREATE OR REPLACE TYPE dba_mviews_tabtype IS TABLE OF dba_mviews_rectype
        /
        CREATE OR REPLACE FUNCTION f_dba_mviews_ext (in_owner IN varchar2, in_mview_name IN varchar2)
          RETURN dba_mviews_tabtype PIPELINED
        AS  
          var_cursor binary_integer;
          var_query varchar2(4000);
          res pls_integer;
          tab_desc dbms_sql.desc_tab;
          var_column_count pls_integer;
          rec_dummy_mview dba_mviews_rectype;
          var_owner dba_mviews.owner%TYPE;
          var_mview_name dba_mviews.mview_name%TYPE;
        BEGIN
          var_query := 'SELECT *
                          FROM dba_mviews
                         WHERE owner = :owner
                           AND mview_name = :mview_name';
                           
          var_cursor := dbms_sql.open_cursor;
          dbms_sql.parse(var_cursor,var_query,dbms_sql.native);
          dbms_sql.bind_variable(var_cursor,'owner',in_owner);
          dbms_sql.bind_variable(var_cursor,'mview_name',in_mview_name);
          
          dbms_sql.define_column(var_cursor,1,var_owner,128);
          dbms_sql.define_column(var_cursor,2,var_mview_name,128);
          dbms_sql.define_column(var_cursor,3,rec_dummy_mview.container_name,128);
          dbms_sql.define_column_long(var_cursor,4);

          dbms_sql.define_column(var_cursor,5,rec_dummy_mview.query_len);
          dbms_sql.define_column(var_cursor,6,rec_dummy_mview.updatable,1);
          dbms_sql.define_column(var_cursor,7,rec_dummy_mview.update_log,128);
          dbms_sql.define_column(var_cursor,8,rec_dummy_mview.master_rollback_seg,128);
          dbms_sql.define_column(var_cursor,9,rec_dummy_mview.master_link,128);
          dbms_sql.define_column(var_cursor,10,rec_dummy_mview.rewrite_enabled,1);
          dbms_sql.define_column(var_cursor,11,rec_dummy_mview.rewrite_capability,9);
          dbms_sql.define_column(var_cursor,12,rec_dummy_mview.refresh_mode,9);
          dbms_sql.define_column(var_cursor,13,rec_dummy_mview.refresh_method,8);
          dbms_sql.define_column(var_cursor,14,rec_dummy_mview.build_mode,9);
          dbms_sql.define_column(var_cursor,15,rec_dummy_mview.fast_refreshable,18);
          dbms_sql.define_column(var_cursor,16,rec_dummy_mview.last_refresh_type,8);
          dbms_sql.define_column(var_cursor,17,rec_dummy_mview.last_refresh_date);
          dbms_sql.define_column(var_cursor,18,rec_dummy_mview.last_refresh_end_time);
          dbms_sql.define_column(var_cursor,19,rec_dummy_mview.staleness,19);
          dbms_sql.define_column(var_cursor,20,rec_dummy_mview.after_fast_refresh,19);
          dbms_sql.define_column(var_cursor,21,rec_dummy_mview.unknown_prebuilt,1);
          dbms_sql.define_column(var_cursor,22,rec_dummy_mview.unknown_plsql_func,1);
          dbms_sql.define_column(var_cursor,23,rec_dummy_mview.unknown_external_table,1);
          dbms_sql.define_column(var_cursor,24,rec_dummy_mview.unknown_consider_fresh,1);
          dbms_sql.define_column(var_cursor,25,rec_dummy_mview.unknown_import,1);
          dbms_sql.define_column(var_cursor,26,rec_dummy_mview.unknown_trusted_fd,1);
          dbms_sql.define_column(var_cursor,27,rec_dummy_mview.compile_state,19);
          dbms_sql.define_column(var_cursor,28,rec_dummy_mview.use_no_index,1);
          dbms_sql.define_column(var_cursor,29,rec_dummy_mview.stale_since);
          dbms_sql.define_column(var_cursor,30,rec_dummy_mview.num_pct_tables);
          dbms_sql.define_column(var_cursor,31,rec_dummy_mview.num_fresh_pct_regions);
          dbms_sql.define_column(var_cursor,32,rec_dummy_mview.num_stale_pct_regions);
          dbms_sql.define_column(var_cursor,33,rec_dummy_mview.segment_created,3);
          dbms_sql.define_column(var_cursor,34,rec_dummy_mview.evaluation_edition,128);
          dbms_sql.define_column(var_cursor,35,rec_dummy_mview.unusable_before,128);
          dbms_sql.define_column(var_cursor,36,rec_dummy_mview.unusable_beginning,128);
          dbms_sql.define_column(var_cursor,37,rec_dummy_mview.default_collation,100);
          dbms_sql.define_column(var_cursor,38,rec_dummy_mview.on_query_computation,1);
          
          
          res := dbms_sql.execute(var_cursor);
          
          WHILE dbms_sql.fetch_rows(var_cursor) > 0
          LOOP
            DECLARE
              var_query_clob clob;
              var_offset pls_integer := 0;
              var_buffer_length pls_integer := 32760;
              var_value_length pls_integer := 0;
              var_long_piece long;
              rec_mview dba_mviews_rectype := dba_mviews_rectype(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
            BEGIN
              dbms_sql.column_value(var_cursor,3,rec_mview.container_name);
                      
              dbms_lob.createtemporary(var_query_clob, false, dbms_lob.call);
                
              LOOP
                dbms_sql.column_value_long(c => var_cursor,
                                           position => 4,
                                           length => var_buffer_length,
                                           offset => var_offset,
                                           value => var_long_piece,
                                           value_length => var_value_length);
                                           
                EXIT WHEN var_value_length = 0;
                
                dbms_lob.append(var_query_clob, var_long_piece);
                var_offset := var_offset + var_value_length;
              END LOOP;
              
              rec_mview.query := var_query_clob;
              
          dbms_sql.column_value(var_cursor,5,rec_mview.query_len);
          dbms_sql.column_value(var_cursor,6,rec_mview.updatable);
          dbms_sql.column_value(var_cursor,7,rec_mview.update_log);
          dbms_sql.column_value(var_cursor,8,rec_mview.master_rollback_seg);
          dbms_sql.column_value(var_cursor,9,rec_mview.master_link);
          dbms_sql.column_value(var_cursor,10,rec_mview.rewrite_enabled);
          dbms_sql.column_value(var_cursor,11,rec_mview.rewrite_capability);
          dbms_sql.column_value(var_cursor,12,rec_mview.refresh_mode);
          dbms_sql.column_value(var_cursor,13,rec_mview.refresh_method);
          dbms_sql.column_value(var_cursor,14,rec_mview.build_mode);
          dbms_sql.column_value(var_cursor,15,rec_mview.fast_refreshable);
          dbms_sql.column_value(var_cursor,16,rec_mview.last_refresh_type);
          dbms_sql.column_value(var_cursor,17,rec_mview.last_refresh_date);
          dbms_sql.column_value(var_cursor,18,rec_mview.last_refresh_end_time);
          dbms_sql.column_value(var_cursor,19,rec_mview.staleness);
          dbms_sql.column_value(var_cursor,20,rec_mview.after_fast_refresh);
          dbms_sql.column_value(var_cursor,21,rec_mview.unknown_prebuilt);
          dbms_sql.column_value(var_cursor,22,rec_mview.unknown_plsql_func);
          dbms_sql.column_value(var_cursor,23,rec_mview.unknown_external_table);
          dbms_sql.column_value(var_cursor,24,rec_mview.unknown_consider_fresh);
          dbms_sql.column_value(var_cursor,25,rec_mview.unknown_import);
          dbms_sql.column_value(var_cursor,26,rec_mview.unknown_trusted_fd);
          dbms_sql.column_value(var_cursor,27,rec_mview.compile_state);
          dbms_sql.column_value(var_cursor,28,rec_mview.use_no_index);
          dbms_sql.column_value(var_cursor,29,rec_mview.stale_since);
          dbms_sql.column_value(var_cursor,30,rec_mview.num_pct_tables);
          dbms_sql.column_value(var_cursor,31,rec_mview.num_fresh_pct_regions);
          dbms_sql.column_value(var_cursor,32,rec_mview.num_stale_pct_regions);
          dbms_sql.column_value(var_cursor,33,rec_mview.segment_created);
          dbms_sql.column_value(var_cursor,34,rec_mview.evaluation_edition);
          dbms_sql.column_value(var_cursor,35,rec_mview.unusable_before);
          dbms_sql.column_value(var_cursor,36,rec_mview.unusable_beginning);
          dbms_sql.column_value(var_cursor,37,rec_mview.default_collation);
          dbms_sql.column_value(var_cursor,38,rec_mview.on_query_computation);
            
              PIPE ROW(rec_mview);
            END;
          END LOOP;

          dbms_sql.close_cursor(var_cursor);
          
          RETURN;
        EXCEPTION 
          WHEN OTHERS THEN
            dbms_output.put_line(SQLERRM);

            IF dbms_sql.is_open(var_cursor)
            THEN
              dbms_sql.close_cursor(var_cursor);
            END IF;
            
            RAISE;
        END;
        /

        CREATE OR REPLACE VIEW dba_mviews_ext
        AS
        SELECT v.owner,
               v.mview_name,
               x.*
          FROM dba_mviews v
               CROSS APPLY TABLE(f_dba_mviews_ext(v.owner,v.mview_name)) x
         ORDER BY owner,mview_name
        /
        CREATE OR REPLACE VIEW all_mviews_ext
        AS
        SELECT v.owner,
               v.mview_name,
               x.*
          FROM all_mviews v
               CROSS APPLY TABLE(f_dba_mviews_ext(v.owner,v.mview_name)) x
         ORDER BY owner,mview_name
        /
        CREATE OR REPLACE VIEW user_mviews_ext
        AS
        SELECT USER owner,
               v.mview_name,
               x.*
          FROM user_mviews v
               CROSS APPLY TABLE(f_dba_mviews_ext(USER,v.mview_name)) x
         ORDER BY mview_name
        /

        GRANT select ON DBA_MVIEWS_EXT TO select_catalog_role;
        GRANT select ON ALL_MVIEWS_EXT TO public;
        GRANT select ON USER_MVIEWS_EXT TO public;
        CREATE PUBLIC SYNONYM DBA_MVIEWS_EXT FOR DBA_MVIEWS_EXT;
        CREATE PUBLIC SYNONYM ALL_MVIEWS_EXT FOR ALL_MVIEWS_EXT;
        CREATE PUBLIC SYNONYM USER_MVIEWS_EXT FOR USER_MVIEWS_EXT;

现在你可以简单地使用常规SQL查询：

SELECT *
  FROM all_mviews_ext
 WHERE UPPER(query) LIKE '%CUSTOMER%'

同样的技术也可以用于dba_views。对于其他更短的LONG，永远不会超过4KB，就像你在dba_tab_partitions/subpartitions，dba_tab_columns，dba_ind_expressions等中找到的那样。您可以通过将LONG转换为varchar2(4000)来简化，这可以通过简单的PL/SQL变量赋值来完成，因此不需要dbms_sql。这段代码要短得多，但同样的原则适用：使用PL/SQL转换为可搜索的数据类型，并使用 Package 该函数的替换视图。

赞(0）分享  回复(0）举报  2023-06-22
whlutmcx

whlutmcx2#

LONG数据类型在SQL中使用时有很多限制，因此不能直接将任何函数应用于LONG，也不能使用任何 predicate ，但null测试除外（尽管 *Oracle Corporation强烈建议将LONG列转换为LOB列 * 至少从版本8.2开始（24年），并 * 尽快 * 从版本9.2开始（21年），但他们仍然在数据字典中保留此类型）。
但是可以通过XML传递此数据类型并反序列化为CLOB，这将允许暴露CLOB的所有SQL功能。你可以使用DBMS_XMLGEN包来实现。

create view v_test as
select 'some text' as res
from dual
select
  v.owner,
  v.view_name,
  v.text,
  v.text_length
from all_views v
  join xmltable(
    '/ROWSET/ROW'
    passing dbms_xmlgen.getxmltype('
      select view_name, owner, text
      from all_views
      where text is not null')

    columns
      view_name varchar2(200),
      owner varchar2(200),
      text clob
  ) flt
  on v.view_name = flt.view_name
  and v.owner = flt.owner
where flt.text like '%some%text%'

| 所有者|查看_名称|正文|文本_长度|
| - -----|- -----|- -----|- -----|
| FIDDLE_SHCEJNRFMOROSMIQCFFB| V_TEST|选择'some text'作为res关于Dual|三十五|
fiddle

赞(0）分享  回复(0）举报  2023-06-22
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com