2 SQL functions Dbms_lob.substr and Wm_concat

Turn from:

http://blog.csdn.net/wenzhongyan/article/details/50315473

http://blog.csdn.net/ojerryzuo/article/details/53927057

1. Convert clob field to VARCHAR2 type by DBMS_LOB.SUBSTR ()

When querying the Dba_stat_extensions view, where the extension field is the CLOB type, it cannot be displayed directly through the SELECT statement, as follows:

Need to convert by DBMS_LOB.SUBSTR ()

SELECT owner, A.table_name,trim (Dbms_lob.substr (extension,4000)) as extension from Dba_stat_extensions A is displayed as follows:

2.

First let's take a look at this magical function wm_concat (column name), which separates the column values with "," and displays them in a row, and then the example below to see how this magical function applies to prepare test data sql> CREATE TABLE test (ID Number,name varchar2 (20)); sql> INSERT INTO test values (1, ' a '); sql> INSERT INTO test values (1, ' B '); sql> INSERT INTO test values (1, ' C '); sql> INSERT INTO test values (2, ' d '); sql> INSERT INTO test values (2, ' e '); Sql> commit; effect 1: Row to column sql> select Wm_concat (name) from test; Wm_concat (NAME)-------------------------------------------------------------------------A,b,c,d,e effect 2: Replace the comma in the result with the "|" Sql> Select Replace (wm_concat (name), ', ', ' | ') from test; REPLACE (Wm_concat (NAME), ', ', ' | ') -----------------------------------------------------------------------A|b|c|d|e Effect 3: Group by ID merge namesql> Select ID , Wm_concat (name) name from test Group by id;id name----------------------------------------1 a,b,c2 d,e Lazy Extension usage: case: I'm going to write a view like "Create or Replace view as Select field 1,... field from TableName ", the base table has more than 50 fields, if it is too cumbersome to write by hand, is there any easy way? Sure, look at me. If you apply wm_concat to make this requirement simple sql> Select ' Create or Replace viewAs SELECT ' | | Wm_concat (column_name) | | ' From dept ' from User_tab_columns where table_name= ' dept '; ' Createorreplaceviewasselect ' | | Wm_concat (column_name) | | ' Fromdept '--------------------------------------------------------------------------------Create or replace view as Select Deptno,dname,loc from Dept

2 SQL functions Dbms_lob.substr and Wm_concat