SQL: Error function returned without value

Question

I get function returned without error when i run the query, i hope you can help me

CREATE OR REPLACE FUNCTION loginSesion(pPassword VARCHAR2, pUser VARCHAR2) 
RETURN NUMBER

IS
   vPassword VARCHAR2(100);
   vUsername VARCHAR2(100);

BEGIN

   SELECT Person.Username, Person.Password INTO vUsername, vPassword
   FROM Person
   WHERE Person.Password = pPassword and Person.Username = pUser;
   EXCEPTION WHEN NO_DATA_FOUND THEN
       vPassword := NULL;
       vUsername := NULL;
   IF vPassword IS NULL OR vUsername IS NULL THEN
       RETURN FALSE;
   ELSE
       RETURN TRUE;

   END IF;

END loginSesion;

I am new in sql.

And what do you want it to do? You intend to return a NUMBER but you are returning true or false... — Alfabravo
– Alfabravo, Commented Oct 11, 2017 at 19:00
You are only returning from the EXCEPTION block. If the exception is not thrown (the SELECT actually succeeds), then the EXCEPTION block is never entered. — user5683823
– user5683823, Commented Oct 11, 2017 at 19:02
Well change the false by 0 and true by 1, i want to return a number — Danny
– Danny, Commented Oct 11, 2017 at 19:05
@mathguy how is the second part of the exception? To fix the code, please can you help me — Danny
– Danny, Commented Oct 11, 2017 at 19:09
Are you really storing passwords in a table and passing them around in queries? — tbone
– tbone, Commented Oct 11, 2017 at 19:13

user330315 · Accepted Answer · 2017-10-12 06:49:38Z

As others have explained you only return a value if an exception occurred. If everything is OK, you don't return something. That can be fixed using the following code:

CREATE OR REPLACE FUNCTION loginSesion(pPassword VARCHAR2, pUser VARCHAR2) 
  RETURN NUMBER
IS
   vPassword VARCHAR2(100);
   vUsername VARCHAR2(100);
BEGIN
   SELECT Person.Username, Person.Password INTO vUsername, vPassword
   FROM Person
   WHERE Person.Password = pPassword and Person.Username = pUser;

   -- executed when a row was found
   return 1;
EXCEPTION WHEN NO_DATA_FOUND THEN

   -- executed when no row was found
   RETURN 0;
END loginSesion;
/

Using an exception as a replacement for an IF condition is a bit of a code-smell (of course this is a bit subjective).

As you never use the values of the returned row, you can simplify the function to simply count the number of rows.

CREATE OR REPLACE FUNCTION loginSesion(pPassword VARCHAR2, pUser VARCHAR2) 
  RETURN NUMBER
IS
   l_count integer;
BEGIN
   SELECT count(*) 
     into l_count;
   FROM Person
   WHERE Person.Password = pPassword 
     and Person.Username = pUser;

   if l_count = 0 then 
     return 0;
   else 
     return 1;
   end if;

END loginSesion;
/

If the calling code treats zero as "false" and anything greater then zero as "true", then you can remove the if and simply use return l_count;

The count() would also be necessary in case username is not unique (which I would find very strange). Because if there were two users with the same username, the select in the first solution would then throw a "select returned more then one row" exception.

user5683823 · Accepted Answer · 2017-10-11 19:03:43Z

1

Assuming you want to return 1 to represent "true" if the combination of username and password is found, and 0 to represent "false" otherwise, then:

ADD return 1 right after the SELECT and before the EXCEPTION block
ADD return 0 in the EXCEPTION block

You don't need to assign anything in the EXCEPTION block, just say when no data found then return 0;

answered Oct 11, 2017 at 19:03

user5683823

Comments

Arkadiusz Łukasiewicz · Accepted Answer · 2017-10-12 07:06:30Z

Pro Tip. Oracle has something like pls_warrinings. You can turn it on. And lot of stupid mistake can be easy to find.

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL'; - turn on 
ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:ALL'; - turn off

After fix compilation errors from your example.([Error] PLS-00382 (16: 15): PLS-00382: expression is of wrong type )

Next "compilation" shows you

[Warning] PLW-05018 (1: 1): PLW-05018: unit LOGINSESION omitted optional AUTHID clause; default value DEFINER used
[Warning] PLW-06002 (15: 8): PLW-06002: Unreachable code
[Warning] PLW-05005 (1: 1): PLW-05005: subprogram LOGINSESION returns without value at line 18

First warning is not relevant.
PLW-06002: Unreachable code is becouse of this.

EXCEPTION WHEN NO_DATA_FOUND THEN
       vPassword := NULL;
       vUsername := NULL;
   IF vPassword IS NULL OR vUsername IS NULL THEN
       RETURN 0;
   ELSE
       RETURN 1; -- <-- this part is Unreachable (vPassword IS NULL OR vUsername IS null is allways ture)



   END IF;

PLW-05005: subprogram LOGINSESION returns without value at line 18 because value is not returned when select has one row.

Correct version are.

create or replace function loginSesion(pPassword varchar2, pUser varchar2)
  return number
is
    vPassword VARCHAR2(100);
   vUsername VARCHAR2(100);
begin
 SELECT Person.Username, Person.Password INTO vUsername, vPassword FROM Person WHERE Person.Password = pPassword and Person.Username = pUser;
   return 0;
exception
  when no_data_found then
    return 0; 
end loginSesion;
/

create or replace function loginSesion(pPassword varchar2, pUser varchar2)
  return number
is
   vResutl number;
begin
 SELECT count(*) into vResutl  FROM Person WHERE Person.Password = pPassword and Person.Username = pUser;
  return vResutl;
end loginSesion;
/

Sabarish Mahalingam · Accepted Answer · 2017-10-12 09:16:00Z

0

you are returning character instead of Number. So your need to change return type values.

code :

   CREATE OR REPLACE FUNCTION loginSesion(pPassword VARCHAR2, pUser VARCHAR2) 
    RETURN NUMBER

    IS
       vPassword VARCHAR2(100);
       vUsername VARCHAR2(100);

    BEGIN
      BEGIN
       SELECT Person.Username, Person.Password INTO vUsername, vPassword
       FROM Person
       WHERE Person.Password = pPassword and Person.Username = pUser;
       EXCEPTION WHEN NO_DATA_FOUND THEN
           vPassword := NULL;
           vUsername := NULL;
            RETURN 0;
       END;
       IF vPassword IS NULL OR vUsername IS NULL THEN
           RETURN 0;
       ELSE
           RETURN 1;

       END IF;
    exception 
          when others then 
          RETURN 0;
          --dbms_output.put_line('Return' || ' - ' || sqlerrm);
    END loginSesion;

edited Oct 12, 2017 at 9:16

answered Oct 12, 2017 at 3:48

Sabarish Mahalingam

14510 bronze badges

1 Comment

Renato Afonso Over a year ago

You're missing the return clause if the exception isn't thrown.

Collectives™ on Stack Overflow

SQL: Error function returned without value

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related