Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: which package?
> 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;
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;
/
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