Home » SQL & PL/SQL » SQL & PL/SQL » What is problem
What is problem [message #228745] Wed, 04 April 2007 01:05 Go to next message
fakhar55
Messages: 99
Registered: September 2005
Location: UAE
Member
Hi all,

What is the problem with this code?

CREATE OR REPLACE PROCEDURE repData
AS
     CURSOR c1 is
     SELECT * from table1;
     c1_rec table1%ROWTYPE;


BEGIN
FOR c1_rec IN c1
  LOOP
  INSERT INTO table2 as SELECT c1_rec.* FROM DUAL;
  END LOOP;

END;

Re: What is problem [message #228756 is a reply to message #228745] Wed, 04 April 2007 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They are so many errors!

1/

Syntax is either (without c1_rec declaration):
FOR c1_rec in (SELECT * from table1) LOOP ...

or (with c1_rec declaration):
OPEN c1;
LOOP
  FETCH C1 into c1_rec;
  EXIT WHEN C1%NOTFOUND;
  ...
END LOOP;
CLOSE c1;

but not
FOR c1_rec IN c1 LOOP ...


2/

Syntax:
INSERT INTO table2 as SELECT c1_rec.* FROM DUAL;

is wrong.

3/

Why not directly:
INSERT INTO table2 SELECT * FROM table1


Regards
Michel


Re: What is problem [message #228766 is a reply to message #228745] Wed, 04 April 2007 02:35 Go to previous messageGo to next message
fakhar55
Messages: 99
Registered: September 2005
Location: UAE
Member
Hi dear!
all points are cleared...
but here i don't want to use "INSERT INTO table2 SELECT * FROM table1"
bcoz i also want to do more operation on the current got row one by one......so is there any way to insert in table2 using c1_rec.* . if i use the folowing statement
insert into table2 values (c1_rec.column1,c1_rec.column2) then it works fine.

but in a real scenario i don't want to write the column name bcoz table contain more than 30 columns.

waiting for reply.......

Regards,
Re: What is problem [message #228784 is a reply to message #228766] Wed, 04 April 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your point.
If you have to make complex operations on record fields then you have to copy them and use INSERT VALUES with a list of fields.

After all, writing once 30 fields is not a so painful task.

Regards
Michel
Re: What is problem [message #228792 is a reply to message #228756] Wed, 04 April 2007 03:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 04 April 2007 08:52
They are so many errors!

1/

Syntax is either (without c1_rec declaration):
FOR c1_rec in (SELECT * from table1) LOOP ...

or (with c1_rec declaration):
OPEN c1;
LOOP
  FETCH C1 into c1_rec;
  EXIT WHEN C1%NOTFOUND;
  ...
END LOOP;
CLOSE c1;

but not
FOR c1_rec IN c1 LOOP ...




Not true. This is another way to use implicit cursor loops!
This construct allows you to explicitly define your cursors in the declaration section and still let PL/SQL handle the open/close/loop-termination.

[Updated on: Wed, 04 April 2007 03:35]

Report message to a moderator

Re: What is problem [message #228793 is a reply to message #228792] Wed, 04 April 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank,

Thanks for the correction, I didn't know this one.

Regards
Michel
Re: What is problem [message #228795 is a reply to message #228745] Wed, 04 April 2007 04:19 Go to previous message
fakhar55
Messages: 99
Registered: September 2005
Location: UAE
Member
Also For Loop Cursors are widely used in database level triggers, procedures and functions.
Previous Topic: SQL query cleanup
Next Topic: SQL Help Needed: Split & Replace - Update Column Value
Goto Forum:
  


Current Time: Sun Dec 04 02:53:36 CST 2016

Total time taken to generate the page: 0.23023 seconds