getting error when calling Oracle SP from VB.NET

Question

Dear Members,

      

I am calling a stored procedure in a package LDAP_AUTH ,this SP will take 3 input arguments which are varchar2.when I am calling the SP I am getting error.The SP is running fine in SQL * Plus. Pls help me on this.Pls mail me at [email protected]

I used cmd.ExecuteNonQuery() i am getting the following error.

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'LOGIN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Stack Trace

"   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)

   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)

   at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)

   at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()

   at Savi.Reports_DataRead.DataRead.ExecSP(String strSQL, OracleParameter[] OracleParam) in C:\Documents and Settings\vsrkrishna\My Documents\Visual Studio 2005\WebSites\SaviReportsWebSite\App_Code\DataLayer\DataRead.vb:line 131

   at Savi.Reports_DataRead.DataRead.check_Login(String strLoginIDDesc, String strPasswordDesc, String strSessionIDDesc) in C:\Documents and Settings\vsrkrishna\My Documents\Visual Studio 2005\WebSites\SaviReportsWebSite\App_Code\DataLayer\DataRead.vb:line 233

   at Savi.HomePage_BL.HomePage.check_LoginDetails(String strLoginIDDesc, String strPasswordDesc, String strSessionIDDesc) in C:\Documents and Settings\vsrkrishna\My Documents\Visual Studio 2005\WebSites\SaviReportsWebSite\App_Code\BusinessLayer\HomePage.vb:line 29

   at HomePage_HomePage.go_Click(Object sender, ImageClickEventArgs e) in C:\Documents and Settings\vsrkrishna\My Documents\Visual Studio 2005\WebSites\SaviReportsWebSite\Modules\HomePage\HomePage.aspx.vb:line 41

   at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e)

   at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument)

   at System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)

   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)

   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)

   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)"

 

************ Procedure Code Start *****************


procedure login(p_user_id in varchar2, p_password in varchar2, p_client_identifier in Varchar2)
as

retval PLS_INTEGER;
ldap_host varchar2(255):=get_host;
ldap_port number:=get_port;
user_id varchar2(100):=p_user_id;
password varchar2(255) := p_password;
base_dn cmm_preference_map_entry.preference_value_1%type := get_base_dn;

username_filter cmm_preference_map_entry.preference_value_1%type := replace(get_ldap_username_filter,'%u',user_id);
group_filter cmm_preference_map_entry.preference_value_1%type := get_ldap_group_member_filter;
my_session DBMS_LDAP.session;
res_message DBMS_LDAP.MESSAGE;
res_attrs DBMS_LDAP.STRING_COLLECTION;
temp_dn VARCHAR2(512);
temp_entry DBMS_LDAP.MESSAGE;
entry_index PLS_INTEGER;

v_string_table v_string_table_ty;
i number:=1;
m number:=1;
v_user_session_id number;

pragma autonomous_transaction;

begin

DBMS_LDAP.USE_EXCEPTION := TRUE;


retval := -1;
-- Initialize the LDAP session
my_session := DBMS_LDAP.init(ldap_host,ldap_port);


--Authenticate to the directory
--retval :=DBMS_LDAP.simple_bind_s(my_session, 'uid=wayyen,ou=people,o=Enterprise Company,dc=smartchain','wayyen');
retval :=DBMS_LDAP.simple_bind_s(my_session, '','');

--res_attrs(1):='cn';

retval := DBMS_LDAP.SEARCH_S(
ld => my_session
, base => base_dn
, scope => DBMS_LDAP.SCOPE_SUBTREE
, filter => username_filter
, attrs => res_attrs
, attronly => 0
, res => res_message
);

temp_entry := DBMS_LDAP.FIRST_ENTRY(my_session, res_message);



temp_dn := DBMS_LDAP.GET_DN(my_session, temp_entry);
-- dbms_output.put_line('User DN: '||temp_dn);



retval :=DBMS_LDAP.simple_bind_s(my_session, TEMP_DN, p_password);


res_attrs.delete;
res_attrs(1):='*';

