CODEWITHSUNDEEP
sqloracle-databasestored-proceduresproceduredeclare
DeaIss
DeaIss

Reputation: 2585

Why does this basic 'Select Into' stored procedure not work?

I'm running Oracle SQL developer and I've got the following Stored Procedure. I'm quite new to this but really not sure why this isn't working:

CREATE OR REPLACE PROCEDURE CHECKDUPLICATE(
       username1 IN USERS.USERNAME%TYPE,
       o_username OUT USERS.USERNAME%TYPE
)

IS
BEGIN

  SELECT USERNAME
  INTO o_username
  FROM USERS WHERE username1 = o_username;

END;

When I try to call it:

DECLARE
   o_username USERS.USERNAME%TYPE;
BEGIN

   CHECKDUPLICATE('Jacklin', o_username);

   DBMS_OUTPUT.PUT_LINE('username :  ' || o_username);

END;

I get the error message: 

Error starting at line 1 in command:
DECLARE
   o_username USERS.USERNAME%TYPE;
BEGIN

   CHECKDUPLICATE(Jacklin, o_username);

   DBMS_OUTPUT.PUT_LINE('username :  ' || o_username);

END;
Error report:
ORA-06550: line 5, column 19:
PLS-00201: identifier 'JACKLIN' must be declared
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

What does it mean by "Identifier 'Jacklin' must be declared? (Table is called USERS, and column name is called USERNAME). Any help would be appreciated.

EDIT** I put Jacklin in quotes, and I get this message now:

Error report:
ORA-01403: no data found
ORA-06512: at "L13JAV04.CHECKDUPLICATE", line 9
ORA-06512: at line 6
01403. 00000 -  "no data found"
*Cause:    
*Action:

Even though Jacklin does it exist in the database!

Upvotes: 0

Views: 1151

Answers (3)

Justin Cave
Justin Cave

Reputation: 231791

Once you quote 'Jacklin' so that it's treated as a string literal rather than an identifier, your SQL statement doesn't look right.

  SELECT USERNAME
  INTO o_username
  FROM USERS 
  WHERE username1 = o_username;

My wager is that you want to use the input parameter in your WHERE clause, not the output parameter.

  SELECT USERNAME
  INTO o_username
  FROM USERS 
  WHERE username1 = username;

It doesn't make sense to check the value of an output parameter when you haven't done anything to initialize it.

But your code still doesn't seem to make sense. A SELECT INTO will throw an error if anything other than 1 row is returned. If your query returns 0 rows, you'll get a NO_DATA_FOUND exception. If your query returns more than 1 row, you'll get a TOO_MANY_ROWS exception. Your procedure is named CheckDuplicate so I'm guessing that it's purpose is to check whether a particular username already exists in the table rather than trying to insert it and catching the unique constraint violation error. If that is the intention of your code

  • You probably want it to be a function
  • You probably don't want to return the username
  • You probably want to return an indicator of whether the username already exists

My guess, therefore, is that you would want something like

create or replace function isAvailable( p_username IN USERS.USERNAME%TYPE )
  return Boolean
is
  l_username USERS.USERNAME%TYPE;
begin
  select username
    into l_username
    from users
   where username = p_username;
  return false;
exception
  when no_data_found
  then
    return true;
end;

Upvotes: 3

Harshit
Harshit

Reputation: 560

There would have been no user by name of "Jacklin" thats why its giving you the error. Please add an exception at the end

WHEN NO_DATA_FOUND 
THEN
......

Upvotes: 0

Barbara Laird
Barbara Laird

Reputation: 12717

You need to put Jacklin within quotes for it to be treateed as a string. Otherwise the parser thinks it's a field name.

Upvotes: 1

Related Questions