Build SQL in the Cursor

[Tim...]

Hi.

Just like any other single-row SQL statement in PL/SQL you need an INTO clause. Define a variable to hold the string, then add "INTO variable-name" after the select list and before the from clause.

You will then need to output the value in the variable using the DBMS_OUTPUT.PUT_LINE procedure call.

To display the output produced by DBMS_OUTPUT your script needs to call SET SERVEROUTPUT ON.

The string you are currently generating is missing some spaces. When you concatenated things together you have to remember to include the spaces also, otherwise you will end up with a jumbled mess.

Cheers

Tim...

[Tim...]

Hi.

Any single-row query that could result in no rows returned must be surrounded by an exception block.

Code: Select all

BEGIN
  -- Your query goes here.
EXCEPTION
  WHEN NO_DATA_ FOUND THEN
    NULL;
END;

This traps the errors when no data is returned.

As for your code, using SELECT ... INTO is only meant for a single row returned. If a table has multiple indexes your code could fail I guess. If this could be the case you would have to deal with the indexes with a loop also.

Check your syntax to the move of the index. You have to use rebuild, not move. This article shows examples of the syntax.

http://www.oracle-base.com/articles/mis ... ganization

Cheers

Tim...

[Tim...]

OK

Cheers

Tim...

[Tim...]

Hi.

When you post code you should always surround it with the CODE tags and it's a good idea to indent your code properly as it makes it easier to read. I've corrected this for you.

As for the parameter issue, try looking at this:

http://www.oracle-base.com/articles/mis ... ipting.php

You have one of several choices:

1) Build up the PL/SQL in a temp file and include the parameters from your script into them, then run this script using SQL*Plus.
2) Do the same, but using an incline file using the "<<EOF ... EOF" method.
3) Put your script into a separate file and use placeholders for the parameter. Call the script passing the relevant values for those placeholders.

http://www.oracle-base.com/articles/mis ... _variables

SQL*Plus and PL/SQL does not undertsand shell script variables, so you must pass them in a way it does understand.

Cheers

Tim...

[Tim...]

Hi.

Dude! I asked you before, please indent your code properly and use the CODE tags around it to make us display in the correct format. Sending a load of unformatted code is really hard for people to read. I've corrected your post.

Now. What are you "struggling" with? You don't actually mention what the problem is...

Cheers

Tim...

[Tim...]

Hi.

There is only one loop, so I think you are confused when you talk about first and second loop...

Perhaps you are talking about the issue I mentioned previously, when I said your code would fail if the table has multiple index partitions. If this is the case, you need to include another loop with the first. Your starting point should probably be something like this.

Code: Select all

declare
  incr  varchar2(200);
  incr1 varchar2(200);
  pat   varchar2(200);
  tab   varchar2(200);
  ts_name varchar2(30) := '?';
begin
  for part_rec in (select part_name,tab_name from temp_table) loop
    begin
      select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' tablespace ' || ts_name ||';'
      into   incr
      from   dba_tab_partitions
      where  table_owner    = part_rec.table_owner
      and    partition_name = part_rec.part_name;

      DBMS_OUTPUT.PUT_LINE (incr);

      FOR ind_rec IN (select 'alter index '||owner||'.'||index_name||' rebuild partition '||partition_name|| ' tablespace ' || ts_name ||';'
                      from dba_ind_partitions where table_name=part_rec.tab_name)
      LOOP
        DBMS_OUTPUT.PUT_LINE (ind_rec);
      END LOOP;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;
  end loop;
end;
/
spool off;

Notice, I have removed the reference to the TEMP tablespace, because you will never actually be rebuildiing anything to that tablespace. Put the correct tbalespace name into the variable at the top. Notice also I've removed your explicit cursor and replace it with an implicit one in the FOR LOOP definition. Implicit cursors are more efficient and require less error handling code. You should always use them. Finally, for each partition, the code loops through 0-many indexes that are relevant to that partition.

Like I said, this is the first cut. I don't even know if it will compile because I do not have your schema to test it against.

Cheers

Tim...

[Tim...]

Hi.

Funny, you say, "Thats entire issue", but you've never actually mentioned it!

Then use,

Code: Select all

from dba_ind_partitions where partition_name=part_rec.partition_name)

You should always include owner as well, since it is possible to have multiple schema in a database each containing partitions of the same name.

Cheers

Tim...

[Tim...]

Which bit of, "Dude! I asked you before, please indent your code properly and use the CODE tags around it to make us display in the correct format. Sending a load of unformatted code is really hard for people to read.", do you not understand? It gets pretty difficult to be bothered to answer questions when you are being ignored!

I'll make you a deal, you put in 2 seconds effort to actually post stuff properly and I'll try to answer. Likewise, if you can't be bothered to post things properly, I won't bother to answer your questions.

Join to ALL_PART_INDEXES and use the LOCALITY column.

Cheers

Tim...

[Tim...]

DBA_PART_INDEXES or ALL_PART_INDEXES amount to pretty much the same thing in this context.

[Tim...]

Hi.

No. Once again you have ignored my request to post formatted code using the CODE tag so it remains formated when displayed on the forum. If you can't be bothered to take the time to do this, when I have asked you 3 times already, I don't see why I should waste any of my time trying to help you.

Regards

Tim...

[Tim...]

Hi.

Not going to consider reading your messy posts anymore. I've asked you 4 times now and you still refuse to format your code and use the code tags around it. Not going to waste my time if you can't be bothered to make your posts readable.

Format your posts properly and I'll consider reading them. Send me messy posts like these again and I'm simply going to ignore them.

Tim...