Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Referring to cursor values with dot notation

Re: Referring to cursor values with dot notation

From: Keith Matthews <Keithm_at_sweeney.demon.co.uk>
Date: 1997/03/01
Message-ID: <857206401snz@sweeney.demon.co.uk>#1/1

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 Matthews
Received on Sat Mar 01 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US