1 2 Previous Next 20 Replies Latest reply: Dec 14, 2012 1:53 AM by Purvesh K Go to original post RSS
      • 15. Re: Looping issue in stored procedure
        Purvesh K
        I take it as instruction to OP as my solution has formatted code. :D
        • 16. Re: Looping issue in stored procedure
          Purvesh K
          Modify it to below
          create or replace procedure string_count (intab IN VARCHAR2)
          AS
              v_str VARCHAR2(200);
              v_query VARCHAR2(200);
              a NUMBER;
              i NUMBER := 1;
              ss VARCHAR2(200);
              upper_count NUMBER := 0;
              lower_count NUMBER := 0;
              space_count NUMBER := 0;
          type arr is table of varchar2(256);
          col_arr arr;
           
          BEGIN
              FOR k IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(intab) AND DATA_TYPE IN ('VARCHAR2'))
              LOOP
          BEGIN
                v_query := 'select '||k.column_name||' from '||intab ;         --> Removed CURSOR IS as it is not required.
                execute immediate v_query bulk collect into col_arr;       --Fetch Entire data for Column into an Array
          for r in 1..col_arr.count loop  --> Process the Array
              v_str := col_arr(r);
                a := LENGTH(v_str);
                  WHILE i <= a
                  LOOP
                     ss := substr(v_str,i,1);
                   IF ASCII(ss) = ASCII(' ') THEN
                     space_count := space_count + 1;
                   ELSIF ASCII(ss) = ASCII(UPPER(ss)) THEN
                     upper_count := upper_count + 1;
                   ELSE
                     lower_count := lower_count + 1;
                     i := i + 1;
                   END IF;
                  END LOOP;
          end loop; --> For Loop Ends
          END;
             DBMS_OUTPUT.PUT_LINE('No.of spaces:'||space_count||' ' ||v_str ); 
             DBMS_OUTPUT.PUT_LINE('No.of upper case char:'||upper_count||' '||v_str );
             DBMS_OUTPUT.PUT_LINE('No.of lower case char:'||lower_count||' '||v_str);
              END LOOP;
          end string_count;
          Although I have tried to help you in running the subprogram that you created, I still do not agree towards usage of Execute Immediate, specifically when an alternative is available.

          Here is a way of doing it without using Execute Immediate:
          create or replace procedure print_case_counts
          as
              
          cursor c_cur is
              select substr(cnt, instr(cnt, '_', 1, 2) + 1, instr(cnt, '_', 1, 3) - instr(cnt, '_', 1, 2) - 1) col_name, count_lower, count_upper, count_space
            from (
                   select *
                     from (
                           select regexp_count(c2, '[a-z]', 1, 'c') count_lowercase_c2, regexp_count(c2, '[A-Z]', 1, 'c') count_uppercase_c2, regexp_count(c2, '[[:space:]]', 1, 'c') count_space_c2,
                                  regexp_count(c3, '[a-z]', 1, 'c') count_lowercase_c3, regexp_count(c3, '[A-Z]', 1, 'c') count_uppercase_c3, regexp_count(c3, '[[:space:]]', 1, 'c') count_space_c3
                             from test_1
                         )
                   unpivot ((count_lower, count_upper, count_space) for cnt in ((count_lowercase_c2, count_uppercase_c2, count_space_c2), (count_lowercase_c3, count_uppercase_c3, count_space_c3)))
                )
           order by col_name;
          begin
              for i in c_cur loop
              dbms_output.put_line('Column : ' || i.col_name ||'; Lower Case count : ' || i.count_lower);
              dbms_output.put_line('Column : ' || i.col_name ||'; Upper Case count : ' || i.count_upper);
              dbms_output.put_line('Column : ' || i.col_name ||'; Space count : ' || i.count_space);
              end loop;
              
          end print_case_counts;
          
          begin
              print_case_counts;
          end;
          Column : C2; Lower Case count : 5
          Column : C2; Upper Case count : 1
          Column : C2; Space count : 1
          Column : C2; Lower Case count : 7
          Column : C2; Upper Case count : 0
          Column : C2; Space count : 0
          Column : C3; Lower Case count : 7
          Column : C3; Upper Case count : 0
          Column : C3; Space count : 1
          Column : C3; Lower Case count : 7
          Column : C3; Upper Case count : 0
          Column : C3; Space count : 0
          
          
          Statement processed. 0.02 seconds
          --------------------------------------------------------------------------------
          • 17. Re: Looping issue in stored procedure
            907442
            Hi puvesh,

            Your code is so helpful. But in which code you used regular expression in that you statically passed the tablename (test_1).

            But my intenssion is to passs the tablename dynamically. please guide me, how to pass the table name dynamically in below code.
            create or replace procedure print_case_counts(intab IN VARCHAR2)
            as
                
            cursor c_cur is
                select substr(cnt, instr(cnt, '_', 1, 2) + 1, instr(cnt, '_', 1, 3) - instr(cnt, '_', 1, 2) - 1) col_name, count_lower, count_upper, count_space
              from (
                     select *
                       from (
                             select regexp_count(c2, '[a-z]', 1, 'c') count_lowercase_c2, regexp_count(c2, '[A-Z]', 1, 'c') count_uppercase_c2, regexp_count(c2, '[[:space:]]', 1, 'c') count_space_c2,
                                    regexp_count(c3, '[a-z]', 1, 'c') count_lowercase_c3, regexp_count(c3, '[A-Z]', 1, 'c') count_uppercase_c3, regexp_count(c3, '[[:space:]]', 1, 'c') count_space_c3
                               from intab
                           )
                     unpivot ((count_lower, count_upper, count_space) for cnt in ((count_lowercase_c2, count_uppercase_c2, count_space_c2), (count_lowercase_c3, count_uppercase_c3, count_space_c3)))
                  )
             order by col_name;
            begin
                for i in c_cur loop
                dbms_output.put_line('Column : ' || i.col_name ||'; Lower Case count : ' || i.count_lower);
                dbms_output.put_line('Column : ' || i.col_name ||'; Upper Case count : ' || i.count_upper);
                dbms_output.put_line('Column : ' || i.col_name ||'; Space count : ' || i.count_space);
                end loop;
                
            end print_case_counts;
            and one more thing first logic ouput is not correct. but regular expression procedure is giving correct result. but i want to pass the table name dynamically.


            THANKS,
            • 18. Re: Looping issue in stored procedure
              Purvesh K
              >
              But my intenssion is to passs the tablename dynamically. please guide me, how to pass the table name dynamically in below code.
              So, you will have to input the Table name and the Column Name dynamically. Similar to the way you did it in your previous attempt.

              something like below:(just an idea not exact code)
              declare
              v_query  varchar2(4000);
              begin
                for cols in (select column_name from user_tab_columns where table_name = p_table_name) loop  -->p_table_name is Input parameter;
                  v_query := 
                  ' select regexp_count(' || cols.column_name || ', ''[a-z]'', 1, ''c'') count_lowercase_c2, regexp_count(' || cols.column_name || ', ''[A-Z]'', 1, ''c'') count_uppercase_c2, regexp_count(' || cols.column_name || ', ''[[:space:]]'', 1, ''c'') count_space_c2,
                    from ' || p_table_name;
                  execute immediate v_query into v_count_lower, v_count_upper, v_count_space;
                  --print the variables according to need.
                end loop;
              end;
              Edited by: Purvesh K on Dec 13, 2012 5:03 PM
              --Corrected Dynamic Query.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
              • 19. Re: Looping issue in stored procedure
                907442
                Hi purvesh,

                please check the below code. compilation is fine..
                create or replace
                procedure string_count (p_table_name IN VARCHAR2)
                As
                   v_query  varchar2(4000);
                   v_count_lower NUMBER;
                   v_count_upper NUMBER;
                   v_count_space NUMBER;
                begin
                  for cols in (select column_name from user_tab_columns where table_name = p_table_name AND DATA_TYPE IN ('VARCHAR2')) 
                    loop  
                       v_query := 'select regexp_count(' || cols.column_name || ','||'''[a-z]'''||','||'1'||','||'''c'''||')'||','
                                       ||'regexp_count(' || cols.column_name || ','||'''[A-Z]'''||','||'1'||','||'''c'''||')'||',' 
                                       ||'regexp_count(' || cols.column_name ||','||'''[[:space:]]'''||','||'1'||','||'''c'''||')'||
                                       ' from ' || p_table_name;  
                      execute immediate v_query into v_count_lower, v_count_upper, v_count_space ;
                  DBMS_OUTPUT.PUT_LINE(v_count_lower);
                  DBMS_OUTPUT.PUT_LINE(v_count_upper);
                  DBMS_OUTPUT.PUT_LINE(v_count_space);        
                   
                     end loop;
                end string_count;
                ERROR Message :
                ORA-01422: exact fetch returns more than requested number of rows
                ORA-06512: at "TESTUSER.OOO", line 14
                ORA-06512: at line 1
                01422. 00000 -  "exact fetch returns more than requested number of rows"
                *Cause:    The number specified in exact fetch is less than the rows returned.
                *Action:   Rewrite the query or change number of rows requested
                Please help on this error.


                Thanks,
                • 20. Re: Looping issue in stored procedure
                  Purvesh K
                  904439 wrote:
                  Hi purvesh,

                  please check the below code. compilation is fine..
                  ERROR Message :
                  ORA-01422: exact fetch returns more than requested number of rows
                  ORA-06512: at "TESTUSER.OOO", line 14
                  ORA-06512: at line 1
                  01422. 00000 -  "exact fetch returns more than requested number of rows"
                  *Cause:    The number specified in exact fetch is less than the rows returned.
                  *Action:   Rewrite the query or change number of rows requested
                  I already mentioned that I had posted the Idea and not exact code.

                  Since the Select query does not have any filteration criteria, it fetches all the records. And you are using Scalar datatypes to fetch multiple records, which causes the error.

                  This should resolve all your queries:
                  create or replace
                  procedure print_string_count (p_table_name IN VARCHAR2)
                  As
                     v_query  varchar2(4000);
                     v_count_lower NUMBER;
                     v_count_upper NUMBER;
                     v_count_space NUMBER;
                  
                  type coll is table of PLS_INTEGER index by PLS_INTEGER;
                  c_count_lower coll;
                  c_count_upper coll;
                  c_count_space coll;
                  
                  c_ref_cur sys_refcursor;
                  
                  begin
                    for cols in (select column_name from user_tab_columns where table_name = p_table_name AND DATA_TYPE IN ('VARCHAR2')) 
                      loop  
                         v_query := 'select regexp_count(' || cols.column_name || ','||'''[a-z]'''||','||'1'||','||'''c'''||')'||','
                                         ||'regexp_count(' || cols.column_name || ','||'''[A-Z]'''||','||'1'||','||'''c'''||')'||',' 
                                         ||'regexp_count(' || cols.column_name ||','||'''[[:space:]]'''||','||'1'||','||'''c'''||')'||
                                         ' from ' || p_table_name;  
                        --execute immediate v_query bulk collect into c_count_lower, c_count_upper, c_count_space ;
                        open c_ref_cur for v_query;
                        loop
                            fetch c_ref_cur bulk collect into c_count_lower, c_count_upper, c_count_space limit 20;
                            exit when c_count_lower.count = 0;
                            for i in c_count_lower.first..c_count_lower.last loop
                                DBMS_OUTPUT.PUT_LINE(c_count_lower(i));
                                DBMS_OUTPUT.PUT_LINE(c_count_upper(i));
                                DBMS_OUTPUT.PUT_LINE(c_count_space(i));
                            end loop;
                        end loop;
                     
                       end loop;
                  end print_string_count;
                  1 2 Previous Next