Oracle – Convert Rows to CSV string and CSV string to Rows – Tablespaces and their Tables example

Whenever a need arises to convert a bunch of rows into a CSV string, typically, we spin up the editor to code a loop to concatenate strings and conversely parse the CSV string out to convert it into rows. Some of us have functions that we re-use. Although functions are better than copying and pasting the same looping logic over and over, they can be inefficient depending on the situation.

Fortunately, there is an easier and simpler way to convert a CSV string to rows and vice versa using a SQL based mechanism which can be used inline.

Rows to CSV (11g and above)

Let us say we wanted to get the list of tablespaces and the associated tables within those tablespaces as a CSV list.

DBA_TABLES view will be used and it looks like this (using SQL*Plus):

(you could use ALL_TABLES if you do not have access to DBA_TABLES)


SQL> DESC DBA_TABLES;
 Name                        Null?    Type
 --------------------------- -------- ----------------------------
 OWNER                       NOT NULL VARCHAR2(30)
 TABLE_NAME                  NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                      VARCHAR2(30)
 CLUSTER_NAME                         VARCHAR2(30)
 IOT_NAME                             VARCHAR2(30)
 STATUS                               VARCHAR2(8)
 PCT_FREE                             NUMBER
 PCT_USED                             NUMBER
 INI_TRANS                            NUMBER
 MAX_TRANS                            NUMBER
 INITIAL_EXTENT                       NUMBER
 NEXT_EXTENT                          NUMBER
 MIN_EXTENTS                          NUMBER
 MAX_EXTENTS                          NUMBER
 PCT_INCREASE                         NUMBER
 FREELISTS                            NUMBER
 FREELIST_GROUPS                      NUMBER
 LOGGING                              VARCHAR2(3)
 BACKED_UP                            VARCHAR2(1)
 NUM_ROWS                             NUMBER
 BLOCKS                               NUMBER
 EMPTY_BLOCKS                         NUMBER
 AVG_SPACE                            NUMBER
 CHAIN_CNT                            NUMBER
 AVG_ROW_LEN                          NUMBER
 AVG_SPACE_FREELIST_BLOCKS            NUMBER
 NUM_FREELIST_BLOCKS                  NUMBER
 DEGREE                               VARCHAR2(40)
 INSTANCES                            VARCHAR2(40)
 CACHE                                VARCHAR2(20)
 TABLE_LOCK                           VARCHAR2(8)
 SAMPLE_SIZE                          NUMBER
 LAST_ANALYZED                        DATE
 PARTITIONED                          VARCHAR2(3)
 IOT_TYPE                             VARCHAR2(12)
 TEMPORARY                            VARCHAR2(1)
 SECONDARY                            VARCHAR2(1)
 NESTED                               VARCHAR2(3)
 BUFFER_POOL                          VARCHAR2(7)
 FLASH_CACHE                          VARCHAR2(7)
 CELL_FLASH_CACHE                     VARCHAR2(7)
 ROW_MOVEMENT                         VARCHAR2(8)
 GLOBAL_STATS                         VARCHAR2(3)
 USER_STATS                           VARCHAR2(3)
 DURATION                             VARCHAR2(15)
 SKIP_CORRUPT                         VARCHAR2(8)
 MONITORING                           VARCHAR2(3)
 CLUSTER_OWNER                        VARCHAR2(30)
 DEPENDENCIES                         VARCHAR2(8)
 COMPRESSION                          VARCHAR2(8)
 COMPRESS_FOR                         VARCHAR2(12)
 DROPPED                              VARCHAR2(3)
 READ_ONLY                            VARCHAR2(3)
 SEGMENT_CREATED                      VARCHAR2(3)
 RESULT_CACHE                         VARCHAR2(7)

The result we are after is something like this (tablespace and a semi-colon separated list of tables in each tablespace)

