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

Home -> Community -> Usenet -> c.d.o.misc -> Re: which package?

Re: which package?

From: Stephan Born <stephan.born_at_beusen.de>
Date: Tue, 04 Apr 2000 13:45:59 +0200
Message-ID: <38E9D5F7.D7A11365@beusen.de>


> Hi everybody,
> I have a VB6 application that has to drop some Oracle indexes. I do it with
> a shell of the sql plus but somebody told me that it is possible to do this
> using a PACKAGE (because in a stored procedure I cannot use the DROP).
> Does anybody know something about this package and how to use it?
> Thanks in advance.

He meant the package DBMS_SQL. It provides dynamic SQL...

Try that:

create or replace procedure drop_index (index_name in varchar2) is

    CurID integer;
    stmt_template varchar2(2000) := 'drop index _$NAME$_';

    stmt             varchar2(2000);
    Dummy            integer;

begin

    CurID := dbms_sql.open_cursor();
    stmt := replace (stmt_template, '_$NAME$_', index_name);     dbms_sql.parse (CurID, stmt, dbms_sql.native);     Dummy := dbms_sql.execute (CurID);
    dbms_sql.close_cursor (CurID);
exception

    when others then

        if dbms_sql.is_open(CurID) then
            dbms_sql.close_cursor(CurID);
        end if;

end drop_index;

/

I didnt try this procedure but i should lead you the right way.

BTW.: system-Grants via roles are not usable from within a procedure... you have to grant rights like CREATE, ... directly to the user which executes this
procedure....at least in Oracle 7.3

Let me know if I could help you

Regards, Stephan

--


Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-21          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------


Received on Tue Apr 04 2000 - 06:45:59 CDT

Original text of this message

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