my procedure code
create or replace procedure proc1(e_id number,ename varchar2,result out varchar2)
as
begin
insert into emp_name(emp_id,emp_name) values(e_id,ename);
result:='Row inserted';
exception when others then
result:='Exception Occurs';
end;
when i execute the above procedure in db it is executing and returns the results even when exception occurs
but when this is used in .net layer when ever exception occurs instead of going to exception block
oracle is throwing exception
05/22/2019 06:07:23 - Exception message: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "CHUB.CHUB_AUTOCONTINUATION_PKG", line 155 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-12899: value too large for column ************* (actual: 39, maximum: 32) ORA-06512: at line 1, Stack trace: at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
Could it be that your calling application is providing a result variable that is too small ?
eg
SQL> create table emp_name ( emp_id int, emp_name varchar2(50));
Table created.
SQL>
SQL> create or replace
2 procedure proc1(e_id number,ename varchar2,result out varchar2)
3 as
4 begin
5 insert into emp_name(emp_id,emp_name) values(e_id,ename);
6 result:='Row inserted';
7 exception when others then
8 result:='Exception Occurs';
9 end;
10 /
Procedure created.
SQL>
SQL>
SQL> declare
2 res varchar2(5); <<==== wont be able to hold the result
3 begin
4 proc1(1,'connor',res);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MCDONAC.PROC1", line 7
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MCDONAC.PROC1", line 5
ORA-06512: at line 4