retval := DBMS_LDAP.SEARCH_S(
ld => my_session
, base => base_dn
, scope => DBMS_LDAP.SCOPE_SUBTREE
, filter => replace(get_ldap_group_member_filter,'%M',temp_dn)
, attrs => res_attrs
, attronly => 0
, res => res_message
);

-- retval := DBMS_LDAP.COUNT_ENTRIES(my_session, res_message);
-- DBMS_OUTPUT.PUT_LINE(
-- RPAD('Number of Entries ', 25, ' ') || ': ' || TO_CHAR(retval)
-- );

temp_entry := DBMS_LDAP.FIRST_ENTRY(my_session, res_message);
entry_index := 1;

while temp_entry is not null
loop
-- dbms_output.put_line('***Group DN**** '||DBMS_LDAP.get_dn(my_session, temp_entry));
v_string_table(i):=DBMS_LDAP.get_dn(my_session, temp_entry);
i := i + 1;

temp_entry := DBMS_LDAP.NEXT_ENTRY(my_session, temp_entry);
-- DBMS_OUTPUT.PUT_LINE('===================================================');
entry_index := entry_index + 1;
END LOOP;

create_user_session(p_user_id, v_string_table, v_user_session_id);

ldap_ctx_pkg.set_session_id(v_user_session_id, p_client_identifier);


dbms_output.put_line('user_session_id: '||v_user_session_id);
commit;



retval := DBMS_LDAP.UNBIND_S(my_session);

end login;

************ Procedure Code End*****************

Web.config

<appSettings>
<!--<add key="BaseURLSite" value="http://localhost/SaviReportsWebSite"/>
<add key="DataSource" value="SRIRAMA-D620"/>
<add key="UID" value="sa"/>
<add key="PWD" value="satyam"/>
<add key="DatabaseName" value="Employee"/>-->

<add key="BaseURLSite" value="http://localhost/SaviReportsWebSite"/>
<add key="UID" value="rpt$928$syn"/>
<add key="PWD" value="rpt$928$syn"/>
<add key="Data Source" value="AURORA"/>
<add key="ReportService2005WebService.ReportService2005" value="http://localhost/ReportServer/ReportService2005.asmx"/>
</appSettings>


Public Function check_Login(ByVal strLoginIDDesc As String, ByVal strPasswordDesc As String, ByVal strSessionIDDesc As String) As Boolean


Dim strSQL As String
'Dim dsHomePage As DataSet
Dim OracleParam(2) As OracleParameter


Dim blnStatus As Boolean = False
Dim gStrConnection As String = Nothing

gStrConnection = "Persist Security Info=False;"
gStrConnection += "Integrated Security=False;"
gStrConnection += "User ID=" + ConfigurationManager.AppSettings("UID") + ";"
gStrConnection += "pwd=" + ConfigurationManager.AppSettings("PWD") + ";"
gStrConnection += "Data Source=" + ConfigurationManager.AppSettings("Data Source")

Dim OracleConnection1 As New OracleConnection(gStrConnection)
Dim cmd As New OracleCommand
Dim rowsAffected As Integer
Dim index As Integer

cmd.CommandText = "ldap_auth.login"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = OracleConnection1

OracleConnection1.Open()

OracleParam(0) = New OracleParameter("LoginIDDesc", OracleType.VarChar)
OracleParam(0).Direction = ParameterDirection.Input
OracleParam(0).Value = strLoginIDDesc

OracleParam(1) = New OracleParameter("PasswordDesc", OracleType. VarChar)
OracleParam(1).Direction = ParameterDirection.Input
OracleParam(1).Value = strPasswordDesc

OracleParam(2) = New OracleParameter("SessionIDDesc", OracleType. VarChar)
OracleParam(2).Direction = ParameterDirection.Input
OracleParam(2).Value = strSessionIDDesc

Dim UBound As Integer = OracleParam.Length

For index = 0 To UBound - 1
cmd.Parameters.Add(OracleParam(index))
Next

rowsAffected = cmd.ExecuteNonQuery()

OracleConnection1.Close()

If rowsAffected = -1 Then
blnStatus = True
End If

Return blnStatus
End Function


Regards,

SriRam