Solved

Convert PL/SQL to SQL

Posted on 2008-10-07
2
749 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Records that don't match 8 34
Query Peformance + mulitple query plans 9 48
su - oracle could not open session 6 52
SQL Statement to Update Email Domain 2 21
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

920 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