0

I have a script that I am running for homework that looks like so:

drop table burger_king_locations_clean;
drop table burger_king_locations_unresolv;


create table burger_king_locations_clean
(
    ...
);  

create table burger_king_locations_unresolv
(
    ....
);

But when I run this I get the following message and I don't understand because I know the table is gone and there is no view or any other object with that name.

Table dropped.

drop table burger_king_locations_unresolv
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


Table created.

create table burger_king_locations_unresolv
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

I don't understand because this exact thing was working before but I put my computer in sleep mode and then came back to this and now it doesn't work at all. Any ideas?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
csteifel
  • 2,854
  • 6
  • 35
  • 61
  • noticed that you have at least one meassage in front of each drop/create that says its already been dropped or created. you can only run it once. you will get an error if you keep trying to run the same drop over and over(same with creates) – Limey Nov 26 '12 at 20:57
  • ..... yes there is only one create for both tables..... and only one drop for both tables.... – csteifel Nov 26 '12 at 21:00
  • turn off the computer for real; restart it for real. If the reason of the new behaviour is that you put it in sleep mode, there's no reason why we try to find some strangeness in the sql statements and existing objects themselves... if restarting properly makes everything works fine as before, then we can suppose something odd happens when you go into sleep mode and then come back. – ShinTakezou Nov 26 '12 at 21:12
  • 1
    @ShinTakezou - shouldn't we be beyond turn it off and switch it on again" as a diagnostic tool by now? – APC Nov 26 '12 at 21:15
  • @APC yes we should, provided that it does not prove that the problem here is not about sql and oracle themselves, but about some system oddness happened since s/he went in sleep mode. So, it is actually a diagnostic tool, unless you want the OP go in debugging what happens when his/her computer goes into sleep mode and then comes back – ShinTakezou Nov 26 '12 at 21:17
  • Try adding a commit statement after your drop the tables and before you recreate them. – Limey Nov 26 '12 at 21:21
  • ("I don't understand because this exact thing was working before but I put my computer in sleep mode" is a clue of strangeness, isn't it? or do you see something odd in the statements that are common statements it happens to me to do almost everyday or so?) – ShinTakezou Nov 26 '12 at 21:23
  • 2
    in your sample, you can see two drop statements + THREE create statements (2 ok "table created"x2, and one failure). please verify in your script that you aren't doing this twice..ie no "/" + semicolon etc? – DazzaL Nov 26 '12 at 21:28
  • 1
    @Limey - this is just *voodoo* : DDL statements in Oracle already issue (implicit) commits, before and after the executed command. – APC Nov 26 '12 at 21:56
  • @APC its been a while since I have done oracle, so I don't remember the demands, and it can't hurt (and its not quite as silly as rebooting ;) – Limey Nov 27 '12 at 14:43
  • You could trap for error of table not existing and ignore it... on a drop... http://stackoverflow.com/questions/1799128/oracle-if-table-exists – xQbert Nov 01 '16 at 15:30

2 Answers2

1

"I have a script that I am running for homework that looks like so"

So you say. But the posted output shows the execution of two DROP statements, one of which fails, and two CREATE statements, one of which is run twice.

Why did the drop table burger_king_locations_unresolv; fail? Who can tell? Perhaps you had already dropped it, perhaps the previous create had failed.

Likewise it is not possible for us to tell you why the create table burger_king_locations_unresolv ran twice. Perhaps your script has a stray back slash following the semi-colon.

APC
  • 144,005
  • 19
  • 170
  • 281
  • 1
    but what if the very same script worked as expected *before* the sleep state? it's not clear, nor the OP is making it clearer if there's a dependency or he wrote from scratch the script (wrongly) once he resurected the machine (btw if it's not a gross script error, the only way to know if it was the sleeping state is to turn off and then on again the machine... general statements are general but they do not prove exceptions are automatically wrong and can't be applied to specific situations — not the only way indeed, but the easier for sure) – ShinTakezou Nov 26 '12 at 22:11
  • I'd be pretty disturbed if an Oracle database managed to achieve DDL inconsistency because the machine was put into a sleep state. I agree with APC's likely correct diagnostic of stray slashes in the script (leading to repeat execution of statements). The only way we can know for sure is with the complete unedited script. – Ben Nov 27 '12 at 00:47
0

Wouldn't you need to purge the table?

drop table burger_king_locations_clean PURGE;
drop table burger_king_locations_unresolv PURGE;
Grambot
  • 4,370
  • 5
  • 28
  • 43
  • 1
    when your drop a table into the recycle bin, its renamed, so as not to conflict with any real objects, so this isn't needed. The item in the recycle bin would get an object name like `BIN$zzLXL+emx1zgQAB/AQEIpQ==$0` – DazzaL Nov 26 '12 at 21:32