How to delete the indexes of a table using routine

[Madhu1981]

Hi

I have to delete the indexes of a table using routines. Can anyone help me... please

[narasimha]

Write a script which will drop indices.
Execute this script in your routine!

[DSguru2B]

Or you can do it in the OPEN command of a database stage. And how exactly are you going to do it in a routine? Are you planning do use a Basic Transformer?

[chulett]

I have to delete the indexes of a table using routines.

No, you don't. You may need to drop them but why do you believe that you 'need' to do this in a routine?

As noted, the OPEN command is a more appropriate place for commands of that nature. Another solution, seeing as how most users would not have the privledges needed for this, would be the creation of procedure under a user that does and then grant your user the ability to execute it.

You'll need to rebuild them once you are done.

[ray.wurlod]

Craig beat me to it.

Always challenge unreasonable and preposterous requirements.

Surely if you can drop indexes without a routine, that is more efficient? How good are your C++ skills? What resources do "they" have for maintaining routines?

[Madhu1981]

Or you can do it in the OPEN command of a database stage. And how exactly are you going to do it in a routine? Are you planning do use a Basic Transformer?

Hi Guru...

could you explain me in detail. I haven't used this OPEN command in database stage. Can you tell me the syntax of this open command also.

Thanks in advance

[DSguru2B]

Its in the database stage. Syntax will be the same as you would use in your favourite sql tool. OPEN command will run only once when the connection to the database opens, before the main sql. You just need to drop index <<index_name>> on <<table_name>> . Search in google for the exact syntax for your database.

[ray.wurlod]

The syntax for OPEN and CLOSE command is "any valid SQL".

"Valid" includes your having appropriate database/table privileges to execute the said command.

[kumar_s]

What is the stage you are using to load the data.
Oracle Enterprise stage has "Index mode" as a option, where you can select "Rebuild". Where these drop and recreate will be taken care by Datastage.