VB.NET and Oracle

[srkvellanki]

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 vsrkrishna@ap.savi.com

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