Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Referring to cursor values with dot notation
In article <5f7h8t$kpi_at_news.acns.nwu.edu>
stevem_at_GSA-ORSP.CROWN.NWU.EDU "Steven Moyano" writes:
>
> Is there anyway to refer to a column in the cursor by the cursor name, for
> use in an insert statement? The follow raises the PLS-00225: subprogram or
> cursor 'C' reference is out of scope message. The cursor is declared between
> the AS and BEGIN of the create procedure statement, and the OPEN C; statement
> does not fail, perhaps suggesting that the location within the block structure
> is not the problem.
>
> Thanks very much for your time and help.
>
> CURSOR C IS
> SELECT *
> FROM TABLE1;
>
> INSERT INTO TABLE2 (FIELD1, FIELD2)
> VALUES (C.FIELD1, C.FIELD2);
>
>
To the best of my knowledge there is no way to transport references/values
directly from one DML statement to another this way. However if you use the
cursor in a cursor loop you get implicit declaration of variables which will
achieve your desired result.
eg
FOR tab1_rec IN C LOOP
...
...
Insert INTO TABLE2 (field1, field2) VALUES (tab1_rec.field1, tab2.field2);...
END LOOP; However you should (and already have I hope) ask yourself if you could be doing it all in one DML statement which would be much more efficient.
-- Keith MatthewsReceived on Sat Mar 01 1997 - 00:00:00 CST