Home » SQL & PL/SQL » SQL & PL/SQL » Can't Create Index With An If Statement
icon10.gif  Can't Create Index With An If Statement [message #360155] Wed, 19 November 2008 19:16 Go to next message
jbradenbaugh
Messages: 3
Registered: November 2008
Junior Member
Hi:

I'd like to run code to create an index on a given table, but only if the index does not exist. Here's what I've got so far....

DECLARE
c_Count NUMBER;
BEGIN
SELECT COUNT(*) INTO c_Count
FROM DBA_INDEXES
WHERE TABLE_NAME = 'SOME_TABLE_NAME'
AND INDEX_NAME = 'SOME_INDEX_NAME'
AND OWNER = 'SOME_OWNER'
AND TABLE_OWNER = 'SOME_OWNER';
IF c_Count = 0 THEN
CREATE INDEX SOME_OWNER.SOME_INDEX ON SOME_OWNER.SOME_TABLE (SOME_KEY);
END IF;

END;

The SELECT INTO statement runs fine alone.
The trouble comes with the IF statement. I receive the following error message:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin case declare exit for goto if loop...

This is the typical error message saying that the syntax is incorrect, and that the parser was expecting something else after the IF-THEN statement. Any ideas as to what may be wrong? I've attached a screen cap of the results. Thanks in advance.

Jerry
  • Attachment: Image2.jpg
    (Size: 71.03KB, Downloaded 67 times)
Re: Can't Create Index With An If Statement [message #360156 is a reply to message #360155] Wed, 19 November 2008 19:30 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
DECLARE
c_Count NUMBER;
BEGIN
SELECT COUNT(*) INTO c_Count
FROM DBA_INDEXES
WHERE TABLE_NAME = 'SOME_TABLE_NAME'
AND INDEX_NAME = 'SOME_INDEX_NAME'
AND OWNER = 'SOME_OWNER'
AND TABLE_OWNER = 'SOME_OWNER';
IF c_Count = 0 THEN
execute immediate 'CREATE INDEX SOME_OWNER.SOME_INDEX ON SOME_OWNER.SOME_TABLE (SOME_KEY)';
END IF;
END;
Re: Can't Create Index With An If Statement [message #360157 is a reply to message #360155] Wed, 19 November 2008 19:32 Go to previous messageGo to next message
jbradenbaugh
Messages: 3
Registered: November 2008
Junior Member
Perfect. Yeah I just learned I can't execute DDL w/o dynamic SQL. Thanks!
Re: Can't Create Index With An If Statement [message #360166 is a reply to message #360157] Wed, 19 November 2008 23:09 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As this is a question asked every month, you should also learn to search and maybe read PL/SQL User's Guide and Reference and Application Developer's Guide - Fundamentals.

Regards
Michel
Previous Topic: DateTime Query Problem
Next Topic: How to load a .raw file using a procedure??
Goto Forum:
  


Current Time: Fri Dec 02 12:12:18 CST 2016

Total time taken to generate the page: 0.13027 seconds