Why do I get no result with this block?-CodePudding

I am working on a function that should return the first and last name from a full name, it's like this:

create or replace package body pkg_format_eagle_data as
    function parse_firstname(p_fullname supplier.contactname%type) return varchar2 is
        v_firstname supplier.contactname%type;
    begin
        v_firstname := substr(p_fullname,1,instr(p_fullname,' ',1,1)-1);
        return v_firstname;
    end PARSE_FIRSTNAME;

    function PARSE_LASTNAME(p_fullname supplier.contactname%type) return varchar2 is
        v_lastname supplier.contactname%type;
    begin
        v_lastname := substr(p_fullname,instr(p_fullname,' ',1,1) 1,length(p_fullname) - instr(p_fullname,' ',1,1));
    return v_lastname;
    end PARSE_LASTNAME;
end pkg_format_eagle_data;

It compiled successfully, but when I call it in the following block, it returns nothing. In this block, I created a variable called fullName to store the cursor, and loop through the cursor.

declare 
fullName supplier.contactname%type;

cursor list_supplier is
select contactname 
from supplier;

begin
open list_supplier;
loop
    fetch list_supplier into fullName;
    dbms_output.put(pkg_format_eagle_data.PARSE_FIRSTNAME(fullName));
    dbms_output.put_line(pkg_format_eagle_data.PARSE_LASTNAME(fullName));
    exit when list_supplier%notfound;
end loop;
close list_supplier;
end;

I've tried the condition of function in SELECT and they work. May I ask that where I made a mistake? Here's a sample table:

create table supplier
(
    contactname varchar2(50);
);

INSERT INTO supplier (contactname)
VALUES ('Alice Bob');
INSERT INTO supplier (contactname)
VALUES ('Tim Tom');
INSERT INTO supplier (contactname)
VALUES ('Sam Bob');

CodePudding user response：

I have verified your code in Oracle DB and it's output is shown below.

AliceBob
TimTom
SamBob
SamBob

Which is as expected except that the repeated lines are due to the placement of exit, which should come immediately after fetch statement.

Only reason I can think of you missing to set serveroutput on if its SQL* Plus or equivalent in any other tool you are using.