Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re:Question: Why cann't I drop table?

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

From: <dgoulet_at_vicr.com>
Date: Thu, 8 Jun 2000 17:10:51 -0400
Message-Id: <10522.108303@fatcity.com> 





Re-write the package as follows (namely get rid of the semicolon when creating
the ISQL variable):



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;

____________________Reply Separator____________________


Subject:    Question: Why cann't I drop table?
Author: "Guang Mei" <zlmei_at_hotmail.com>


Date:       6/8/00 12:31 PM



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;
/







Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com


-- 
Author: Guang Mei
  INET: zlmei_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


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

To REMOVE yourself from this mailing list, send an E-Mail message


Received on Thu Jun 08 2000 - 16:10:51 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US