Anyone have any experience using ex

[brownjd]

Anyone have any experience using execute immediate? I'm trying to use a procedure to rebuild indexes. Problem is the indexes are SAP flavor with the ~ symbol in them which is giving oracle fits. I've been able to hide the symbol in a normal sqlplus prompt with " ( double quotes ) around the index name, however, execute immediate is not recognizing the index name and not working. The following is what I'm using to no avail.

Execute immediate analyze index ' || owner ||
'."' ||
segment_name ||
'" validate structure'
with owner and segment_name coming from dba_segments.

Thanks in advance

 

[rcurva]

I don't know if this one can help but, I think you miss a single quote (at the beginning of analyze) in your statement.


Execute immediate 'analyze index ' ||
owner ||
'."' ||
segment_name ||
'" validate structure'
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[brownjd]

Thanks...Actually, I granted analyze any to the user executing the procedure and that cured it. Even tho the user had DBA privileges the grant was necessary. Thanks for your reply.