Home » SQL & PL/SQL » SQL & PL/SQL » One Single Stored Procedure to fetch data using multiple complex queries (Oracle)
One Single Stored Procedure to fetch data using multiple complex queries [message #626449] Tue, 28 October 2014 01:59 Go to next message
contactsenthils@yahoo.com
Messages: 1
Registered: October 2014
Junior Member
I have a scenario where i have to show a screen which has multiple data fetched from different tables using complex joins etc.,
There will be only one service call from ui to fetch those in JSON format.

Following are the approaches that came to my mind

1) Execute Multiple queries from service layer and populate Object and send the JSON object back.
2) Create a temp table, populate the data required as when the original data is updated.And query the temp table when the above screen is displayed.

For me both are the approaches are not convincing.I don't want multiple db calls from service layer or create temp table for this purpose. Rather iam thinking to have one Stored PRocedure which will execute mutiple queries within and return Custom structure which can be mapped to Java Object which in turn will be returned to UI Layer.

IS this POssible?

Any Suggestions?

Thanks.

Regards
Senthil Kumar Sekar
Re: One Single Stored Procedure to fetch data using multiple complex queries [message #626450 is a reply to message #626449] Tue, 28 October 2014 02:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

What are the queries you are running? One query can join as many tables as you want, and return as many rows as you want. Are you sure that you need more than one?
Re: One Single Stored Procedure to fetch data using multiple complex queries [message #626960 is a reply to message #626449] Wed, 05 November 2014 10:58 Go to previous message
raj_te
Messages: 46
Registered: August 2013
Location: INDIA
Member
Senthil,

The best way to do is to use a "sys_refcursor". Just use it and return the data in the cursor on the screen.

For example
create function get_data ( type varchar2 ) return sys_refcursor as
  ret_cur sys_refcursor;
begin

  if type = 'EMP' then
    open ret_cur for select * from emp;
  elsif type = 'DEPT' then
    open ret_cur for select * from dept;
  end if;

  return ret_cur;
end;
Previous Topic: trigger on update
Next Topic: how to convert code of sql server into oracle
Goto Forum:
  


Current Time: Fri Mar 29 05:40:35 CDT 2024