Re: which package?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 4 Apr 2000 12:28:29 +0200
Message-ID: <954844162.11052.2.pluto.d4ee154e@news.demon.nl>

ilkaos <ilkaos_at_trae.ka> wrote in message news:10qt304$3pc$1_at_fe2.cs.interbusiness.it...
> 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.
>
>

In Oracle 7 and Oracle 8.0 use dbms_sql, in Oracle 8i use execute immediate.

A short wrapper for dbms_sql is the following (this will execute any DDL command)

create or replace procedure do_sql (sqlstr in varchar2) is cur_handle integer;
begin
cur_handle := dbms_sql.open_cursor;

dbms_sql.parse(cur_handle, sqlstr, dbms_sql.native);
dbms_sql.execute(cur_handle); -- Oracle 8 only?
dbms_sql.close_cursor(cur_handle);

exception
when-others then

   dbms_output.put_line(dbms_utility.format_error_stack);    if cur_handle > 0
   then
    dbms_sql.close_cursor(cur_handle);
   end if;
end;
/

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Apr 04 2000 - 05:28:29 CDT