stopher2475
asked on
Error in Procedure to Rebuild Index
I was attempting to make a stored procedure to rebuild some indexes on a ginormous table I have to refresh monthly.
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?
create or replace
PROCEDURE
SP_REBUILD_INDEXES AS
BEGIN
--NPI INDEXES
ALTER INDEX "VALIDATION_TOOLS"."PX_NPI" REBUILD;
COMMIT;
END SP_REBUILD_INDEXES;
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. =)
@markgeer - You're correct, my background is more SQL server. Definately a learning curve for the Oracle way of doing things. =)
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.
ALTER INDEX "VALIDATION_TOOLS"."PX_NPI
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.
ASKER
Seems to be working now. Thanks for everyones help.
Good tip on the quotes signaling case sensitivity. Was not aware of that.
Good tip on the quotes signaling case sensitivity. Was not aware of that.
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!