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: 10571
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: 10571
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: 1080
Registered: May 2013
Location: Hi-tech city
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: 10571
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: 1080
Registered: May 2013
Location: Hi-tech city
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: 57602
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: Wed Apr 16 12:10:55 CDT 2014

Total time taken to generate the page: 0.10204 seconds