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

Question

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!

Thanks! But I'm getting another error 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: — DeaIss, Jul 31 '13 at 15:30
what do you intend to do in your where clause `FROM USERS WHERE username1 = o_username;` you are comparing your in and out parameters? if you have a column name called `username1` then you should use table alias — Jafar Kofahi, Jul 31 '13 at 15:49

score 3 · Accepted Answer · answered Jul 31 '13 at 15:52

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;

score 1 · Answer 2 · answered Jul 31 '13 at 15:28

1

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

answered Jul 31 '13 at 15:28

Barbara Laird

12,599
2
44
57

I did that, but I am getting another error message (Edited into original question) – DeaIss Jul 31 '13 at 15:31
1

See Jafar's comment. Your where clause is wrong. FROM USERS WHERE username1 = o_username should be replaced with FROM USERS WHERE username1 = USERNAME_FIELD_IN_TABLE (whatever that is). – Barbara Laird Jul 31 '13 at 16:33

score 0 · Answer 3 · answered Jul 31 '13 at 15:50

0

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
......

answered Jul 31 '13 at 15:50

Harshit

560
1
5
15

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

3 Answers3