Possible to drop or create indexes in package in Oracle 10g?

I want to do some refreshing of my tables and would like to use a package to do so but I get an error when trying to drop or create an index in the package script.  Is this something that can't be done in a package?  I know you can't drop a table or view in a package, but does it also apply to table indexes?

Is there another way I can script do have an index dropped and then re-created?

Thanks.

Here's my procedure in the package:


PROCEDURE load_tables
IS
BEGIN
  -- purge the table
  delete from [table name];
  -- drop the bitmap index
  drop index [index name];

  -- do insert
  -- re-create bitmap index
END;
  
END;

Open in new window

LVL 6
jameso99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you can't execute DDL directly from within pl/sql
use execute immediate
create or replace PROCEDURE load_tables
IS
BEGIN
    -- drop the bitmap index
    EXECUTE IMMEDIATE 'drop index idx_test_bm';

    -- purge the table
    DELETE FROM test;

    -- do insert

    -- recreate the index
    EXECUTE IMMEDIATE 'create bitmap index idx_test_bm on test(n)';
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flow01Commented:
All data-definition-statements are not directly possible in pl/sql and thus a package.
You can however use the statement for dynamic processing of sql-statements in a pl/sql-environment

  -- purge the table
  execute immediate 'delete from [table name]';
  -- drop the bitmap index
  execute immediate 'drop index [index name]';
0
jameso99Author Commented:
What if I am creating an index with statistics (ie is has multiple lines)?

Ie:

CREATE BITMAP INDEX [index name] ON [table name]
([column name])
LOGGING
TABLESPACE [tablespace]
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          2M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

How would I wrap that in ticks?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
the delete you can do directly, that's dml,  not ddl  only the drop and create will fail.

you may want to consider using

execute immediate 'truncate table {table name}';

instead of "delete {table name}'

it's much more efficient.  But may not be allowed if you have foreign key constraints to the table
0
sdstuberCommented:
put it all in the execute immediate string  - note the ";" is outside the single quotes


execute immediate 'CREATE BITMAP INDEX [index name] ON [table name]
([column name])
LOGGING
TABLESPACE [tablespace]
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          2M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL';

0
sdstuberCommented:
or, remove the new lines and extra white space and put it all on one line
0
jameso99Author Commented:
Thanks!  That worked nicely.
Looks ugly in Toad but oh well ;)
0
sdstuberCommented:
glad I could help, don't forget to close the question!
0
jameso99Author Commented:
Don't worry - I'll assign points I'm just working things out.  I'm not one of those who conveniently leave the question open for 3 weeks and never use EE again.


I'm getting an error now, maybe you can help:

ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "truncate table channel_readings_rpt" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "truncate table channel_readings_rpt" to continue.

This is the statement:
execute immediate 'truncate table channel_readings_rpt';
0
jameso99Author Commented:
Nevermind, somehow I got that cleared up.  Weirdness.
0
jameso99Author Commented:
I did not realize I could do it this way - thank you both.  Thanks sdstuber for the complete follow-up.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.