Creation of PL/SQL store procedure

Logic is come up with a store procedure in oracle . There will be a SOA team which will be sending input to the store procedure and then sql logic will be executed based on the input parameter . The output from store procedure will be sent back to SOA. May need to use object types to store output in array format. I can share my screen and show you what exactly output should look like


my requirement is I need to create a SP in oracle and it gets input from SOA team
SOA team builds the API and based on the search parameters in API , they will be passing parameter as input to SP
lets say there is a organization table and its has multiple columns and if API call is made in such a way that if oragnization ID is passed as search parameter , i need to get that ID as input and pass all the respective columns from the organization based on taht ID
columns might be org name, org location etc
the output will be multiple columns from SP and SOA will be consuming it and displaying the result set based on AI call