Re: inserting records from a table to another using %rowtype

swoop wrote:
> Hi I would like to copy records from table a to table b using %rowtype.
>
> Here is the code I have so far. The error I get is on line 15 (FOR
> rec1 in ab_cur)
>
> *
> ERROR at line 15:
> ORA-06550: line 15, column 13:
> PLS-00103: Encountered the symbol "AB_CUR" when expecting one of the
> following:
> (
>
> I hope someone can help.
>
> Cheers.
>
>
> DECLARE
>
> CURSOR ab_cur IS
>
> SELECT *
> FROM a
> WHERE This = 1
> AND That = 'Y';
>
> b_rec b%rowtype;
>
> BEGIN
> OPEN ab_cur
>
> FOR rec1 in ab_cur
>
> LOOP
> INSERT INTO b VALUES(rec1.????, rec1.????, rec1.????,
> rec1.????, rec1.????, rec1.????, rec1.????, rec1.????,
> rec1.????, rec1.????, rec1.????, rec1.????, rec1.????,
> rec1.????, rec1.????, rec1.????, rec1.????, rec1.????,
> rec1.????, rec1.????, rec1.????, rec1.????, rec1.????, );
>
> EXIT WHEN ab_cur%NOTFOUND;
>
> END LOOP;
> CLOSE ab_cur
> END;
And what does this do that couldn't be done far more efficiently by:

INSERT INTO b
(col1, col2, col3, ...)
SELECT *
FROM a
WHERE This = 1
AND That = 'Y';

As Sybrand says ... why no version number. But as of Oracle 9i there is almost no reason to ever use a cursor loop for anything.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)