Oracle 12C With Clause Enhancements

Functions can be defined in the "WITH" clause and can be used in select statement from 12.1 onwards.

       

WITH
    FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER
        AS
    BEGIN
        RETURN power(p_no,3);
    END;

SELECT
    100 AS num,
    fn_cube(10)
FROM
    dual;
/

       
 

Another Example of "WITH" clause function

       

WITH
    FUNCTION avg_sal (p_deptno NUMBER) RETURN NUMBER AS
        v_avg_sal   NUMBER;
    BEGIN
        SELECT
            AVG(sal)
        INTO v_avg_sal
        FROM
            emp
        WHERE
            deptno = p_deptno;

        RETURN v_avg_sal;
    END avg_sal;

SELECT
    deptno,
    dname,
    loc,
    round(avg_sal(deptno) ) avg_sal -- Calling the function
FROM
    dept;
/

       
 

No DMLs are allowed in function while using in select statements. However DMLs can be used with autonomous_transaction functions.

       

CREATE TABLE temp_1 (
    col1   NUMBER
);

WITH
    FUNCTION avg_sal (p_deptno NUMBER) RETURN NUMBER AS
        PRAGMA autonomous_transaction;
        v_avg_sal   NUMBER;
    BEGIN
        SELECT
            AVG(sal)
        INTO v_avg_sal
        FROM
            emp
        WHERE
            deptno = p_deptno;

        INSERT INTO temp_1 VALUES ( 1 );

        COMMIT;
        RETURN v_avg_sal;
    END avg_sal;

SELECT
    deptno,
    dname,
    loc,
    round(avg_sal(deptno) ) avg_sal -- Calling the function
FROM
    dept;
/

       
 

Example of using procedure and function in WITH clause, however procedures cannot be called directly in select statements. Procedure can be accessed via the calling functions as show below.

       

WITH
    PROCEDURE pr_cube (p_no IN OUT NUMBER)
        AS
    BEGIN
        p_no := power(p_no,3);
    END;

    FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER AS
        lv_num   NUMBER := p_no;
    BEGIN
        pr_cube(lv_num);
        RETURN lv_num;
    END;

SELECT
    100 AS num,
    fn_cube(10)
FROM
    dual;
/

       
 

       

WITH
    PROCEDURE pr_cube (p_no IN OUT NUMBER) AS
        PRAGMA autonomous_transaction;
    BEGIN
        p_no := power(p_no,3);
        INSERT INTO temp_1 VALUES ( 1 );

        COMMIT;
    END;

    FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER AS
        lv_num   NUMBER := p_no;
    BEGIN
        pr_cube(lv_num);
        RETURN lv_num;
    END;

SELECT
    100 AS num,
    fn_cube(10)
FROM
    dual;
/

       
 

Oracle 18C JSON_EQUAL function

select case
when json_equal('{}','{   }') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;

select case
when json_equal('{"A":null}','{"A":""}') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;

select case
when json_equal('{"EMPNAME":"SCOTT"}','{"EMPNAME":"SCOTT"}') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;

select case
when json_equal('{"EMPNAME":"SCOTT"}','{"EMPNAME":"Scott"}') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;

select case
when json_equal('{"EMPID":"1000","EMPNAME":"KING"}','{"EMPID":"1000","EMPNAME":"KING"}') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;

select case
when json_equal('{"EMPID":"1000","EMPNAME":"KING"}','{"EMPNAME":"KING","EMPID":"1000"}') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;

select case
when json_equal('{"NUMLIST":[1,2,3,4,5,6]}','{"NUMLIST":[1,2,3,4,5,6]}') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;

select case
when json_equal('{"NUMLIST":[1,2,3,4,5,6]}','{"NUMLIST":[6,5,4,3,2,1]}') then 'SAME JSON' else 'DIFFERENT JSON' end JSON_COMP_RESULT
from dual;



CREATE TABLE json_sample_data_table (
    s_no                 NUMBER NOT NULL,
    json_1               VARCHAR2(100),
    json_2               VARCHAR2(100),
    CONSTRAINT json_chk_1 CHECK ( json_1 IS JSON ),
    CONSTRAINT json_chk_2 CHECK ( json_2 IS JSON )
);

insert into json_sample_data_table values(1,'{}','{  }');
insert into json_sample_data_table values(2,'{"A":null}','{"A":""}');
insert into json_sample_data_table values(3,'{"A":""}','{"A":""}');
insert into json_sample_data_table values(4,'{"EMPNAME":"SCOTT"}','{"EMPNAME":"SCOTT"}');
insert into json_sample_data_table values(5,'{"EMPID":"1000","EMPNAME":"KING"}','{"EMPID":"1000","EMPNAME":"KING"}');
insert into json_sample_data_table values(6,'{"EMPID":"1000","EMPNAME":"KING"}','{"EMPNAME":"KING","EMPID":"1000"}');
insert into json_sample_data_table values(7,'{"NUMLIST":[1,2,3,4,5,6]}','{"NUMLIST":[1,2,3,4,5,6]}');
insert into json_sample_data_table values(8,'{"NUMLIST":[1,2,3,4,5,6]}','{"NUMLIST":[6,5,4,3,2,1]}');

commit;


select * from json_sample_data_table
where json_1 = json_2;

select * from json_sample_data_table
where json_equal(json_1,json_2);

select * from json_sample_data_table
where not json_equal(json_1,json_2);

select s_no, json_1,json_2,
case
when json_equal(json_1,json_2)
   then 'SAME JSON'
   else 'DIFFERENT JSON'
end JSON_COMP_RESULT
from json_sample_data_table;

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.