SQL> CREATE OR REPLACE FUNCTION count_line_feeds (p_id IN NUMBER)
  2  RETURN NUMBER
  3  IS
  4      l_text               VARCHAR2(32767);
  5      l_lines              NUMBER;
  6      LINE_FEED   CONSTANT VARCHAR2(10) := CHR(10);
  7  BEGIN
  8      SELECT s.column_three
  9      INTO   l_text
 10      FROM   scott.sample   s
 11      WHERE  s.column_one = p_id;
 12      
 13      IF (l_text IS NULL) THEN
 14          l_lines := 0;
 15      ELSE
 16          l_lines := ((LENGTH(l_text)
 17                       -
 18                       LENGTH(REPLACE(l_text,LINE_FEED))
 19                      )
 20                      /
 21                      LENGTH(LINE_FEED));
 22       END IF;
 23       RETURN (l_lines);
 24   EXCEPTION
 25      WHEN NO_DATA_FOUND THEN
 26          RAISE_APPLICATION_ERROR (
 27              -20010
 28          ,   'Error, no rows in SAMPLE table have a primary '
 29          ||  'key equal to '
 30          ||  TO_CHAR(p_id)
 31          ||  '.');
 32      WHEN TOO_MANY_ROWS THEN
 33          RAISE_APPLICATION_ERROR (
 34              -20020
 35          ,   'Error, multiple rows in SAMPLE table have a '
 36          ||  'primary key of '
 37          ||  TO_CHAR(p_id)
 38          ||  '.');        
 39      WHEN OTHERS THEN
 40          RAISE;
 41  END count_line_feeds;
 42  /
  
Function created.
  
SQL> CREATE TABLE sample (column_one NUMBER, column_two VARCHAR2(10), column_three VARCHAR2(2000));
    
Table created.
    
SQL> INSERT INTO sample VALUES (1,'nada','a' || CHR(10) || 'b' || CHR(10) || 'c' || CHR(10) || 'd');
    
1 row created.
   
SQL> INSERT INTO sample VALUES (2,'zip','efghijk');
   
1 row created.
   
SQL> INSERT INTO sample VALUES (3,NULL,NULL);
   
1 row created.
   
SQL> COMMIT;
   
Commit complete.
   
SQL> SELECT count_line_feeds(1) FROM DUAL;
   
COUNT_LINE_FEEDS(1)
-------------------
                  3
   
SQL> SELECT count_line_feeds(2) FROM DUAL;
   
COUNT_LINE_FEEDS(2)
-------------------
                  0
   
SQL> SELECT count_line_feeds(3) FROM DUAL;
     
COUNT_LINE_FEEDS(3)
-------------------
                  0
     
SQL> SELECT count_line_feeds(4) FROM DUAL;
SELECT count_line_feeds(4) FROM DUAL
       *
ERROR at line 1:
ORA-20010: Error, no rows in SAMPLE table have a primary key equal to 4.
ORA-06512: at "SCOTT.COUNT_LINE_FEEDS", line 26
ORA-06512: at line 1
   
   
SQL> INSERT INTO sample VALUES (3,NULL,'1' || CHR(10) || '2');
    
1 row created.
   
SQL> COMMIT;
   
Commit complete.
   
SQL> SELECT count_line_feeds(3) FROM DUAL;
SELECT count_line_feeds(3) FROM DUAL
       *
ERROR at line 1:
ORA-20020: Error, multiple rows in SAMPLE table have a primary key of 3.
ORA-06512: at "SCOTT.COUNT_LINE_FEEDS", line 33
ORA-06512: at line 1
   
   
SQL>