I need to be able to call a stored procedure via ODP.Net. I have set up the procedure as this:
create or replace
PROCEDURE CHANGE_USER_PASSWORD(
sReturnCode Out Varchar2,
sNewPassword IN VARCHAR2,
sUser IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'ALTER USER '|| sUser ||' IDENTIFIED BY '|| sNewPassword;
COMMIT;
END CHANGE_USER_PASSWORD;
The VB.net code is like this:
Function ChangePassword() As String
Dim OraCmd As New OracleCommand()
Dim sReturnCode As String = ""
cn.Open()
OraCmd.Connection = cn
OraCmd.CommandText = "comunits.CHANGE_USER_PASSWORD"
OraCmd.CommandType = CommandType.StoredProcedure
OraCmd.Parameters.Add(sReturnCode, Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.ReturnValue)
OraCmd.Parameters.Add(sNewPassword, Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.Input)
OraCmd.Parameters.Add(sUser, Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.Input)
OraCmd.Parameters.Item("sNewPassword").Value = sNewPassword
OraCmd.Parameters.Item("sUser").Value = sUser
OraCmd.ExecuteNonQuery()
cn.Close()
Return sReturnCode
End Function
sNewPassword and sUser are declared higher up the page and do have the expected values in them. When I step through the parameters.add lines the OraCmd parameters collection increases in count however, when I try to set a parameter value the parameter kicks
an error because the parameter in the collection is still Nothing.
If I comment out the lines where I set the values of the 2 In parameters I get the following error:
Oracle.DataAccess.Client.OracleException was unhandled
DataSource=Oradev
ErrorCode=-2147467259
Message=ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'CHANGE_USER_PASSWORD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Number=6550
Procedure=comunits.CHANGE_USER_PASSWORD
Source=Oracle Data Provider for .NET
That didn't make any difference. On a different site I found a completely different way to change passwords so I'll give the other way a try and see what happens. If it works I'll post the other way here.
It turns out that the problem was an Oracle issue with the ODP.Net and using the latest client with an older version of the Oracle 11 server. Oracle has chosen not to fix this so far and simply say upgrade the database and give us lots of money or go back
to an older ODP. We went back to the older ODP.
Marked as answer by kraznodar on Apr 18, 2012 06:47 PM
kraznodar
Contributor
3332 Points
881 Posts
Problem calling stored procedure for password change via ODP.Net
Dec 09, 2011 09:46 PM|LINK
Hello,
I need to be able to call a stored procedure via ODP.Net. I have set up the procedure as this:
create or replace
PROCEDURE CHANGE_USER_PASSWORD(
sReturnCode Out Varchar2,
sNewPassword IN VARCHAR2,
sUser IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'ALTER USER '|| sUser ||' IDENTIFIED BY '|| sNewPassword;
COMMIT;
END CHANGE_USER_PASSWORD;
The VB.net code is like this:
Function ChangePassword() As String
Dim OraCmd As New OracleCommand()
Dim sReturnCode As String = ""
cn.Open()
OraCmd.Connection = cn
OraCmd.CommandText = "comunits.CHANGE_USER_PASSWORD"
OraCmd.CommandType = CommandType.StoredProcedure
OraCmd.Parameters.Add(sReturnCode, Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.ReturnValue)
OraCmd.Parameters.Add(sNewPassword, Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.Input)
OraCmd.Parameters.Add(sUser, Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.Input)
OraCmd.Parameters.Item("sNewPassword").Value = sNewPassword
OraCmd.Parameters.Item("sUser").Value = sUser
OraCmd.ExecuteNonQuery()
cn.Close()
Return sReturnCode
End Function
sNewPassword and sUser are declared higher up the page and do have the expected values in them. When I step through the parameters.add lines the OraCmd parameters collection increases in count however, when I try to set a parameter value the parameter kicks an error because the parameter in the collection is still Nothing.
If I comment out the lines where I set the values of the 2 In parameters I get the following error:
Oracle.DataAccess.Client.OracleException was unhandled
DataSource=Oradev
ErrorCode=-2147467259
Message=ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'CHANGE_USER_PASSWORD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Number=6550
Procedure=comunits.CHANGE_USER_PASSWORD
Source=Oracle Data Provider for .NET
What am I doing wrong?
Thanks for any help!
Lannie
Contributor
3724 Points
726 Posts
Re: Problem calling stored procedure for password change via ODP.Net
Dec 10, 2011 08:37 PM|LINK
Try ParameterDirection.Output
instead of ReturnValue
kraznodar
Contributor
3332 Points
881 Posts
Re: Problem calling stored procedure for password change via ODP.Net
Dec 12, 2011 04:06 PM|LINK
That didn't make any difference. On a different site I found a completely different way to change passwords so I'll give the other way a try and see what happens. If it works I'll post the other way here.
kraznodar
Contributor
3332 Points
881 Posts
Re: Problem calling stored procedure for password change via ODP.Net
Apr 18, 2012 06:47 PM|LINK
It turns out that the problem was an Oracle issue with the ODP.Net and using the latest client with an older version of the Oracle 11 server. Oracle has chosen not to fix this so far and simply say upgrade the database and give us lots of money or go back to an older ODP. We went back to the older ODP.