"numeric or value error" on the output parameter of EF CORE call to Oracle11g

Question

I followed all the online help I could get and come up with this:

Stored Procedure:

create or replace procedure            proc_cmap_unit_test 
(
  param1 in varchar2
  ,tkn out varchar2
) as 
  begin
    select 'hello' into tkn from dual;
  null;
end proc_cmap_unit_test;

On .Net CORE, I have these:

OracleParameter param1 = new OracleParameter
  (
    "param1"
    , OracleDbType.Varchar2
    , ParameterDirection.Input
  );

OracleParameter token = new OracleParameter
  (
    "tkn"
    , OracleDbType.Varchar2
    , ParameterDirection.Output
  );

cntxt.Database
     .ExecuteSqlCommand($"BEGIN PROC_CMAP_UNIT_TeST(:param1, :tkn); end;", param1, token);

After I run the code, I got this stupid error:

ORA-06502: PL/SQL: numeric or value error

Can anyone tell me what am I missing? Thanks!

Answer 1

Problem solved:

For some reason, I have to explicitly set the size of the output variable.

token.Size = '10';

Answer 2

I don't know tools you use, but - as far as Oracle is concerned, here's how it goes:

Your procedure:

SQL> CREATE OR REPLACE PROCEDURE proc_cmap_unit_test (param1  IN     VARCHAR2,
  2                                                   tkn        OUT VARCHAR2)
  3  AS
  4  BEGIN
  5     SELECT 'hello' INTO tkn FROM DUAL;
  6
  7     NULL;
  8  END proc_cmap_unit_test;
  9  /

Procedure created.

Testing:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_out  VARCHAR2 (10);                       --> this! "hello" fits in here
  3  BEGIN
  4     proc_cmap_unit_test ('a', l_out);
  5     DBMS_OUTPUT.put_line (l_out);
  6  END;
  7  /
hello

PL/SQL procedure successfully completed.

But, if variable that is supposed to accept procedure's OUT parameter value is too small, it'll fail:

SQL> DECLARE
  2     l_out  VARCHAR2 (1);                      --> this! "hello" can't fit
  3  BEGIN
  4     proc_cmap_unit_test ('a', l_out);
  5     DBMS_OUTPUT.put_line (l_out);
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DP_4005.PROC_CMAP_UNIT_TEST", line 5
ORA-06512: at line 4


SQL>

See if it rings a bell regarding your tool. Best of luck!