none
Error ORA-06550 , VB.NET and Oracle RRS feed

  • Question

  •  

    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



    ************ 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.LongVarChar)
    OracleParam(0).Direction = ParameterDirection.Input
    OracleParam(0).Value = strLoginIDDesc

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

    OracleParam(2) = New OracleParameter("SessionIDDesc", OracleType.LongVarChar)
    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.

    Friday, October 19, 2007 12:23 PM

All replies


  • Hi All,

      I am able to solve the problem.

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

    Parameter name in SP code should be same.Here LoginID is tbe parameter name in SP and the code.By doing this parameters r binded.


    Cheers,
    SriRam

     

    Thursday, November 1, 2007 6:16 AM