Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure (merged 3) (10 g)
icon4.gif  Stored procedure (merged 3) [message #435626] Thu, 17 December 2009 02:31 Go to next message
satyamhcl
Messages: 3
Registered: December 2009
Junior Member
Hi,

I need to create a stored procedure which contains latest inserted rows from a table.

Eg: Table contains 3 rows and if I am inserting 4th record the stored procedure must return only 4 th row.
and if I am inserting 5,6 rows then I have to get only 5,6 rows in my result set.
If we want we can use temporary table and also triggers. But I need a soluntion.

Thanks in Advance.

[Updated on: Thu, 17 December 2009 02:36] by Moderator

Report message to a moderator

Re: Stored procedure (merged) [message #435630 is a reply to message #435626] Thu, 17 December 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no way to knwo the last row unless you have a timestamp in your row columns.

Regards
Michel
Re: Stored procedure [message #435631 is a reply to message #435626] Thu, 17 December 2009 02:36 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
For how long?

If you insert a fourth row, how long should that fourth row be returned by the procedure?

And what is the difference between inserting the fourth row, and inserting the 5th and 6th row. Why should the 5th AND 6th row be returned in the second case, and in the first case not the 3rd and 4th row?

Oh, and to have a "nth" row in the first place, you will need a column in the table that stores this "insertion order", do you have such a column?

[Updated on: Thu, 17 December 2009 03:06]

Report message to a moderator

Re: Stored procedure (merged 3) [message #435633 is a reply to message #435626] Thu, 17 December 2009 02:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
From the info you posted, I can only conclude that you will need a column ALREADY_DISPLAYED that gets updated by the procedure.
Re: Stored procedure (merged 3) [message #435714 is a reply to message #435626] Thu, 17 December 2009 10:09 Go to previous messageGo to next message
satyamhcl
Messages: 3
Registered: December 2009
Junior Member
Hi All,

I have created the following procedure.
Test1 is master table and test2 is temporary table.

create procedure p1 (p_rs out sys_refcursor) as
cursor c_rs is
select col1, col2 from test1;
t_rs c_rs%rowtype;
begin
delete from test2; commit;
open c_rs;
loop fetch c_rs into t_rs;
exit when c_rs%notfound;
insert into test2(col1,col2) values (t_rs.col1,t_rs.col2);
end loop;
close c_rs; commit;
open p_rs for
select col1, col2 from test2;
end;

This procedure retrives all the rows from master table(test1).
Please rewrite this procedure to insert only the latest values into the temporary table (test2) using truggers.

Thanks in advance.
Re: Stored procedure (merged 3) [message #435719 is a reply to message #435714] Thu, 17 December 2009 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 17 December 2009 09:34
There is no way to knwo the last row unless you have a timestamp in your row columns.

Regards
Michel

ThomasG wrote on Thu, 17 December 2009 09:36
...
Oh, and to have a "nth" row in the first place, you will need a column in the table that stores this "insertion order", do you have such a column?

Frank wrote on Thu, 17 December 2009 09:52
From the info you posted, I can only conclude that you will need a column ALREADY_DISPLAYED that gets updated by the procedure.

Re: Stored procedure (merged 3) [message #435912 is a reply to message #435626] Fri, 18 December 2009 21:10 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
First tell us how you know what order rows were inserted into your table.

I offer up some suggestions for you to read about.

sequences with order
ora_scn
oracle timestamp data type


So again, how do you know the order of rows inserted into your table?

Kevin

[Updated on: Fri, 18 December 2009 21:11]

Report message to a moderator

Previous Topic: Select command betwen two differente owners
Next Topic: Create Bitmap index on primary key
Goto Forum:
  


Current Time: Fri Sep 30 12:23:51 CDT 2016

Total time taken to generate the page: 0.05526 seconds