pl/sql developer ora-01001: invalid cursor

[Bruce Carson]

We run cursors within our select statements all the time. Recently it stopped working and am not sure why. The following query isn't a query we are running but does demonstrate the issue.

Execute the following query in a sql window:
select t.table_name,
cursor (select c.column_name
from user_tab_cols c
where c.table_name = t.table_name) as columns
from user_tables t;

The query returns the following output:
PLSQL_PROFILER_RUNS
PLSQL_PROFILER_UNITS
PLSQL_PROFILER_DATA
STAFF_TR1
STAFF
LOAD_FROM_ICM_BASELINE_TMP
LOAD_FROM_ICM_BASELINE

When I click the box next to the to see the contents of the cursor a new window opens but I get the error ora-01001: Invalid Cursor. The strange thing is this used to work but something has changed to give this error.

Environment as follows:
PL/SQL Developer 7.0.3.1123
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production

 

[Marco Kalter]

This works fine for me. To obtain some more diagnostic information, can you modify the shortcut and add the debugsql parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debugsql

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.

 

[Bruce Carson]

Debug file was sent to Allround Automations

 

[ronvoe]

Hello,

i have a similar problem.
The original select works for me too, but if I add a where-clause to the outer select, I get also an ora-01001 when trying to open the cursors.

Example:

select t.table_name,
cursor (select c.column_name
from user_tab_cols c
where c.table_name = t.table_name) as columns
from user_tables t where t.table_name = 'MYTABLE';

In sqlplus this select works.

PL/SQL Developer Version 7.0.3.1123.

 

[Bruce Carson]

No solution yet. Haven't had much chnace to work on this and the users have learned to live without it (they are not happy but are surviving).

 

[Marco Kalter]

select t.table_name,
cursor (select c.column_name
from user_tab_cols c
where c.table_name = t.table_name) as columns
from user_tables t where t.table_name = 'MYTABLE';

This fails with ORA-01001 for me too. We'll check it out.

 

[soldado]

Marco,
Have you guys found a solution to this problem? I amd getting the same error in PL/SQL Dev version 7.1.1.1339.

I'm running the same query:
select t.table_name,
cursor (select c.column_name
from user_tab_cols c
where c.table_name = t.table_name) as columns
from user_tables t where t.table_name = 'MYTABLE';

 

[Marco Kalter]

No, we don't have a solution yet. It might be an Oracle bug.

 

[Marcel Hoefs]

Originally posted by Marco Kalter:
No, we don't have a solution yet. It might be an Oracle bug.

According to me it doesn't seem to be an Oracle bug. I tested it on Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit and Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit using PL/SQL Developer 6.0.6.947 and TOAD 8.6.1.0.
On both databases using PL/SQL Developer I get the ORA-01001 error. When I use TOAD with the same query then there is no problem. Both tools use the same Oracle Client software.