How many rows in a Clob

If you are not familiar with the ‘WITH’ statement, I am just using it to mock up a table that has a row_id and a clob value – feel free to run this bit (from the select within the WITH clause) in isolation to get the rows with the CR/LF’s.
There are several ways, if you need to extract each row, I expect as separate text you may find working with Option 3 the most productive.

Option 1:
Using LENGTH is just working out how many CHR(13)’s there are

WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
SELECT table_id, LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (13), '')) + 1
FROM clob_tab

Option 2:

WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
select table_id, LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (10), '')) + 1
from clob_tab

Option 3:

If you’ve not used CONNECT BY LEVEL before, it is worth reading and working through the example in the oracle documentation..

WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
SELECT table_id,
MAX (row_num)
FROM (SELECT table_id,
clob_val,
LEVEL AS row_num
FROM clob_tab
CONNECT BY LEVEL <= LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (10), '')) + 1)
GROUP BY table_id

Oracle - Testing UTL_FILE

CREATE OR REPLACE PROCEDURE
utl_file_test_write (PATH IN VARCHAR2,
filename IN VARCHAR2,
firstline IN VARCHAR2,
secondline IN VARCHAR2
)
AUTHID CURRENT_USER
IS
output_file UTL_FILE.file_type;
v_path all_directories.directory_path%TYPE;
BEGIN
SELECT directory_path
INTO v_path
FROM all_directories
WHERE UPPER (directory_name) = UPPER (PATH) AND ROWNUM = 1;

DBMS_OUTPUT.put_line ('Opening file ' || RTRIM (v_path, '/') || '/' || filename);
output_file := UTL_FILE.fopen (PATH, filename, 'W');

DBMS_OUTPUT.put_line ('Writing the 1st record "' || firstline || '"');
UTL_FILE.put_line (output_file, firstline);

DBMS_OUTPUT.put_line ('Writing the 2nd record "' || secondline || '"');
UTL_FILE.put_line (output_file, secondline);

DBMS_OUTPUT.put_line ('Closing the file');
UTL_FILE.fclose (output_file);

DBMS_OUTPUT.put_line ('Complete!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;

/

Three Peaks Challenge

For some mad reason I thought walking the three giants in the UK within 24 hours would be a good idea!!

Better off visiting the Bingham 3 Peaks Blog - http://bingham3peaks.blogspot.com