locked
Oracle.DataAccess.Client, Stored Procedure, Problem: Error: ORA-01403: no data found ORA-06512: at "jehanSchema.GETUSERFNAME", line 8 ORA-06512: at line 1 RRS feed

  • Question

  • using oracle 10gR2, I created following function

     

    create or replace FUNCTION GetUserFName(

        user_id1 IN varchar2

    )

    RETURN VARCHAR2

    IS

      user_idd   VARCHAR2(100);

    begin

      SELECT user_name

        INTO user_idd

        FROM app_user

       WHERE user_id = user_id1

      ;

      RETURN user_idd;

    end;

     

    When I am executing in sql developer, correct value returning as following

     

    declare

        result VARCHAR2(100);

    begin

         result := GetUserFName('kami');

         DBMS_Output.put_line(result);

    end;

     

    the result i obtaining is following  

    Mr. Kamran Khan


    ---------------------

    Problem arise when I want to execute it using C# VS2008, using following code

     

    OracleCommand command = new OracleCommand();

                command.CommandType = CommandType.StoredProcedure;

                command.Connection = connection;

                command.CommandText = "GetUserFName";

                command.Parameters.Add("user_id1", OracleDbType.Varchar2, username, ParameterDirection.Input);

                command.Parameters.Add("user_idd", OracleDbType.Varchar2,ParameterDirection.ReturnValue);

                try

                {

                    string status = command.ExecuteNonQuery().ToString();

                

                    if (status == "-1")

                    {

                        throw (new Exception("ther is no data in your table", null));

                    }

                    else

                    {

                        value = command.Parameters["user_idd"].Value.ToString();

                    }

                }

                catch (Exception ex)

                {

                    errorMessage = ex.Message + " " + ex.InnerException;

                }

                finally

                {

                    command.Dispose();

     

                }

     

     

    I getting exception

    Error: ORA-01403: no data found ORA-06512: at "jehanSchema.GETUSERFNAME", line 8 ORA-06512: at line 1



    Where I am doing wrong.


    Seek Knowledge From Cradle to Grave
    • Edited by Mr. Jehan Friday, March 20, 2009 1:10 PM missing information
    Friday, March 20, 2009 1:06 PM

Answers

  • this exception is oracle exception.

    begin

      SELECT user_name

        INTO user_idd

        FROM app_user

       WHERE user_id = user_id1

      ;

     


    exception

    when no_data_found then

       you can write what do you want when no data found at table app_user

    end;


    RETURN user_idd;


    • Proposed as answer by Guo Surfer Thursday, March 26, 2009 5:57 AM
    • Marked as answer by Guo Surfer Friday, March 27, 2009 11:05 AM
    Friday, March 20, 2009 6:12 PM
  • Hi Mr.Jehan,

    According to http://www.orafaq.com/wiki/ORA-01403, an ORA-01403 error occurs when a SQL statement, written within a PL/SQL block, does not fetch any data.
    Please have a try taking gluttonous's suggestion, handling the error in the PL/SQL block. For more information, please refer to the above site. Thank you!

    This response contains a reference to a third party http://www.orafaq.com/wiki/ORA-01403. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best regards,
    Guo
    • Proposed as answer by Guo Surfer Thursday, March 26, 2009 5:57 AM
    • Marked as answer by Guo Surfer Friday, March 27, 2009 11:05 AM
    Tuesday, March 24, 2009 9:58 AM

All replies

  • You probably need to provide size to the parameters.
    Friday, March 20, 2009 2:31 PM
  • this exception is oracle exception.

    begin

      SELECT user_name

        INTO user_idd

        FROM app_user

       WHERE user_id = user_id1

      ;

     


    exception

    when no_data_found then

       you can write what do you want when no data found at table app_user

    end;


    RETURN user_idd;


    • Proposed as answer by Guo Surfer Thursday, March 26, 2009 5:57 AM
    • Marked as answer by Guo Surfer Friday, March 27, 2009 11:05 AM
    Friday, March 20, 2009 6:12 PM
  • Dear Nimish Mistry

               I specified the size as following 

    command.Parameters.Add("user_id1", OracleDbType.Varchar2,16, username, ParameterDirection.Input);

    command.Parameters.Add("user_idd", OracleDbType.Varchar2,100,ParameterDirection.ReturnValue);


    even though same problem,
    ----------------------------------------
    and gluttonous, as i shown in my post, when i am executing the procedure using sql developer, then without any problem i getting correct value, but when  i executing procedure through C# code then i getting exception,

    although there is no problem in connection, and other than stored procedure every thing work fine, mean there is no problem when i am executing queries like "select * from emp". 


    Seek Knowledge From Cradle to Grave
    Saturday, March 21, 2009 5:53 AM
  • Hi Mr.Jehan,

    According to http://www.orafaq.com/wiki/ORA-01403, an ORA-01403 error occurs when a SQL statement, written within a PL/SQL block, does not fetch any data.
    Please have a try taking gluttonous's suggestion, handling the error in the PL/SQL block. For more information, please refer to the above site. Thank you!

    This response contains a reference to a third party http://www.orafaq.com/wiki/ORA-01403. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best regards,
    Guo
    • Proposed as answer by Guo Surfer Thursday, March 26, 2009 5:57 AM
    • Marked as answer by Guo Surfer Friday, March 27, 2009 11:05 AM
    Tuesday, March 24, 2009 9:58 AM
  • Hi Mr.Jehan,

    We are marking the above posts as answers.
    If you still have problem, please feel free to unmark as the answer and change the issue type back to "Question" and follow up with more necessary information. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.
    Thank you!

    Best regards,
    Guo
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, March 27, 2009 11:06 AM