I am getting error when calling a Oracle SP from VB.NET

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%40ap.savi.com">vsrkrishnaap.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 SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteApp_CodeDataLayerDataRead.vb:line 131
at Savi.Reports_DataRead.DataRead.check_Login(String strLoginIDDesc, String strPasswordDesc, String strSessionIDDesc) in C:Documents and SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteApp_CodeDataLayerDataRead.vb:line 233
at Savi.HomePage_BL.HomePage.check_LoginDetails(String strLoginIDDesc, String strPasswordDesc, String strSessionIDDesc) in C:Documents and SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteApp_CodeBusinessLayerHomePage.vb:line 29
at HomePage_HomePage.go_Click(Object sender, ImageClickEventArgs e) in C:Documents and SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteModulesHomePageHomePage.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=&quot;http://localhost/SaviReportsWebSite";/>
&lt;add key="DataSource&quot; value=&quot;SRIRAMA-D620"/>
<add key="UID" value=&quot;sa"/>
<add key="PWD" value=&quot;satyam&quot;/>
<add key="DatabaseName" value=&quot;Employee&quot;/>-->

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

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") + ";&quot;
gStrConnection += "pwd=" + ConfigurationManager.AppSettings("PWD") + ";&quot;
gStrConnection += "Data Source=&quot; + ConfigurationManager.AppSettings("Data Source&quot;)

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(&quot;LoginIDDesc", OracleType.VarChar)
OracleParam(0).Direction = ParameterDirection.Input
OracleParam(0).Value = strLoginIDDesc

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

OracleParam(2) = New OracleParameter(&quot;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


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
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%40ap.savi.com">vsrkrishnaap.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&amp; rowidDescriptor, ArrayList&amp; resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor&amp; rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at Savi.Reports_DataRead.DataRead.ExecSP(String strSQL, OracleParameter[] OracleParam) in C:Documents and SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteApp_CodeDataLayerDataRead.vb:line 131
at Savi.Reports_DataRead.DataRead.check_Login(String strLoginIDDesc, String strPasswordDesc, String strSessionIDDesc) in C:Documents and SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteApp_CodeDataLayerDataRead.vb:line 233
at Savi.HomePage_BL.HomePage.check_LoginDetails(String strLoginIDDesc, String strPasswordDesc, String strSessionIDDesc) in C:Documents and SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteApp_CodeBusinessLayerHomePage.vb:line 29
at HomePage_HomePage.go_Click(Object sender, ImageClickEventArgs e) in C:Documents and SettingsvsrkrishnaMy DocumentsVisual Studio 2005WebSitesSaviReportsWebSiteModulesHomePageHomePage.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)&quot;

************ 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=&quot;http://localhost/SaviReportsWebSite";/>
&lt;add key="DataSource&quot; value=&quot;SRIRAMA-D620"/>
<add key="UID" value=&quot;sa"/>
<add key="PWD" value=&quot;satyam&quot;/>
<add key="DatabaseName" value=&quot;Employee&quot;/>-->

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

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") + ";&quot;
gStrConnection += "pwd=" + ConfigurationManager.AppSettings("PWD") + ";&quot;
gStrConnection += "Data Source=&quot; + ConfigurationManager.AppSettings("Data Source&quot;)

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(&quot;LoginIDDesc", OracleType.VarChar)
OracleParam(0).Direction = ParameterDirection.Input
OracleParam(0).Value = strLoginIDDesc

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

OracleParam(2) = New OracleParameter(&quot;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

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]

Messages in this topic (1) Reply (via web post) | Start a new topic

.