Login form using Oracle Stored Procedure with return value

kerek2

Hi Sir, I'm trying to use oracle Stored Produce with my login form as below :-

package cms is
-- 1 => OK, 0 => ERROR

function validateUser(  username    in  cms\_user.cu\_username%TYPE,
                        passwd      in  varchar2,
                        errmsg      out varchar2 )
                return number;

END;

Here my Vb.Net Code :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    conn.Open()


    Dim oracmd As New OracleCommand
    oracmd.Connection = conn
    oracmd.CommandText = "cms.validateUser( username, passwd, msg )"
    oracmd.CommandType = CommandType.StoredProcedure
    oracmd.Parameters.Add(New OracleParameter("username", OracleType.VarChar)).Value = TxtUsername.Text.Trim
    oracmd.Parameters.Add(New OracleParameter("passwd", OracleType.VarChar)).Value = TxtPassword.Text.Trim
    '  oracmd.Parameters.Add(New OracleParameter("msg", OracleType.VarChar)).Value = Label1.Text.Trim
  

    oracmd.Parameters.Add("msg", OracleType.VarChar).Value = Label1.Text
   

    oracmd.Parameters("msg").Direction = ParameterDirection.Output
    oracmd.ExecuteNonQuery()
    conn.Close()



End Sub

and got this Error :

ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Can anyone guide me plz....tq

Richard Deeming

Not a solution to your question, but you seem to be storing your users' passwords in plain text. That's a huge security problem, which you need to fix ASAP. Secure Password Authentication Explained Simply[^] Salted Password Hashing - Doing it Right[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Victor Nijegorodov

kerek2 wrote:

and got this Error :

ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Did you read something about these ORA-06550 and other errors? Like [Oracle / PLSQL: ORA-06550 Error Message](https://www.techonthenet.com/oracle/errors/ora06550.php)

CHill60

In your Stored Procedure you are attempting to use the type (%TYPE) cms_user.cu_username but you haven't supplied the definition for that type. In your VB code you are trying to pass username as a VarChar Try changing your stored procedure to be

function validateUser(username in varchar2,..

and see if that has any effect.

kerek2

sir, My password in DB is encrypted, what i try to do is just to check whether the username n password correct it will return the value 0 or 1, I'm blank how to do , plz help me ,tq

kerek2

Yup already read it, for info...my password in Db is encrpted, so i try to use stored procedure to check whether the username n password is correct or not and will return the value 0 or 1, Blank to do it, plz guide me sir, tq

Richard Deeming

"Encrypted" passwords are only marginally better than passwords stored in plain-text. Passwords need to be stored using a salted one-way hash, with a unique salt per record. Read the articles I linked to in my previous reply for details.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer