SQL>
SQL> CREATE OR REPLACE function exitfunc(p_pass_string VARCHAR2)
2 RETURN NUMBER IS
3 v_MYCOUNTER INTEGER := 1;
4 v_COUNTNOSP NUMBER := 0;
5 BEGIN
6 WHILE v_MYCOUNTER <= LENGTH(p_PASS_STRING) LOOP
7 IF SUBSTR(p_PASS_STRING,v_MYCOUNTER,1) != ' ' THEN
8 v_COUNTNOSP := v_COUNTNOSP + 1;
9 ELSE
10 NULL;
11 END IF;
12 v_MYCOUNTER := v_MYCOUNTER + 1;
13 EXIT WHEN SUBSTR(p_PASS_STRING,v_MYCOUNTER,1) = ' ';
14 END LOOP;
15 RETURN v_COUNTNOSP ;
16 END exitfunc;
17 /
Function created.
SQL>
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>
SQL> --