| All records in cursor [message #437018] |
Wed, 30 December 2009 05:56  |
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  |
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 |
joy_division
Messages: 4963
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 |
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 |
joy_division
Messages: 4963
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 |
 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
