Object has been deleted Error on triggers

[Roeland]

Hi,

Execute this in a command window

create table Temp_Test
(id number(9)
)
/
Create Or Replace Trigger "Temp_Test_Tr"
  after insert or update or delete on Temp_Test
  for each row
begin
  Null;
end ;
/

Everything works and compiles fine.

Now go to the triggers and
1) Right-Click on "Temp_Test_Tr" -> "Recompile"
Result:

ORA-04080: trigger 'TEMP_TEST_TR' does not exist

2) Right-Click on "Temp_Test_Tr" -> "View"
The trigger appears in a Program window.
Recompile this window.
Result:

Object TEMP_TEST_TR has been deleted

This is rather scaring, but the trigger still exists after this 2 error messages.

Could this be solved? The problem here is that the trigger name is case sensitive.

Regards,

Roeland

PS: Look also at Object Has Been Deleted Error when describing v$ system object .

Is this related?

 

[Roeland]

The problem is even worse...

3) Try to delete the trigger
Right-Click on "Temp_Test_Tr" -> "Drop"

Error dropping TEMP_TEST_TR:
ORA-04080: trigger 'TEMP_TEST_TR' does not exist

4) Open a SQL Window
type "drop trigger "
Drag and drop the trigger just after the typed text and choose "Name".

Now you have

drop trigger temp_test_tr

executing this gives

Error dropping TEMP_TEST_TR:
ORA-04080: trigger 'TEMP_TEST_TR' does not exist

5) It's only when I type

drop trigger "Temp_Test_Tr"

that the trigger is dropped.

So could these issues also been solved ("Dropping" and "Drag&Drop")

Thanks,

Roeland

 

[Marco Kalter]

This is indeed an issue with mixed case identifiers. We'll check it out.

 

[Claus Pedersen]

I have some more:

1) I have a table mytable with a mixed case column name "myColumn".
When I use code completion, PLD gives mytable.mycolumn as name and I get an error ORA-00904: invalid identifier when I select from the table. Code completion should return the correct column name mytable."myColumn"

2) When I rename mytable into "myTable" by rightclicking in the object browser and select Rename, I get the message
Object "MYTABLE" has been deleted
and the object browser now shows the name as "MYTABLE". When I refresh the object browser, then name is now myTable, but I can not select from it by rightclicking and select Query or other operations, as reported by Roeland.

No big deal for me personally, as I only accidentally renamed a column into mixed case and got this error, but since Oracle supports object names with lower case letters and other symbols, PLD should also suppport this in code assistant as well as in object browser and elsewhere.

 

[Roeland]

[quote:Marco Kalter]This is indeed an issue with mixed case identifiers. We'll check it out.[/quote]
Are these issues being addressed in V9?

I'm working with beta 4 and these issues are still there.

Also invalid triggers (objects?) are not compiled with the "Compile Invalid Objects" tool when they are case sensitive.

When edited, they are still being "Deleted"!? when compiled, although they have just compiled fine.

Roeland

 

[Marco Kalter]

No, this has not yet been addressed.

 

[Roeland]

[quote: Marco KalterL/SQL Developer BetaL/SQL Developer 9.0 Beta 5 released]Right-Click > Recompile did not work for objects with mixed case name[/quote]

I have tested this on some triggers and this is still not solved in PL/SQL Dev 9.0.0.1595

Roeland

 

[Marco Kalter]

Works for me. Can you show me an example?

 

[Roeland]

I have retried the example given in the first post.
This still gives the same errors.

Roeland

INFO:

PL/SQL Developer
Version 9.0.0.1595
Windows 7 6.1 Build 7600

Physical memory : 2.097.152 kB (2.097.152 available)
Paging file : 4.194.304 kB (4.194.304 available)
Virtual memory : 2.097.024 kB (1.889.988 available)

Parameters
C:\Program Files\PLSQL Developer Beta\plsqldev.exe

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False

Plug-Ins
*Version Control Interface 1.2 (C:\Program Files\PLSQL Developer Beta\PlugIns\VCS.dll)
(* is Active)

DLLs
D:\Oracle\product\11.2.0\client\bin\oci.dll

TNS File
D:\Oracle\product\11.2.0\client\Network\Admin\tnsnames.ora

Using
Home: OraClient11g_home1
DLL: D:\Oracle\product\11.2.0\client\bin\oci.dll
OCI: version 11.1
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Character Sets
Character size: 4 byte(s)
CharSetID: 873
NCharSetID: 2000
Unicode Support: True
NLS_LANG: DUTCH_BELGIUM.AL32UTF8
NLS_CHARACTERSET: AL32UTF8
NLS_NCHAR_CHARACTERSET: AL16UTF16

 

[Marco Kalter]

We'll check it out.

 

[Roeland]

[Quote: PL/SQL Developer 9.0 Beta 6 released]Right-Click > Recompile and Drop did not work for all objects with mixed case name[/quote]

This does indeed work, BUT ALAS:

-> when you "View" or "Edit" such an object and then try to compile this, you still get the "Object TEMP_TEST_TR has been deleted". See above example.

Regards,

Roeland

PL/SQL Dev v9.0.0.1596 (Beta 6)

Edit: This error occurs when the object is in the Object Browser.
1) View the Object
2) Drop the object
3) Compile the View (multiple times) => This works
4) Refresh the Object Browser
5) Compile the View => "Object has been Deleted"

 

[Roeland]

Hi Marco,

[quote:Marco KalterL/SQL Developer BetaL/SQL Developer 9.0 Beta 7 released]Compiling an object with mixed case name could lead to "Object has been deleted" message[/Quote]

I'm really sorry, but this still isn't fixed.

See above examples:
1) Right-Click on "Temp_Test_Tr" -> "Recompile" ==> Fixed
2) View "Temp_Test_Tr" and Recompile ==> FIXED/Not Fixed
After searching a lot:

a) Start Pl/SQL Dev
  b) View "Temp_Test_Tr" and Recompile => Ok
  c) Open "Compare User Objects"
  d) Recompile "Temp_Test_Tr" => Object TEMP_TEST_TR has been deleted => NOT FIXED
  e) Close "Compare User Objects"
  f) View "Temp_Test_Tr" and Recompile => Ok

So the error comes from the list of objects in "Compare User Objects".
Notice that Dropping works when "Compare User Objects" is open. Object is also dropped in "Compare User Objects". But when compiling this object, it's not added to the "Compare User Objects".

Please check "Compare Table Data" also for this bug.

3) Right-Click "Temp_Test_Tr" and Drop ==> Fixed
4) Drag & Drop Mixed case Object -> Select "Name" ==> NOT FIXED

Roeland

PL/SQL Dev 9.0.0.1597 (Beta 7)

 

[Roeland]

Marco,

Issue 2) and 4) are still not fixed. Are they going to be fixed?

Regards,

Roeland

PL/SQL Dev 9.0.0.1599 (Release Candidate)

 

[Marco Kalter]

They will not be fixed for 9.0.0 but at a later time.