ORACLE SQL CURSOR / FOR LOOP

Question

I need to verify converted data, distinct values and records counts. I would like to write statements so that I can enter a table name, then retrieve it's columns and use them in a query to get its distinct values (the actual values, not just a count of how many distinct) and their count.

I think I need to a CURSOR or CURSOR FOR LOOP and create something like this:

declare 
    cursor field_name
is 
  select COLUMN_NAME
     from user_tab_cols
     where table_name='TABLE1'
c_field_name    field_name%ROWTYPE;


BEGIN
    OPEN field_name
    loop 
        fetch field_name INTO c_field_name;
        exit when field_name%NOTFOUND;
    end loop;
    CLOSE field_name;
end;

Then run a query using that above in something like

select field_name, count(*)
from table1
group by field_name

Do I need to create 2 loop statements? I've not yet created one and can't quite get the context to get my results so far.

Is there something preventing you from getting the distinct values and counts in 2 queries then joining them together on a key field? I would try with dynamic SQL before I went to doing multiple loops. — Jacob H
– Jacob H, Commented Sep 26, 2017 at 14:16
If you can edit to show some sample output that you might expect just for one table that may better illustrate what it is you want to achieve. — BriteSponge
– BriteSponge, Commented Sep 26, 2017 at 14:56

Cyrille MODIANO · Accepted Answer · 2017-09-26 14:38:12Z

1

BEGIN
    FOR myrow in (select field_name, count(*) as "count" from table1 group by field_name)
    loop 
        dbms_output.put_line(myrow.field_name);
        dbms_output.put_line(myrow.count);
    end loop;
end;

answered Sep 26, 2017 at 14:38

Cyrille MODIANO

2,4163 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pawan Rawat · Accepted Answer · 2017-09-27 07:24:46Z

Considering you will be giving the table name as parameter below code will print all the values of all the columns one by one along with the count of the values

create or replace PROCEDURE PR_PREP(
P_TABLE_NAME IN VARCHAR2)
IS
  CURSOR CUR_COLUMNS (PA_TABLE_NAME VARCHAR2)
  IS
    SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = PA_TABLE_NAME;
  COL_NAMES CUR_COLUMNS%ROWTYPE;
TYPE TYP_RECORD
 IS
 RECORD
 (
   FIELD_NAME VARCHAR2(255),
  CNT        INT);
TYPE TYP_OP_TABLE
IS
 TABLE OF TYP_RECORD;
  OP_TABLE TYP_OP_TABLE;
 I     INT;
 V_SQL VARCHAR2(2000);
BEGIN
  FOR COL_NAMES IN CUR_COLUMNS(P_TABLE_NAME)
   LOOP
     V_SQL := 'SELECT ' || COL_NAMES.COLUMN_NAME || ' AS FIELD_NAME , 
 COUNT(*) AS CNT FROM ' || 
         P_TABLE_NAME || ' GROUP BY ' || COL_NAMES.COLUMN_NAME ;
  --    DBMS_OUTPUT.PUT_LINE (V_SQL);
     EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO OP_TABLE;
  dbms_output.put_line('columna name = ' ||COL_NAMES.COLUMN_NAME);
     FOR I IN OP_TABLE.FIRST .. OP_TABLE.LAST
     LOOP
       DBMS_OUTPUT.PUT_LINE('FIELD VALUE '||OP_TABLE(I).FIELD_NAME || ' COUNT = ' || OP_TABLE(I).CNT);
     END LOOP;
      DBMS_OUTPUT.PUT_LINE('ONE FILED ENDED , NEXT STARTED');
    END LOOP;
 END;

Collectives™ on Stack Overflow

ORACLE SQL CURSOR / FOR LOOP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related