Home » SQL & PL/SQL » SQL & PL/SQL » Fetching results using intermediate table (Oracle 10g)
Fetching results using intermediate table [message #602068] Fri, 29 November 2013 03:53 Go to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Hi All,


The intermediate table is necessary to populate the results of two or more cursors after calculating the complex conditions which is never possible with direct query. But when there are more records for insertion it is taking long time for execution. Because for multiuser environment always need to show the user's respective data. Is there any way to achieve the same thing.

   --create table 
    create table emp_info (info1 number,info2 varchar2(20),info3 varchar2(20),info4 varchar2(20));
  
    --getting emp details 
    create or replace procedure get_emp_info(p_deptno number,p_user varchar2,p_result out sys_refcursor) is    
    begin
    delete from emp_info where info4=p_user;
    commit;
    for i in( select deptno,dname,loc from dept where deptno=p_deptno) loop
      insert into emp_info values(i.deptno,i.dname,i.loc,p_user);
     
      for j in (select empno,ename,job from emp where deptno=i.deptno)loop
        insert into emp_info values(j.empno,j.ename,j.job,p_user);
      end loop;
    end loop;
    commit;
    open p_result for select * from emp_info where info4=p_user;    
    end get_emp_info;
    
    --testing 
    variable p_result refcursor;
    SET autoprint ON ;
    BEGIN
      get_emp_info(20,'xyz',:p_result);
      --get_emp_info(20,'yyy',:p_result);
    end;
    


Regards,
Nathan

[Updated on: Fri, 29 November 2013 03:54]

Report message to a moderator

Re: Fetching results using intermediate table [message #602073 is a reply to message #602068] Fri, 29 November 2013 04:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1766
Registered: May 2013
Location: World Wide on the Web
Senior Member
How is this procedure executed? How are the parameters passed? Is it a backend operation or called from frontend?

Can you implement MERGE statement, WHEN MATCHED then delete WHEN NOT MATCHED then insert.
Re: Fetching results using intermediate table [message #602074 is a reply to message #602068] Fri, 29 November 2013 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 10839
Registered: September 2008
Location: Rainy Manchester
Senior Member
sss111ind wrote on Fri, 29 November 2013 09:53

The intermediate table is necessary to populate the results of two or more cursors after calculating the complex conditions which is never possible with direct query.

Are you sure they can't be done in sql? Presumably it's these calculations that are taking most of the time and since you haven't shown them I'm not sure what it is you expect us to do.
sss111ind wrote on Fri, 29 November 2013 09:53
Because for multiuser environment always need to show the user's respective data.

If you really need an intermediate table make it a global temp, then you don't need to worry about this.
Re: Fetching results using intermediate table [message #602075 is a reply to message #602073] Fri, 29 November 2013 04:08 Go to previous messageGo to next message
cookiemonster
Messages: 10839
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lalit Kumar B wrote on Fri, 29 November 2013 10:06
Can you implement MERGE statement, WHEN MATCHED then delete WHEN NOT MATCHED then insert.


Where did you get the idea that merge would be useful from?

[Updated on: Fri, 29 November 2013 04:08]

Report message to a moderator

Re: Fetching results using intermediate table [message #602092 is a reply to message #602075] Fri, 29 November 2013 05:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1766
Registered: May 2013
Location: World Wide on the Web
Senior Member
cookiemonster wrote on Fri, 29 November 2013 15:38
Lalit Kumar B wrote on Fri, 29 November 2013 10:06
Can you implement MERGE statement, WHEN MATCHED then delete WHEN NOT MATCHED then insert.


Where did you get the idea that merge would be useful from?


At first instance it looked like a normal delete and insert task.

MERGE INTO EMP_INFO EI
      USING (SELECT <required column list> FROM EMP E, DEPT D WHERE < JOIN CONDITIONS >)
         ON (EI.INFO4 = <required value and conditions>)
      WHEN MATCHED THEN 
         DELETE WHERE <conditional operation as required> 
         INSERT INTO EMP_INFO VALUES <required values> --these values are fetched from dept table
         INSERT INTO EMP_INFO VALUES <required values> --these values are fetched from emp table


I am not able to understand this :

sss111ind wrote on Fri, 29 November 2013 15:23

      <some code>
      insert into emp_info values(i.deptno,i.dname,i.loc,p_user);
      <some code>
      insert into emp_info values(j.empno,j.ename,j.job,p_user);



In the same columns OP is inserting different values from dept and emp? Seems pretty confusing. I am just looking at the logic, I might be wrong. But requirement is unclear. The data integrity is violated.
Re: Fetching results using intermediate table [message #602095 is a reply to message #602092] Fri, 29 November 2013 05:45 Go to previous messageGo to next message
cookiemonster
Messages: 10839
Registered: September 2008
Location: Rainy Manchester
Senior Member
Re-read the first sentence of the OPs post. This has nothing to do with merge.
OP is using the table to store results of complex calculations before passing back to the client.
The data for given user is completely deleted each time it's run.
And since the data is transitory data integrity is irrelevant as well.
Re: Fetching results using intermediate table [message #602096 is a reply to message #602095] Fri, 29 November 2013 05:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1766
Registered: May 2013
Location: World Wide on the Web
Senior Member
cookiemonster wrote on Fri, 29 November 2013 17:15
And since the data is transitory data integrity is irrelevant as well.


Ok, but doesn't the refcursor has different set of rows from dept and emp together?

For the below statement :
open p_result for select * from emp_info where info4=p_user;


At this moment, emp_info has :

"deptno and empno in info1 field"
"dname and ename in info2 field"
"loc and job in info3 field"
for the same user in info4 field.
Re: Fetching results using intermediate table [message #602097 is a reply to message #602095] Fri, 29 November 2013 06:10 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Thanks Cooke and Lalit,

But how global temporary can resolve the issue if the calculation is taking more time. And definitely that can not possible in plain sql
for that only pl/sql must have been used. Is there any way to do this for mutiple user giving different data.
Re: Fetching results using intermediate table [message #602098 is a reply to message #602096] Fri, 29 November 2013 06:11 Go to previous message
cookiemonster
Messages: 10839
Registered: September 2008
Location: Rainy Manchester
Senior Member
well yes, but this isn't the real code, since the complex conditions are missing, so I wouldn't spend too much time analysing it.
Previous Topic: Need code
Next Topic: translate
Goto Forum:
  


Current Time: Tue Jul 22 05:03:49 CDT 2014

Total time taken to generate the page: 0.09568 seconds