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?
 
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.