Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

User_U66J8Jul 4 2022

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:

image.png

From SOAP UI:

image.png
Thanks,
Aleks

Comments

Post Details

Added on Jul 4 2022
3 comments
793 views