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

User_U66J8

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

thatJeffSmith-Oracle

You might need to use media resource and just return application/json with your BLOB on the response.

User_U66J8

https://community.oracle.com/tech/developers/discussion/comment/16840631#Comment_16840631

Thanks for your response!

Any examples, please?

Thank you!

thatJeffSmith-Oracle

I have lots of examples for ORDS on my blog, i'll save you the Google, here's one

https://www.thatjeffsmith.com/archive/2021/03/downloading-streaming-content-with-ords-file-downloads/