How to avoid PLS-00413 when using cursorType

#1

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.

#2

Martin schrieb:

Quote:

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

#3

Martin schrieb:

Quote:

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

#4

Martin schrieb:

Quote:

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

#5

Martin schrieb:

Quote:

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

#6

On Apr 30, 7:34*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:

Quote:

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

#7

On Apr 30, 7:34*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:

Quote:

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

#8

On Apr 30, 7:34*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:

Quote:

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

#9

On Apr 30, 7:34*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:

Quote:

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

#10

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

Quote:

On Apr 30, 7:34*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) 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