Re: Question: Why cann't I drop table?

The answer to this is roles are *NOT* enabled during stored procedure execution (and compilation). In Oracle 8i you can circumvent this problem by either running the procedure with invokers rights, prior to 8i you have to grant the privilege to the user directly. In this particular case you'd better make sure the owner of the tables also owns the packages, as the owner always have the privilege to do anything.

Hth,

Sybrand Bakker, Oracle DBA

<gmei_at_my-deja.com> schreef in berichtnieuws 8horvg$eem$1_at_nnrp1.deja.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
>
> IS
> ------------------------------------------------------------------
> 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;
> /
> GRANT EXECUTE ON hpxpkg_catalog_management TO PUBLIC;
> /
> --------------------
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jun 08 2000 - 00:00:00 CDT