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

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

Answer 1

You probably need to provide size to the parameters.

Answer 2

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;

Answer 3

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

Answer 4

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

Answer 5

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.