Home » SQL & PL/SQL » SQL & PL/SQL » RECORDs --> %ROWTYPE (oracle 9i)
icon4.gif  RECORDs --> %ROWTYPE [message #589733] Wed, 10 July 2013 06:45 Go to next message
cp.dandriyal
Messages: 5
Registered: July 2013
Location: India
Junior Member
below is the code. please let me know why only 1 record is getting inserted and not all 10 records. Shocked


declare

cursor c1 is select * from temp_table; -- contains 10 records
t_cursor_c1 c1%rowtype;

begin
dbms_output.put_line('Project ID ------------------ Name');

if c1%isopen then
close c1;
end if;

open c1;
fetch c1 into t_cursor_c1; -- how many records are fetch into t_cursor_c1 ? I guess 10 !!!!
close c1;

t_cursor_c1.project_id := t_cursor_c1.project_id*12;

insert into ud_project values t_cursor_c1; -- Only 1 record is getting inserted. WHY ?
commit;


end;
Re: RECORDs --> %ROWTYPE [message #589734 is a reply to message #589733] Wed, 10 July 2013 06:49 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

cp.dandriyal wrote on Wed, 10 July 2013 12:45

fetch c1 into t_cursor_c1; -- how many records are fetch into t_cursor_c1 ? I guess 10 !!!!

You guess wrong. It fetches 1. If you read the documentation then you wouldn't need to guess.
Re: RECORDs --> %ROWTYPE [message #589735 is a reply to message #589734] Wed, 10 July 2013 06:55 Go to previous messageGo to next message
cp.dandriyal
Messages: 5
Registered: July 2013
Location: India
Junior Member
Requested to please let me know in brief....In documentation, lots of things are there...Please help
Re: RECORDs --> %ROWTYPE [message #589736 is a reply to message #589735] Wed, 10 July 2013 06:58 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
Click on my link.
Click on link for your version of DB.
type "fetch cursor" into search box.
Read results.
Re: RECORDs --> %ROWTYPE [message #589738 is a reply to message #589736] Wed, 10 July 2013 07:06 Go to previous messageGo to next message
cp.dandriyal
Messages: 5
Registered: July 2013
Location: India
Junior Member
SO SORRY.... Question was posted wrongly...I was wrong....
Re: RECORDs --> %ROWTYPE [message #589756 is a reply to message #589738] Wed, 10 July 2013 08:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1882
Registered: May 2013
Location: World Wide on the Web
Senior Member
cp.dandriyal -

So what did you find in the link cookiemonster provided to you? Did you understand the Fetch statement and why in your case it would only fetch 1 record and not 10? Please post how you fixed your issue.
Re: RECORDs --> %ROWTYPE [message #589839 is a reply to message #589756] Thu, 11 July 2013 05:45 Go to previous messageGo to next message
cp.dandriyal
Messages: 5
Registered: July 2013
Location: India
Junior Member
Dear lalit ,

Please try to insert the same one-by-one using a LOOP.

Rgds/Chandra Prakash
Re: RECORDs --> %ROWTYPE [message #589841 is a reply to message #589839] Thu, 11 July 2013 06:17 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm sure lalit can, question is, can you?
Do you understand how fetch works?
If you haven't already - read up on cursor for loops. Once you've understood how that works try rewriting the whole thing as single insert/select statement (without any cursor).
You don't need cursors to do this, but you should understand how they work.
Re: RECORDs --> %ROWTYPE [message #589843 is a reply to message #589839] Thu, 11 July 2013 06:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1882
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ok, that's good you understood and fixed your issue. Hope you also read about using "EXIT WHEN" within the loop in the link provided to you:-
loop
fetch c1 into t_cursor_c1; 
exit when c1%notfound;
end loop;


BTW, as cookiemonster has suggested, the whole pl/sql code can be replaced by just a insert SQL.

[Updated on: Thu, 11 July 2013 06:27]

Report message to a moderator

Re: RECORDs --> %ROWTYPE [message #589846 is a reply to message #589843] Thu, 11 July 2013 06:47 Go to previous messageGo to next message
cp.dandriyal
Messages: 5
Registered: July 2013
Location: India
Junior Member
Lalit and Cookie

Thanks for your replies. I was jus trying to this using RECORDs. that is the reason that i used in this way.

Anyways, thanks for your tremendous efforts on this.

Re: RECORDs --> %ROWTYPE [message #589852 is a reply to message #589846] Thu, 11 July 2013 07:54 Go to previous message
Michel Cadot
Messages: 58639
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 good books for you:

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: UTL_FILE
Next Topic: Oracle DB 11g "LEVEL" issue
Goto Forum:
  


Current Time: Fri Aug 01 01:37:27 CDT 2014

Total time taken to generate the page: 0.14575 seconds