Solved

Convert PL/SQL to SQL

Posted on 2008-10-07
2
744 Views
Last Modified: 2013-12-07
Hello,

I need to convert a PL/SQL block to standard ANSI SQL (attached).  What can you recommend?


Thanks,

Michael

DECLARE

   CURSOR c IS SELECT count(1) amount, pnd_index_owner OWNER FROM ctxsys.ctx_pending GROUP BY pnd_index_owner;

   pending_rec c%ROWTYPE;

BEGIN

   OPEN c;

   LOOP

      FETCH c INTO pending_rec;

         EXIT WHEN c%NOTFOUND;

         DBMS_OUTPUT.PUT_LINE(rpad(pending_rec.owner, 15,'.') || pending_rec.AMOUNT);

   END LOOP;

END;

Open in new window

ots.txt
0
Comment
Question by:michael4606
2 Comments
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 125 total points
Comment Utility

SELECT rpad(pnd_index_owner, 15, '.') || to_char(count(*))

  FROM ctxsys.ctx_pending

  GROUP BY pnd_index_owner

Open in new window

0
 

Author Closing Comment

by:michael4606
Comment Utility
GGuzdziol,

That is perfect!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now