Re: How to avoid PLS-00413 when using cursorType

From: <sybrandb_at_hccnet.nl>
Date: Wed, 30 Apr 2008 22:49:56 +0200
Message-ID: <elmh149t1a7jk6gfio34qoe9tbg1ghsqdf@4ax.com>


On Wed, 30 Apr 2008 12:46:54 -0700 (PDT), Martin <martin.j.evans_at_gmail.com> wrote:

>On Apr 30, 7:34 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>> Martin schrieb:
>>
>>
>>
>> > Hi,
>>
>> > I have the following cut down pls/sql:
>>
>> > cur_entries cursorType;
>>
>> > IF condition
>> >    open cur_entries for
>> >      SELECT a from test_table1 for update of b;
>> > ELSE
>> >    open cur_entries for
>> >      SELECT a from test_table2 for update of b;
>> > END IF;
>>
>> > LOOP
>> >   FETCH cur_entries INTO var;
>> >   EXIT WHEN cur_entries%NOTFOUND;
>>
>> >   IF condition
>> >     -- a lot of pl/sql here
>> >     update test_table1 set b = 1 where current of cur_entries;
>> >   ELSE
>> >     -- a lot of pl/sql here
>> >     update test_table2 set b = 1 where current of cur_entries;
>> >   END IF;
>> > END LOOP;
>> > and I am getting "PLS-00413: identifier in CURRENT OF clause is not a
>> > cursor name".
>>
>> > I don't quite understand this as I thought "cur_entries" was a cursor
>> > but I guess as it is defined as cursorType Oracle does not like it. Is
>> > there any way for me to avoid this without replicating all the code
>> > with two explicitly named cursors? All the code in the procedure is
>> > identical for both cursors other than the fetch and update.
>>
>> > Thanks.
>>
>> The "current of"  clause does apply only to cursors, not to cursor
>> varibales which you are using.
>>
>> Best regards
>>
>> Maxim
>
>Thanks.
>
>Do you know if it works with reference cursors? Both the tables in
>question contain exactly the same columns so it would be possible for
>me to create a record type of those columns.
>
>Martin

What you currently use is a strongl-typed ref cursor, as opposed to a weakly-typed ref cursor, you seem to label as a ref cursor per se. So, no, it doesn't work with ref cursors, it only works with
cursor ... is
select ...
etc.
in the declararation section.
You can pass a CURSOR as a parameter to a procedure, the other way out is to explicitly select the rowid and refer to the rowid in the where clause. current of is shorthand for rowid=....

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Wed Apr 30 2008 - 15:49:56 CDT

Original text of this message