Question: Why cann't I drop table?

From: Guang Mei <zlmei_at_hotmail.com>
Date: Thu, 08 Jun 2000 19:25:01 GMT
Message-Id: <10522.108283@fatcity.com>

Hi:

I am totally confused why my "drop table .. " statement using dynamic sql does not work. The oracle user_name I login has dba privilege. And as a test, I am trying to drop table HPXAREA in schema CATALOG_MANAGEMENT;

Here is how I tested:

SQL> select user from dual;

USER

WEBSTORE_OWNER SQL> select owner, object_name, object_type, status 2 from all_objects
3 where object_name ='HPXPKG_CATALOG_MANAGEMENT';

OWNER                          OBJECT_NAME                    OBJECT_TYPE    
     STATUS
------------------------------ ------------------------------ 
------------------ -------
PUBLIC                         HPXPKG_CATALOG_MANAGEMENT      SYNONYM        
     VALID
WEBSTORE_OWNER                 HPXPKG_CATALOG_MANAGEMENT      PACKAGE        
     VALID
WEBSTORE_OWNER                 HPXPKG_CATALOG_MANAGEMENT      PACKAGE BODY   
     VALID

SQL> select * from CATALOG_MANAGEMENT.HPXAREA 2 ;

AREAID          ZONEID A LOCALE                                              
          DELIVERYD

------------ --------- -

------------------------------------------------------------ ---------
02138                  T

SQL> drop table CATALOG_MANAGEMENT.HPXAREA;

Table dropped.

SQL> select * from CATALOG_MANAGEMENT.HPXAREA; select * from CATALOG_MANAGEMENT.HPXAREA

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

SQL> copy from demodb/demodb_at_aredev00 -
>to CATALOG_MANAGEMENT/CATALOG_MANAGEMENT_at_webstore-kungfu -
>create HPXAREA -
>using select * from HPXAREA;

Array fetch/bind size is 1000. (arraysize is 1000) Will commit after every array bind. (copycommit is 1) Maximum long size is 80. (long is 80)
Table HPXAREA created.

1 rows selected from demodb_at_aredev00. 1 rows inserted into HPXAREA.
1 rows committed into HPXAREA at CATALOG_MANAGEMENT_at_webstore-kungfu.

SQL> select * from CATALOG_MANAGEMENT.HPXAREA;

AREAID          ZONEID A LOCALE                                              
          DELIVERYD

------------ --------- -

------------------------------------------------------------ ---------
02138                  T

SQL> begin

  2    hpxpkg_catalog_management.drop_object('table',
  3                                          'CATALOG_MANAGEMENT',
  4                                          'hpxarea');

5 end;
6 /
DROP table CATALOG_MANAGEMENT.hpxarea;
exception raised
ERROR CODE: 0
ERROR MESSAGE: ORA-0000: normal, successful completion

PL/SQL procedure successfully completed.

SQL> select * from CATALOG_MANAGEMENT.HPXAREA;

AREAID          ZONEID A LOCALE                                              
          DELIVERYD

------------ --------- -

------------------------------------------------------------ ---------
02138                  T

SQL> So, why couldn't I drop the table?

Thanks.

Guang

PS: Here is the code for package "hpxpkg_catalog_management":

/***********************************************************************
* Package Name: hpxpkg_catalog_management
* Description:  To manage the process of refreshing data from "catalog
*               management" server to "production" server
*
* Restriction: None.
*
* Revisions:   None.
*
* Authors:     Guang Mei
*
* Date Written: 6/10/2000
*
* Special Notes: None.

***********************************************************************/
------------------------------------------------------------------

CREATE OR REPLACE PACKAGE hpxpkg_catalog_management IS

PROCEDURE drop_object(

    obj_type_in             IN VARCHAR2,
    schma_name_in           IN VARCHAR2,
    obj_name_in             IN VARCHAR2);

  PROCEDURE copy_table(
    from_user_in            IN VARCHAR2,
    from_password_in        IN VARCHAR2,
    from_serv_in            IN VARCHAR2,
    from_schma_name_in      IN VARCHAR2,
    to_user_in              IN VARCHAR2,
    to_password_in          IN VARCHAR2,
    to_serv_in              IN VARCHAR2,
    to_schma_name_in        IN VARCHAR2,
    table_name_in           IN VARCHAR2);

END hpxpkg_catalog_management ;
/

CREATE OR REPLACE PACKAGE BODY hpxpkg_catalog_management

PROCEDURE drop_object(

    obj_type_in             IN VARCHAR2,
    schma_name_in           IN VARCHAR2,
    obj_name_in             IN VARCHAR2)
IS
    lSQL                    VARCHAR2(2000);
    lCURSOR_HANDLE          INTEGER;
    lERROR_CODE             NUMBER := SQLCODE;
    lERROR_MSG              VARCHAR2(300) := SQLERRM;

BEGIN
lSQL := 'DROP '|| obj_type_in ||' '|| schma_name_in || '.' || obj_name_in || ';';

DBMS_OUTPUT.PUT_LINE (lSQL);
lCURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE (lCURSOR_HANDLE, lSQL,dbms_SQL.V7);
    DBMS_OUTPUT.PUT_LINE ('after parse');
    DBMS_SQL.CLOSE_CURSOR (lCURSOR_HANDLE);

EXCEPTION
WHEN OTHERS
THEN

    DBMS_OUTPUT.PUT_LINE ('exception raised');
    DBMS_OUTPUT.PUT_LINE ('ERROR CODE: ' || lERROR_CODE);
    DBMS_OUTPUT.PUT_LINE ('ERROR MESSAGE: ' || lERROR_MSG);
    DBMS_SQL.CLOSE_CURSOR (lCURSOR_HANDLE);
END drop_object;
------------------------------------------------------------------

-- copy table and data from one database to another database: PROCEDURE copy_table(

    from_user_in            IN VARCHAR2,
    from_password_in        IN VARCHAR2,
    from_serv_in            IN VARCHAR2,
    from_schma_name_in      IN VARCHAR2,
    to_user_in              IN VARCHAR2,
    to_password_in          IN VARCHAR2,
    to_serv_in              IN VARCHAR2,
    to_schma_name_in        IN VARCHAR2,
    table_name_in           IN VARCHAR2)
IS
    cursor_id               INTEGER;
    lsql                    VARCHAR2(2000);

BEGIN
lsql := 'COPY FROM ' || from_user_in || '/' || from_password_in

            || '@' || from_serv_in
            || ' TO ' || to_user_in || '/' || to_password_in
            || '@' || to_serv_in
            || ' CREATE ' || to_schma_name_in || '.' || table_name_in
            || ' USING SELECT * FROM '|| from_schma_name_in
            || '.' || table_name_in ;

    dbms_output.put_line (lsql);

dbms_sql.parse(cursor_id, lsql, dbms_SQL.V7); dbms_sql.close_cursor(cursor_id);
EXCEPTION
WHEN OTHERS
THEN
dbms_sql.close_cursor(cursor_id);
END copy_table;

END hpxpkg_catalog_management;
/
CREATE PUBLIC SYNONYM hpxpkg_catalog_management FOR hpxpkg_catalog_management; Received on Thu Jun 08 2000 - 14:25:01 CDT