Oracle Sql Function Compiled (with errors)

You have some PL/SQL syntax issues

CREATE or replace FUNCTION updatePassword(CurrentP VARCHAR2,NewPwd VARCHAR2,StudentId VARCHAR2) RETURN VARCHAR2
    is
        getCount integer :=0;
    BEGIN
        Select count(*) into getCount from users where student_id=StudentId and Password=md5(CurrentP);
        if getCount = 1
        then 
        update users set Password=md5(NewPwd) where student_id=StudentId and Password=md5(CurrentP);        
        else
        getCount := 0;
        end if;
    RETURN getCount;
    END;
/

You have a little syntax error. The code should look likes this:

CREATE or replace FUNCTION updatePassword(
  CurrentP in VARCHAR2,
  NewPwd in VARCHAR2,
  StudentId in number) RETURN number
is
    getCount number:=0;
BEGIN
    Select count(*) into getCount from users where student_id=StudentId and Password=md5(CurrentP);
    if getCount = 1
    then 
    update users set Password=md5(NewPwd) where student_id=StudentId and Password=md5(CurrentP);     
    end if;
RETURN getCount;
END;

UPDATE: After a closer look, you have a lot of syntax problems^^ Modified the code.

Suggestions:

• Avoid stand alone functions/procedures. Use the Oracle advantage - packages. Helps to avoid invalidated objects situation;
• When you use PL/SQL - try to stick with the coding standards in PL/SQL.
• Set the correct types of variables.

Here's an example which shows how to do that.

First, test case. The MD5 function doesn't do anything; it just returns the IN parameter's value.

SQL> CREATE TABLE users
  2  (
  3     student_id  VARCHAR2 (10),
  4     password    VARCHAR2 (20)
  5  );

Table created.

SQL> INSERT INTO users (student_id, password)
  2       VALUES ('100', 'Stack');

1 row created.

SQL> CREATE OR REPLACE FUNCTION md5 (par_password IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4  BEGIN
  5     RETURN par_password;
  6  END;
  7  /

Function created.

SQL>

You should use a procedure to modify a password, not a function as you can't perform DML in a function (OK, you can, but you shouldn't. I'll show you show to do that later).

The procedure returns number of updated rows; if you want, you can return getcount (which is number of selected rows).

SQL> CREATE OR REPLACE PROCEDURE updatepassword (p_currentp   IN     VARCHAR2,
  2                                              p_newpwd     IN     VARCHAR2,
  3                                              p_studentid  IN     VARCHAR2,
  4                                              retval          OUT NUMBER)
  5  IS
  6     getcount  INTEGER := 0;
  7  BEGIN
  8     SELECT COUNT (*)
  9       INTO getcount
 10       FROM users
 11      WHERE     student_id = p_studentid
 12            AND password = md5 (p_currentp);
 13
 14     IF getcount = 1
 15     THEN
 16        UPDATE users
 17           SET password = md5 (p_newpwd)
 18         WHERE     student_id = p_studentid
 19               AND password = md5 (p_currentp);
 20
 21        retval := SQL%ROWCOUNT;
 22     END IF;
 23  END;
 24  /

Procedure created.

SQL>

Testing: change password from "Stack" to "Overflow":

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l_retval  NUMBER;
  3  BEGIN
  4     updatepassword ('Stack',
  5                     'Overflow',
  6                     '100',
  7                     l_retval);
  8     DBMS_OUTPUT.put_line ('retval = ' || l_retval);
  9  END;
 10  /
retval = 1

PL/SQL procedure successfully completed.

SQL> SELECT * FROM users;

STUDENT_ID PASSWORD
---------- --------------------
100        Overflow

SQL>

Seems to be OK.

And here's why you can't use a function:

SQL> DROP PROCEDURE updatepassword;

Procedure dropped.

SQL> CREATE OR REPLACE FUNCTION updatepassword (p_currentp   IN VARCHAR2,
  2                                             p_newpwd     IN VARCHAR2,
  3                                             p_studentid  IN VARCHAR2)
  4     RETURN NUMBER
  5  IS
  6     getcount  INTEGER := 0;
  7  BEGIN
  8     SELECT COUNT (*)
  9       INTO getcount
 10       FROM users
 11      WHERE     student_id = p_studentid
 12            AND password = md5 (p_currentp);
 13
 14     IF getcount = 1
 15     THEN
 16        UPDATE users
 17           SET password = md5 (p_newpwd)
 18         WHERE     student_id = p_studentid
 19               AND password = md5 (p_currentp);
 20     END IF;
 21
 22     RETURN getcount;
 23  END;
 24  /

Function created.

SQL> SELECT updatepassword ('Overflow', 'Littlefoot', '100') FROM DUAL;
SELECT updatepassword ('Overflow', 'Littlefoot', '100') FROM DUAL
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.UPDATEPASSWORD", line 16


SQL>

Finally, a function which shows that you can do DML (but you really don't want to do that) - use pragma autonomous_transaction which instructs Oracle to execute code within that function separately from an outside transaction. It requires you to commit (or rollback).

SQL> CREATE OR REPLACE FUNCTION updatepassword (p_currentp   IN VARCHAR2,
  2                                             p_newpwd     IN VARCHAR2,
  3                                             p_studentid  IN VARCHAR2)
  4     RETURN NUMBER
  5  IS
  6     PRAGMA AUTONOMOUS_TRANSACTION;
  7     getcount  INTEGER := 0;
  8  BEGIN
  9     SELECT COUNT (*)
 10       INTO getcount
 11       FROM users
 12      WHERE     student_id = p_studentid
 13            AND password = md5 (p_currentp);
 14
 15     IF getcount = 1
 16     THEN
 17        UPDATE users
 18           SET password = md5 (p_newpwd)
 19         WHERE     student_id = p_studentid
 20               AND password = md5 (p_currentp);
 21     END IF;
 22
 23     COMMIT;
 24
 25     RETURN getcount;
 26  END;
 27  /

Function created.

SQL> SELECT updatepassword ('Overflow', 'Littlefoot', '100') FROM DUAL;

UPDATEPASSWORD('OVERFLOW','LITTLEFOOT','100')
---------------------------------------------
                                            1

SQL> select * from users;

STUDENT_ID PASSWORD
---------- --------------------
100        Littlefoot

SQL>