CODEWITHSUNDEEP
sqloracle-databaseplsql
Danny
Danny

Reputation: 47

SQL: Error function returned without value

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.

Upvotes: 0

Views: 487

Answers (4)

Sabarish Mahalingam
Sabarish Mahalingam

Reputation: 145

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;

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

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;
/

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 2

user5683823
user5683823

Reputation:

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;

Upvotes: 1

Related Questions