Home » SQL & PL/SQL » SQL & PL/SQL » All records in cursor (10gR2 Unix)
All records in cursor [message #437018] Wed, 30 December 2009 05:56 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Can someone correct the INSERT statement in below code.I dnt want to specify VALUES ,but insert all records.
Thanks

DECLARE
cnt NUMBER:=0;
CURSOR cr IS SELECT * FROM SCOTT.emp_1;


BEGIN
FOR rec IN cr
LOOP
  INSERT INTO HR.emp_2(*) values (rec.*);
    cnt := cnt + 1;
    IF ( mod( cnt, 1000 ) ) = 0 THEN
       commit;
    END IF;
END LOOP;

END;
Re: All records in cursor [message #437022 is a reply to message #437018] Wed, 30 December 2009 06:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hint: you can't just make up syntax in Oracle, you actually have to look it up if you don't know.

correcting your code would give:
DECLARE
cnt NUMBER:=0;
CURSOR cr IS SELECT * FROM SCOTT.emp_1;

BEGIN
FOR rec IN cr LOOP
  INSERT INTO HR.emp_2 values rec;
END LOOP;
commit;
END;

but the better way to do it would be:
BEGIN
  INSERT INTO hr.emp_2
  SELECT * 
  FROM   SCOTT,emp_1;
END;


Both these solutions rely upon the table structures being exactly the same, in column names and order, which is why it is very good practice to explicitly state the list of columns that you're using.
Re: All records in cursor [message #437035 is a reply to message #437022] Wed, 30 December 2009 07:48 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Wed, 30 December 2009 07:01

Both these solutions rely upon the table structures being exactly the same, in column names and order, which is why it is very good practice to explicitly state the list of columns that you're using.


This is one of those time Jrowbottom will hit his hand against his head and say "Doh!" because he knew he meant to say "column types" instead of "column names."

But naming the columns is the proper way to do it.


Re: All records in cursor [message #437040 is a reply to message #437035] Wed, 30 December 2009 07:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'll give you a 1/2 D'oh for that.

If the column types are in the same order, but the names aren't then it will still work in the sense that it won't raise an exception, but if you've transposed the EMP_NO and AGE columns, your data is still garbage.
Re: All records in cursor [message #437041 is a reply to message #437040] Wed, 30 December 2009 08:03 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
True, and I was a bit lazy, because the column types do not have to be exactly the same, since a VARCHAR2(10) will fit into a VARCHAR2(20).

Most all of these would be fixed by naming the columns.
Re: All records in cursor [message #437048 is a reply to message #437041] Wed, 30 December 2009 08:56 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
And let's not forget getting lucky with implicit conversion:

SQL> create table t1 (a1 number, a2 varchar2(20));

Table created.

SQL> create table t2 (a1 varchar2(20), a2 varchar2(20));

Table created.

SQL> insert into t1 values (1, 'One');

1 row created.

SQL> insert into t2 select * from t1;

1 row created.


Previous Topic: display column to row
Next Topic: Log details for exception for the sql
Goto Forum:
  


Current Time: Tue Sep 27 21:11:42 CDT 2016

Total time taken to generate the page: 0.13360 seconds