NO Data Found on Oracle OR ORA-01403:

While Insert data into another varible some case if datanot available then we get an error on Oracle so take care that case we need to write a query like this.

Sample Procedure

 create or replace

PROCEDURE "SPROC_VAULT_TEMP" 

( 

  l_RECORDSET OUT TYPES.cursor_type,

  l_ACTION in varchar2    DEFAULT  null,

  l_Vault_No_New in varchar2  DEFAULT  null,

  l_Vault_TKN_No in varchar2  DEFAULT  null

)

As 

l_PanNumber varchar2(100) default '';

l_Vault_No_Old varchar2(100) default '';

Begin

  If l_ACTION = 'SelectById' Then 

  

    BEGIN

      Select pan_number, vault_ref_aadhaar into l_PanNumber, l_Vault_No_Old from tbl_step1 

      Where vault_ref_aadhaar_old is null And uid_token = l_Vault_TKN_No;

    Exception

    WHEN TOO_MANY_ROWS  then  OPEN l_RECORDSET FOR Select 'Too Many Rows in that' from dual;

    WHEN NO_DATA_FOUND then OPEN l_RECORDSET FOR Select 'No Data Found' from dual;

    WHEN others then OPEN l_RECORDSET FOR Select 'No Other Issue' from dual;

    END;

    if l_PanNumber IS NOT NULL then

      Update tbl_step1 Set vault_ref_aadhaar = l_Vault_No_New, VAULT_REF_AADHAAR_OLD = l_Vault_No_Old 

        Where uid_token is not null And uid_token = l_Vault_TKN_No;

      Update tbl_aadhar Set UID_ID = l_Vault_No_New , UID_ID_OLD = UID_ID 

        Where pannumber = l_PanNumber;

      Commit;

      

      OPEN l_RECORDSET FOR  Select 'True' from Dual;

    Else

      OPEN l_RECORDSET FOR 

      Select l_Vault_TKN_No ,l_PanNumber from dual;  

    end if;

    

  end if;

End;

Sample Source Code (.Net)

dsMain = new DataSet();

            if (Connection.State == ConnectionState.Closed)

            {

                Connection.Open();

            }

            OracleCommand cmdMain = new OracleCommand("SPROC_VA", Connection);

            cmdMain.CommandType = CommandType.StoredProcedure;

            cmdMain.Parameters.Add("l_RECORDSET", OracleType.Cursor);

            cmdMain.Parameters["l_RECORDSET"].Direction = ParameterDirection.Output;

            cmdMain.Parameters.Add("l_ACTION", OracleType.VarChar, 100);

            cmdMain.Parameters["l_ACTION"].Value = "SelectById";

            cmdMain.Parameters.Add("l_Vault_No_New", OracleType.VarChar, 100);

            cmdMain.Parameters["l_Vault_No_New"].Value = l_Vault_No_New;

            cmdMain.Parameters.Add("l_Vault_TKN_No", OracleType.VarChar, 100);

            cmdMain.Parameters["l_Vault_TKN_No"].Value = l_Vault_TKN_No;

            OracleDataAdapter daMain = new OracleDataAdapter(cmdMain);

            daMain.Fill(dsMain);