Oracle 12C New Feature : Listagg function overflow improved

1. If the query exceeds 4000 char, oracle will throw error to caller.

SELECT
    LISTAGG(table_name,',')
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

2. "On Overflow Truncate" option will truncate the char exceeds 4000 char, and suffix "..." & count of fields that were truncated.
   
SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE)
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;
 
3. "On Overflow Error" will throw the error in case of the concatenated string exceeds 4000 char

SELECT
    LISTAGG(table_name,',' ON OVERFLOW ERROR)
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

4. Optionally, default message for overflow can be embedded as the end of result. ( example given below)

SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE '*** MORE DATA EXISTS ***' )
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

5. The count suffix can be explicitly specified with "WITH COUNT" option. ( Through this is the default)

SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE '*** MORE DATA EXISTS ***' WITH COUNT )
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

6. "Without count" will not suffix the count at the end of the string.

SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE '*** MORE DATA EXISTS ***' WITHOUT COUNT )
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;


Here is the video that talks about the feature usage.

Oracle 12C New Feature : Table names greater than 30 char ( Up to 128 Char)

From Oracle 12.2 Version onwards, developer can create objects with name more than 30 char ( up to 128 char). This video talks about the new feature, and the parameter you need to know incase if the feature is not working in 12.2.

Loading CLOB data into oracle table using SQL Loader

SQL Loader utility can be used to load data from flat files into oracle tables. This utility also supports loading the content of files into LOB data type columns as well. This utility comes handy whenever you want load huge amount of text data from files into LOB columns. This post shows how to load the content of text file into CLOB column. For demo purpose, let us consider we want to load content of three text files ( "1_SQL_Loader_details.txt", "2_External_table_details.txt" & "3_UTL_File_details.txt" ) into table. So to capture the file name, and its content we might need two columns, to hold name and content respectively.

Here is the table creation script.

To load the data using SQL Loader utility, we need the data file and control file.
"Data file" contains the information on "what" to load, and "Control File" contains the information about "where & how" to load.

Given below the content of data file. (data_file.txt)

1,1_SQL_Loader_details.txt 2,2_External_table_details.txt 3,3_UTL_File_details.txt

Given below the content of control file. (control_file.ctl)

LOAD DATA INFILE 'data_file.txt'    INTO TABLE file_content_tab    FIELDS TERMINATED BY ','    (s_no    ,     file_name    CHAR(100),     file_data    LOBFILE(file_name) TERMINATED BY EOF)

Upon running sql loader utility for above control file, the data from data files would be read, and loaded into the target table specified.
On successful completion of load, all the contents of the file would be loaded into CLOB column.

Here is the vedio with real time example.