Forum Stats

  • 3,840,101 Users
  • 2,262,567 Discussions
  • 7,901,149 Comments

Discussions

Returning clob type fails with ORA-06502: PL/SQL: numeric or value error

Hi All,


In Oracle DB 19c I have a stored procedure which accepts a list of IDs (Invoices ) as varchar2 type, calls a SOAP service which returns binary data and I then try to return this as CLOB type.

My proc works fine within the scope of SQL dev (I've tested it with multiple invoices and the result is OK) .


However, I've exposed it as REST but I struggle to find a way to return the binary data as CLOB.. When I test the REST for just 1 invoice it works fine, but whenever I pass more than 1 it fails with error ORA-06502: PL/SQL: numeric or value error.


I guess this is due to the fact that my OUT parameter (Response ) is type STRING rather than CLOB( which is not available from the LoV when I define the parameter... ). Any ideas how to return that CLOB please?


Pasting the handler here :


BEGIN    

XXCITCO_PMOSS_UTL_PKG.PRINT_DRAFT_INVOICE(

    P_POD_NAME => :P_POD_NAME,

    P_INVOICE_IDS => :P_INVOICE_IDS,

    P_BIN_OUTPUT => :P_BIN_OUTPUT,

    P_STATUS => :P_STATUS,

    P_MESSAGE => :P_MESSAGE

  );


END;


Parameters are defined below:




From SOAP UI:



Thanks,

Aleks

Answers