SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE function exitfunc(myString VARCHAR2)
2 RETURN NUMBER IS
3 v_current_position INTEGER := 1;
4 v_counter NUMBER := 0;
5 BEGIN
6 WHILE v_current_position <= LENGTH(myString) LOOP
7 IF SUBSTR(myString,v_current_position,1) != ' ' THEN
8 v_counter := v_counter + 1;
9 ELSE
10 NULL;
11 END IF;
12 v_current_position := v_current_position + 1;
13 EXIT WHEN SUBSTR(myString,v_current_position,1) = ' ';
14 END LOOP;
15 RETURN v_counter ;
16 END exitfunc;
17 /
Function created.
SQL>
SQL> DECLARE
2 v_MYTEXT VARCHAR2(20) := 'THIS IS A TEST';
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Total count is ' || exitfunc(v_MYTEXT));
5 END;
6 /
Total count is 4
PL/SQL procedure successfully completed.
SQL>