OraFAQ Forum: SQL & PL/SQL » Simple Stored Procedure help...

Home » SQL & PL/SQL » SQL & PL/SQL » Simple Stored Procedure help... (11g Enterprise Edition Release 11.2.0.2.0 - 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Simple Stored Procedure help... [message #613276]

Fri, 02 May 2014 08:10

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Hi,

I am attempting to create a procedure which will drop indexes, run some SQL, and then rebuild indexes. However, I am keeping it simple and short with this example.

Can someone please tell me what I am doing wrong here? When I compile it, I get an error starting from the DROP.

Thank you,
Joe


create or replace PROCEDURE JOES_SAMPLE iS 
BEGIN
        DROP INDEX I_LH_INTRANET_PAYMENTS_BA ; 
        DROP INDEX I_LH_INTRANET_PAYMENTS_PD ; 
        DROP INDEX I_LH_INTRANET_PAYMENTS_PN ; 
        DROP INDEX I_LH_INTRANET_PAYMENTS_LVN ;
        DROP INDEX I_LH_INTRANET_PAYMENTS_REM ;
 

END JOES_SAMPLE;

Report message to a moderator

Re: Simple Stored Procedure help... [message #613277 is a reply to message #613276]

Fri, 02 May 2014 08:11

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

DDL needs to be wrapped in execute immediate.

Report message to a moderator

Re: Simple Stored Procedure help... [message #613278 is a reply to message #613276]

Fri, 02 May 2014 08:13

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

DROP is a DDL. You can't use DDL inside a procedure. You need to use EXECUTE IMMEDIATE for that.

EXECUTE IMMEDIATE 'DROP INDEX I_LH_INTRANET_PAYMENTS_BA';

Suggest you to search more about it and come back with your feedback.

[Updated on: Fri, 02 May 2014 08:15]

Report message to a moderator

Re: Simple Stored Procedure help... [message #613279 is a reply to message #613277]

Fri, 02 May 2014 08:14

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Thanks, Roachcoach.

Do you mean like this?

EXECUTE IMMEDIATE 'DROP INDEX I_LH_INTRANET_PAYMENTS_BA';
EXECUTE IMMEDIATE 'DROP INDEX I_LH_INTRANET_PAYMENTS_PD';

Report message to a moderator

Re: Simple Stored Procedure help... [message #613280 is a reply to message #613279]

Fri, 02 May 2014 08:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

rappaj wrote on Fri, 02 May 2014 06:14

Thanks, Roachcoach.

Do you mean like this?

EXECUTE IMMEDIATE 'DROP INDEX I_LH_INTRANET_PAYMENTS_BA';
EXECUTE IMMEDIATE 'DROP INDEX I_LH_INTRANET_PAYMENTS_PD';

I suggest rather than DROP you merely DISABLE the index.

Report message to a moderator

Re: Simple Stored Procedure help... [message #613281 is a reply to message #613280]

Fri, 02 May 2014 08:19

rappaj
Messages: 147
Registered: June 2010
Location: NYC

Senior Member

Ok, thank you all for your suggestions.

Joe

Report message to a moderator

Re: Simple Stored Procedure help... [message #613282 is a reply to message #613280]

Fri, 02 May 2014 08:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

BlackSwan wrote on Fri, 02 May 2014 18:47

I suggest rather than DROP you merely DISABLE the index.

Disable a regular index, are you sure? We don't know whether those indexes are function-based or not.
Or perhaps you meant DISABLE/UNUSABLE depending on the type of index Smile

[Updated on: Fri, 02 May 2014 08:32]

Report message to a moderator

Previous Topic:	Why we cannot use procedure in select statement?
Next Topic:	Creating Table in plsql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:34:34 CDT 2024