Re: How to avoid PLS-00413 when using cursorType
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 DBAReceived on Wed Apr 30 2008 - 15:49:56 CDT