Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Error in Procedure to Rebuild Index

Posted on 2013-11-25
5
659 Views
Last Modified: 2013-11-26
I was attempting to make a stored procedure to rebuild some indexes on a ginormous table I have to refresh monthly.

create or replace 
PROCEDURE                                                                                                       
SP_REBUILD_INDEXES AS
BEGIN
  --NPI INDEXES

  ALTER INDEX "VALIDATION_TOOLS"."PX_NPI" REBUILD;

  COMMIT;
END SP_REBUILD_INDEXES;

Open in new window


I get this error when I try to compile:
Error(6,3): PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:     ( begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    continue close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge pipe purge

Can I not put the rebuild in a procedure?
0
Comment
Question by:stopher2475
  • 2
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39674924
You cannot perform DDL natively in a procedure.

You need to use EXECUTE IMMEDIATE:

...
execute immediate 'ALTER INDEX "VALIDATION_TOOLS"."PX_NPI" REBUILD';
...
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39675448
I see that you named your procedure "SP_REBUILD_INDEXES".  That looks like a SQL Server-style name.  If you are familiar with SQL Server, but new to Oracle, be careful!  Yes, these are both "SQL compliant" databases.  But, beyond support for the four basic SQL verbs (select, insert, update and delete) and storing data in tables that contain rows and columns, there are a *LOT* more differences than similarities between SQL Server and Oracle.

Don't assume that the SQL Server way of getting things done is "normal" and Oracle's way is "odd".  They are just different.  Oracle is optimized for performance, stability and security.  SQL Server (to me at least) appears to be optimized for: ease-of-use and  compatibility with Micosoft programming tools.

Oracle stored procedures only support the four basic SQL verbs directly, because Oracle does "early binding" (at compile time) of stored procedure code to the corresponding DB objects, to give maximum run-time performance.

Also, be aware that for things like record-locking, read consistency and whether temporary tables need to be used or not, there are *MANY* differences between SQL Server and Oracle!
0
 
LVL 2

Author Comment

by:stopher2475
ID: 39676123
Thanks for the suggestions. It's compiling now. I'm going to test running it in the application tomorrow and post my results back.
@markgeer - You're correct, my background is more SQL server. Definately a learning curve for the Oracle way of doing things. =)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39676220
Then to add to Oracle training:
 ALTER INDEX "VALIDATION_TOOLS"."PX_NPI" REBUILD;

Double quotes are bad in Oracle.  It forces case sensitivity.  By default Oracle converts all object names to upper case.

So:
create table tAb1(cOl1 char(1));  --will run
select CoL1 from TAB1;
select col1 from tab1;
select COL1 from TaB1;
all work.

When you use double quotes around an object, it forces case sensitivity:
create table "tAb1"("cOl1" char(1));  --will run
select CoL1 from TAB1;
select col1 from tab1;
select COL1 from TaB1;
all fail.

Best to not use them anywhere.  Then you don't get caught.

Change the code to:
execute immediate 'ALTER INDEX VALIDATION_TOOLS.PX_NPI REBUILD';

Since it was already uppercase in the original code, I never bothered to mention it.
0
 
LVL 2

Author Closing Comment

by:stopher2475
ID: 39677643
Seems to be working now. Thanks for everyones help.
Good tip on the quotes signaling case sensitivity. Was not aware of that.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to connect SQL Server from my Oracle database? 11 131
form builder not starting 3 56
PL SQL Developer 7 54
oracle date format checking 7 26
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question