Function is Compiled but in the time of execution Only return first letter of the string-CodePudding

/* Write a function to insert the string in a table which will display the character of string If You enter Sameer then output will be

set SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION f1(c in varchar2)
                  return varchar2
is
a number := 0;
b varchar2(20);
var_len number := length(c);
begin
     loop
         a := a   1;
        select SUBSTR(c, a,1) into b from dual;
           EXIT when  a <= var_len;
           dbms_output.put_line(b);

    END LOOP;
               return b;
END;

SELECT f1('Sameer') FROM DUAL;

CodePudding user response：

set SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION f1(c in varchar2)
                  return varchar2
is
mRet VarChar2(50) := '';
begin
     FOR i in 1..Length(c) LOOP
        mRet := mRet || SubStr(c, i, 1) || Chr(10);
    END LOOP;
    return RTRIM(mRet, Chr(10));
END;

SELECT f1('Sameer') FROM DUAL;
--  
--  R e s u l t
--      
--  F1('SAMEER')
--  ------------
--  S           
--  a           
--  m           
--  e          
--  e          
--  r

CodePudding user response：

Your code (when fixed, as Alex suggested), displays desired output (all letters in separate rows), but function returns only the last letter:

SQL> set SERVEROUTPUT ON;
SQL> CREATE OR REPLACE FUNCTION f1(c in varchar2)
  2    return varchar2
  3  is
  4    a number := 0;
  5    b varchar2(20);
  6    var_len number := length(c);
  7  begin
  8    loop
  9       a := a   1;
 10       select SUBSTR(c, a,1) into b from dual;
 11       dbms_output.put_line(b);
 12       EXIT when  a = var_len;
 13    END LOOP;
 14    return b;
 15  END;
 16  /

Function created.

SQL> SELECT f1('Sameer') FROM DUAL;

F1('SAMEER')
--------------------------------------------------------------------------------
r           --> this is what function returns

S           --> this is result
a           --> of the
m           --> DBMS_OUTPUT.PUT_LINE
e
e
r
SQL>

Though, that's not what you wanted - you want to return such an output. One option is to concatenate all b values with the line feed character (chr(10)):

SQL> CREATE OR REPLACE FUNCTION f1(c in varchar2)
  2    return varchar2
  3  is
  4    a number := 0;
  5    b varchar2(20);
  6    var_len number := length(c);
  7  begin
  8    loop
  9       a := a   1;`enter code here`
 10       b := b || SUBSTR(c, a,1) || chr(10);
 11       EXIT when  a = var_len;
 12    END LOOP;
 13    return b;
 14  END;
 15  /

Function created.

SQL> SELECT f1('Sameer') FROM DUAL;

F1('SAMEER')
--------------------------------------------------------------------------------
S           --> this is return value, a "single" value, not 
a           --> separate letters, each in its own row
m
e
e
r


SQL>

If you run it in SQL Developer, you won't see it properly unless you double click the result (which is the "Sameer") word, then click the pencil button and here's your result.

Also, consider somewhat simpler option:

SQL> create or replace function f1 (c in varchar2)
  2    return varchar2
  3  is
  4  begin
  5    return regexp_replace(c, '(.)', '\1' ||chr(10));
  6  end;
  7  /

Function created.

SQL> SELECT f1('Sameer') FROM DUAL;

F1('SAMEER')
--------------------------------------------------------------------------------
S
a
m
e
e
r


SQL>