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.108302@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: Received on Thu Jun 08 2000 - 16:10:51 CDT