[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Convert PL/SQL to SQL

Posted on 2008-10-07
2
Medium Priority
?
762 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 500 total points
ID: 22659180

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
ID: 31503847
GGuzdziol,

That is perfect!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

591 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