Build SQL in the Cursor

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Re: Build SQL in the Cursor

Postby Tim... » Fri Jan 20, 2012 7:39 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Fri Jan 20, 2012 8:59 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Fri Jan 20, 2012 9:23 pm

OK

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Mon Jan 23, 2012 5:31 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Thu Feb 02, 2012 3:53 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Thu Feb 02, 2012 4:17 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Thu Feb 02, 2012 5:13 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Thu Feb 02, 2012 5:47 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Thu Feb 02, 2012 6:25 pm

DBA_PART_INDEXES or ALL_PART_INDEXES amount to pretty much the same thing in this context.
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Fri Feb 03, 2012 4:20 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Build SQL in the Cursor

Postby Tim... » Fri Feb 10, 2012 4:28 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest