Dropped procedure shows up in USER_OBJECTS

Question

I've composed this SQL*Plus script, based upon other's suggestions, to remove all objects from current user:

SET ECHO OFF
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TIMING OFF
SET TIME OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 100

SET TERMOUT OFF
SPOOL eliminar-todo
/*
 * Descartamos objetos en la papelera ya que intentar eliminarlos directamente genera
 * «ORA-38301: no se puede realizar DDL/DML en objetos de la papelera de reciclaje»
 */
SELECT 'DROP ' || UO.OBJECT_TYPE || ' "' || UO.OBJECT_NAME || '"' ||
    DECODE(UO.OBJECT_TYPE,
        'CLUSTER', ' INCLUDING TABLES CASCADE CONSTRAINTS',
        'OPERATOR', ' FORCE',
        'TABLE', ' CASCADE CONSTRAINTS',
        'TYPE',  ' FORCE',
        'VIEW', ' CASCADE CONSTRAINTS',
    '') || ';'
FROM USER_OBJECTS UO
LEFT JOIN USER_RECYCLEBIN UR ON UO.OBJECT_NAME=UR.OBJECT_NAME
WHERE UO.OBJECT_TYPE IN (
    'CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION', 'DIRECTORY', 'FUNCTION', 'INDEX TYPE',
    'JAVA', 'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR', 'OUTLINE', 'PACKAGE', 'PROCEDURE',
    'SEQUENCE', 'SYNONYM', 'TABLE', 'TYPE', 'VIEW'
)
AND UR.OBJECT_NAME IS NULL
ORDER BY UO.OBJECT_TYPE, UO.OBJECT_NAME;
/
SPOOL OFF
SET TERMOUT ON

@eliminar-todo.lst

PURGE RECYCLEBIN;

I had an older simpler version that worked fine all the time:

select 'drop '||object_type||' '||object_name||
       decode(object_type,'CLUSTER',' including tables cascade constraints',
                          'OPERATOR', ' force',
                          'TABLE',' cascade constraints',
                          'TYPE', ' force',
                          'VIEW',' cascade constraints',
                          '')||';'
from user_objects
where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
                      'DIRECTORY', 'FUNCTION', 'INDEX TYPE', 'JAVA',
                      'LIBRARY', 'MATERIALIZED VIEW', 'OPERATOR',
                      'OUTLINE', 'PACKAGE', 'PROCEDURE', 'SEQUENCE',
                      'SYNONYM', 'TABLE', 'TYPE', 'VIEW')
order by object_type, object_name

... but triggered syntax errors when user had BIN$... tables.

I tried double-quoting identifiers but got ORA-38301 instead:

can not perform DDL/DML Over Object in Recycle Bin

Current version attempts to avoid issuing DELETE TABLE statements for BIN$... tables. To do so, I join user objects with user recycle bin and omit matches. I've no longer had issues with BIN$... tables again. However, now I get this error message like 1 in 3 times:

DROP PROCEDURE "RESET_SEQUENCE"
*
ERROR en línea 1:
ORA-04043: el objeto RESET_SEQUENCE no existe

Apparently, procedure RESET_SEQUENCE shows up in USER_OBJECTS but no longer exists when DROP PROCEDURE "RESET_SEQUENCE" runs.

How's that possible?
How can I fix it?

Why don't you just use your original query and add "AND object_name not like 'BIN$%' " instead? — Frank Schmitt, Mar 22 '13 at 13:33
@FrankSchmitt I'm not an oracle expert so I don't know if all bin items start with `BIN$` and all items that start with `BIN$` are bin items. The `USER_RECYCLEBIN` view looked like a safer path to me. Whatever, my `ORA-04043` does not seem related to this, does it? — Álvaro González, Mar 22 '13 at 13:36
No, your error is not related to this. I was just curious :-) — Frank Schmitt, Mar 23 '13 at 06:51

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

firstly, in your script you're running the cursor twice because you have ; and /.

ORDER BY UO.OBJECT_TYPE, UO.OBJECT_NAME;
/

From SQL: the semicolon or the slash?:

There is a huge difference in SQL*Plus between the meaning of a / and a ; because they work differently.

The ; ends a SQL statement, whereas the / executes whatever is in the current "buffer". So when you use a ; and a / the statement is actually executed twice.

edited May 23 '17 at 12:04

Community

1
1

answered Mar 22 '13 at 13:19

DazzaL

21,638
3
49
57

Yes... There's no need for `/` here, is there? (Just checked file history, previous version didn't have `;`.) Thank for the pointer. – Álvaro González Mar 22 '13 at 13:30
1

@ÁlvaroG.Vicario correct. either `;` or `/` is sufficient for a dml statement. – DazzaL Mar 22 '13 at 13:33
@a_horse_with_no_name - That link perfectly explains my problem. Now I'm 100% sure than my bogus `;/` is the culprit. (I've taken the liberty of adding it to the answer.) – Álvaro González Mar 22 '13 at 13:47

Dropped procedure shows up in USER_OBJECTS

1 Answers1