Invalid column type: 1111 with Execute Store Procedure

@sourav
please make sure all the parameters mentioned in the stored procedure as IN
should be exactly match the name and datatype.Any mismatch would result in wrong mapping error

@ramanjaneyulut- In the stored procedure (Oracle db), I checked the input parameter it is fine, I have out parameter of type package object. For example GETTRTDETAIL(PackageNo IN VARCHAR2,Package_Tab OUT package_obj)
When Out parameter is of type package object, it shows Invalid Column Type 1111. When I remove the out parameter, it return {}.I guess issue is with out parameter
But I need to have out parameter of type object to return multiple column values.
How to retrieve this out parameter or any other alternative solution?

and type definition for Package_Obj is
type packge_obj is object (param1 varchar2(100), param 2 varchar2(200));

@sourav
As per my knowledge, instead of returning Object(Package_obj), return the values(param1,param2) directly and map the same in Appian with same name and equivalent data type

@ramanjaneyulut- Thanks for your suggestion,
create or replace type package_tab is table of packge_obj;
create or replace type packge_obj is object (param1 varchar2(100), param2 varchar2(200));
We are returning type of "package_tab " from procedure because there are more than 100000 records which will be returned from procedure for certain scenario
Is there any alternative solution? Please advise

souravs - I have had a bunch of problems figuring out the execute stored procedure plugin. I've had decent luck with the smart service running in a process, but using the function call has been very difficult for me. I can get the correct results running the stored procedure in a normal database tool and via the process model, but the function call is very finicky. When it works (perfectly coded select statement in the stored procedure, etc.) it is fast and runs fine, but just because the results are correct in the db & via the process model smartservice call does NOT mean that Appian will cleanly run it as a function. Be careful and thoroughly test if you are calling it via a function/rule. Also, per previous posts, do NOT attempt to do data insertion/updates via the function call, only select statements. The smartservice should be used if you have to update data.