Not able to get result set from stored procedure in ESQL?

Question

I am calling external stored procedure from esql., where input is also clob and output also clob.

i have to send input and and get response from Oracle DB Stored procedure.

but im getting error as SQL TYPE NOT FOUND 0 Error...

Any suggestions...

#MessageBroker #IIB #ESQL

CALL pro_call(P_DATA_CLOB,P_INIT_LOGGING) INTO V_RETRUN;

CREATE PROCEDURE pro_call(IN P_REQUEST CHAR ,IN P_INIT_LOGGING BOOLEAN) 
RETURNS CHAR
LANGUAGE DATABASE 
EXTERNAL NAME "INTERFACE.LPB_SERVICING.GET_SERVICE";

have tried this, but getting error response.

Please let us know if any one used this and get success response.

I believe your error is coming from your external program, not from the database. Double check to make sure your program knows what a Boolean is. — Paul W
– Paul W, Commented Feb 21, 2023 at 12:57
Are you sure that SQL runs under Oracle? functions return something, not procedures. — gsalem
– gsalem, Commented Feb 21, 2023 at 13:16
Yes this error is occured in ESQL in IIB toolkit, if we run the function in Oracle DB it is giving results, and its not a procedure, it is function under package — Satham hussain
– Satham hussain, Commented Feb 22, 2023 at 8:13
Sure... Have tried some methods.. Error : Text:CHARACTER:The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure. Insert Type:INTEGER:5 Text:CHARACTER:LPBTEST.INTERFACE.LPB_SERVICING.GET_SERVICE Insert Type:INTEGER:2 Text:CHARACTER:3 ESQL Code: CALL compasscall(P_DATA_CLOB,P_INIT_LOGGING,v_Return); CREATE PROCEDURE compasscall(IN P_REQUEST CHAR ,IN P_INIT_LOGGING BOOLEAN, OUT v_Return CHAR)LANGUAGE DATABASE EXTERNAL NAME "INTERFACE.LPB_SERVICING.GET_SERVICE"; — Satham hussain
– Satham hussain, Commented Feb 22, 2023 at 9:56

Attila Repasi · Accepted Answer · 2023-02-22 08:50:08Z

1

I think the problem is the BOOLEAN parameter in your declaration. Oracle SQL doesn't have a BOOLEAN type, so there is no mapping between the ESQL BOOLEAN and an Oracle type.

https://www.ibm.com/docs/en/app-connect/11.0.0?topic=functions-data-types-values-from-external-databases

answered Feb 22, 2023 at 8:50

Attila Repasi

1,84011 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Satham hussain Over a year ago

yes but the oracle function declaration also have the Boolean type as input.. I Have added the function declaration here... function get_service(p_request clob, p_init_logging boolean := true) return clob;

Attila Repasi Over a year ago

Maybe try creating a function that hides the boolean parameter with a type that is supported by IIB.

Satham hussain Over a year ago

I really don't know how to do that, could you please suggest me something...

Attila Repasi Over a year ago

I mean you could create a new function in your DB, that have an INTEGER in place of the current BOOLEAN parameter and that new function would be calling the existing function and translating the new INTEGER parameter to a BOOLEAN value. And then you should be able to call the new function from IIB.

Collectives™ on Stack Overflow

Not able to get result set from stored procedure in ESQL?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related