TABLSPACE_NAME TABLES
BUSINESS_OBJECTS_TAB DS_PENDING_JOB; DS_USER_LIST; OBJ_M_ACTOR; OBJ_M_ACTORDOC; OBJ_M_ACTORLINK; OBJ_M_CATEG; OBJ_M_CHANNEL; OBJ_M_CONNECTDATA; OBJ_M_CONNECTION; OBJ_M_DOCAT; OBJ_M_DOCATVAR; OBJ_M_DOCCATEG; OBJ_M_DOCCST; OBJ_M_DOCUMENTS;
LIVELINK_REP_TBS AGENTCONFIG; AGENTSCHEDULE; AGENTSTATS; ASSIGNEES; AUDITCOLLECTIONSITEMS; BESTBETSDATA; BESTBETSSEARCH; BLOBDATA; BLOBRESDATA; COLLECTIONS; COMPANY_GROUP; COMPONENTSETTINGS; CONTRACT_ISSUES; CONTRACT_SIGNATURE; CT;
RCVCAT AL; BCB; BCF; BDF; BP; BRL; BS; CCB; CCF; CDF; CKP; CONFIG; DB; DBINC; DF; DFATT; OFFR; ORL; RCVER; RLH; RR; RT; SCR; SCRL; TS; TSATT; XCF; XDF
REPORTS_DATA_TAB CML_INCOME; CML_INVESMENTS; ESSDATA_EXCEPTION; ESSDATA_STAGE; IA_INCOME_ADJUSTMENTS; IA_NET_STAT_LIABILITIES; MESSAGE_CENTER; NON_PAM_CASH; PAM_HOLDINGS; PAM_INCOME; REPORT; REPORTCATEGORY; REPORTCLASS; REPORT_FAVORITE; REPORT_HISTORY; RULE; RULEITEM
STATS_DATA_TAB STATS_JOB_CONTROL; STATS_JOB_LOG; STATS_JOB_LOG_DETAIL
SYSTEM CSM$TRIGGERS; OL$; OL$HINTS; OL$NODES
TSA_DATA_TAB TSA_ACTION; TSA_ALLOW
USERS CHAINED_ROWS; DBF_PLAN_TABLE

The query to do this is extremely simple in Oracle 11g and above


SELECT TABLESPACE_NAME, LISTAGG(TABLE_NAME, '; ')
WITHIN GROUP (ORDER BY TABLESPACE_NAME) AS TABLES
FROM DBA_TABLES
WHERE
    --OWNER = 'LIVELINK' /* Change this if necessary */
    ROWNUM < 200
    AND OWNER NOT IN ('SYS','SYSTEM')
GROUP BY TABLESPACE_NAME

For brevity of illustration I have put some limitations in my WHERE clause. Please adjust your own WHERE clause to target what you want to.

The key above is the LISTAGG function. The rest of the statement should be self-explanatory.

CSV string to ROWS (any Oracle version)

WITH
ValsStr
AS
(
    SELECT
        ',' AS Separator
        ,'Alex,Mike,Terry,Joe,Sam' CSVs
    FROM DUAL
)
SELECT SUBSTR (CSVs,
                  INSTR (CSVs,
                         Separator,
                         1,
                         LEVEL)
                + 1,
                  INSTR (CSVs,
                         Separator,
                         1,
                         LEVEL + 1)
                - INSTR (CSVs,
                         Separator,
                         1,
                         LEVEL)
                - 1) Value
FROM
    (
        SELECT
            Separator
            , Separator || CSVs || Separator AS CSVs
        FROM ValsStr
    )
    CONNECT BY LEVEL >
                 LENGTH (CSVs) - LENGTH (REPLACE (CSVs, Separator, ''))

This would produce the result

Value
Alex
Mike
Terry
Joe
Sam

To test, make sure you substitute the highlighted line in the SQL with your own CSV list string.

I have used this technique in a lot of places in my code. It looks complex but it really is not and also, once you have a reference like this post, it is easy to just substitute without having to think too hard!

Advertisements

One thought on “Oracle – Convert Rows to CSV string and CSV string to Rows – Tablespaces and their Tables example

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s