1 2 Previous Next 20 Replies Latest reply: Dec 14, 2012 1:53 AM by Purvesh K RSS

    Looping issue in stored procedure

    907442
      Hi Experts,

      currently i am using below version :

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

      Here i am describibng DDL and DML queries.

      CREATE TABLE test_1 (c1 NUMBER,c2 VARCHAR2(30),c3 VARCHAR2(20));

      INSERT INTO test_1 VALUES(10,'abcdefg','abcdefg');
      INSERT INTO test_1 VALUES(20,'Hi jklm','hij klmn');


      i want the ouput like below : (count of spaces,count of lower characters,count of upper characters for every column)

      Number of spaces : 0 column_name :c2
      Number of uppercase char : 0 column_name :c2
      Number of lowercase char :7 column_name :c2
      Number of spaces : 0 column_name :c3
      Number of uppercase char : 0 column_name :c3
      Number of lowercase char :7 column_name :c3
      Number of spaces : 1 column_name :c2
      Number of uppercase char : 1 column_name :c2
      Number of lowercase char :5 column_name :c2
      Number of spaces : 1 column_name :c3
      Number of uppercase char : 0 column_name :c2
      Number of lowercase char :7 column_name :c2


      For the above requirement i had written the below procedure. but i am getting error, Please help me on this issue. or else tell me other logic for this requirement.



      create or replace procedure string_count(intab IN VARCHAR2)
      AS
      v_str VARCHAR2(200);
      v_query VARCHAR2(200);
      l NUMBER;
      i NUMBER := 1;
      ss VARCHAR2(20);
      ascii NUMBER;
      upper_count NUMBER := 0;
      lower_count NUMBER := 0;
      space_count NUMBER := 0;
      BEGIN
      FOR i IN (SELECT COLUMN_NME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(intab) AND DATA_TYPE IN ('VARCHAR2'))
      LOOP
      BEGIN
      v_query := 'CURSOR c2 IS select '||i.column_name||' from '||intab;
      EXECUTE IMMEDIATE v_query INTO v_str;
      l := LENGTH(v_str);
      WHILE i <= l
      LOOP
      ss := substring(v_str,i,1);
      IF (ASCII(ss)) = ASCII(' ') THEN
      space_count := space_count+ 1;
      ELSE
      IF (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;
      CLOSE c2;
      DBMS_OUTPUT.PUT_LINE(space_count|| 'Number of spaces :'||v_str );
      DBMS_OUTPUT.PUT_LINE(upper_coun|| 'Number of uppercase char :'||v_str );
      DBMS_OUTPUT.PUT_LINE(lower_count||'Number of lowercase char :'||v_str);
      END LOOP;
      CLOSE c1;
      END string_count;



      ERROR is : Error(33,8): PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if



      Thanks in Advance.
        • 1. Re: Looping issue in stored procedure
          jeneesh
          Each "IF" should have a curresponding "ENDIF".

          Or use "ELSIF" instad of "ELSE IF"
          • 2. Re: Looping issue in stored procedure
            Suri
            Hi,

            First of all you didint define any CURSORS is declaration section and you are closing those undefined cursors in procedure body.

            Please check those.
            create or replace procedure string_count(intab IN VARCHAR2) AS
              v_str       VARCHAR2(200);
              v_query     VARCHAR2(200);
              l           NUMBER;
              i           NUMBER := 1;
              ss          VARCHAR2(20);
              ascii       NUMBER;
              upper_count NUMBER := 0;
              lower_count NUMBER := 0;
              space_count NUMBER := 0;
            BEGIN
              FOR i IN (SELECT COLUMN_NME
                          FROM USER_TAB_COLUMNS
                         WHERE TABLE_NAME = UPPER(intab)
                           AND DATA_TYPE IN ('VARCHAR2')) LOOP
                BEGIN
                  v_query := 'CURSOR c2 IS select ' || i.column_name || ' from ' ||
                             intab;
                  EXECUTE IMMEDIATE v_query
                    INTO v_str;
                  l := LENGTH(v_str);
                  WHILE i <= l LOOP
                    ss := substring(v_str, i, 1);
                    IF (ASCII(ss)) = ASCII(' ') THEN
                      space_count := space_count + 1;
                    ELSE
                      IF (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;
                  CLOSE c2;  --   You have not defined the cursor 
            
                  DBMS_OUTPUT.PUT_LINE(space_count || 'Number of spaces :' || v_str);
                  DBMS_OUTPUT.PUT_LINE(upper_coun || 'Number of uppercase char :' ||
                                       v_str);
                  DBMS_OUTPUT.PUT_LINE(lower_count || 'Number of lowercase char :' ||
                                       v_str);
                END LOOP;
                CLOSE c1;   -- You have not defined the cursor 
              END string_count;
            Edited by: Suri on Dec 12, 2012 2:08 PM
            • 3. Re: Looping issue in stored procedure
              Billy~Verreynne
              A CASE statement is often a better and more structured approach than a large IF-ELSEIF statement. I almost never resort to using ELSE-IF...
              • 4. Re: Looping issue in stored procedure
                AlbertoFaenza
                Hi,

                When you put some code or output please enclose it between two lines starting with {noformat}
                {noformat}
                
                i.e.:
                {noformat}
                {noformat}
                SELECT ...
                {noformat}
                {noformat}
                
                Regarding your problem the issue is quite clear from the error message:
                ERROR is : Error(33,8): PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if
                It means that you use END LOOP where you should have used END IF.
                Check again your code.
                
                Regards.
                Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: Looping issue in stored procedure
                  jeneesh
                  Billy  Verreynne  wrote:
                  A CASE statement is often a better and more structured approach than a large IF-ELSEIF statement. I almost never resort to using ELSE-IF...
                  I was jsut decoding the error message for the OP :)

                  That function does not seems to be for meeting some business requirements..
                  • 6. Re: Looping issue in stored procedure
                    Purvesh K
                    If I were to do such a Job, I would rather prefer a SQL than such long and abusive code.

                    I believe this statement will do the job for you and get almost the results you need:
                    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;
                    
                    COL_NAME COUNT_LOWER COUNT_UPPER COUNT_SPACE
                    -----------------------------------------------------
                    C2         2            1               1 
                    C2         1            0               0 
                    C3         2            0               1 
                    C3         1            0               0 
                    • 7. Re: Looping issue in stored procedure
                      907442
                      hi suri thanks for reply. in that procedure i didnot declare cursor c1 that's copy-paste mistake.

                      Here is my actual procedure.


                      create or replace procedure string_count(intab IN VARCHAR2)
                      AS
                      v_str VARCHAR2(200);
                      v_query VARCHAR2(200);
                      l NUMBER;
                      i NUMBER := 1;
                      ss VARCHAR2(20);
                      ascii NUMBER;
                      upper_count NUMBER := 0;
                      lower_count NUMBER := 0;
                      space_count NUMBER := 0;
                      BEGIN
                      FOR i IN (SELECT COLUMN_NME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(intab) AND DATA_TYPE IN ('VARCHAR2'))
                      LOOP
                      BEGIN
                      v_query := 'CURSOR c2 IS select '||i.column_name||' from '||intab;
                      EXECUTE IMMEDIATE v_query INTO v_str;
                      l := LENGTH(v_str);
                      WHILE i <= l
                      LOOP
                      ss := substring(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;
                      CLOSE c2;
                      DBMS_OUTPUT.PUT_LINE(space_count|| 'No.of spaces:'||v_str );
                      DBMS_OUTPUT.PUT_LINE(upper_coun|| 'No.of upper case char:'||v_str );
                      DBMS_OUTPUT.PUT_LINE(lower_count||'No.of lower case char:'||v_str);
                      END LOOP;
                      END string_count;
                      • 8. Re: Looping issue in stored procedure
                        907442
                        Hi purvesh many thanks for giving the Required query. But i need procedure for this requiremnt.

                        could you please provide complete procedure with this Logic.


                        Thanks,
                        • 9. Re: Looping issue in stored procedure
                          Purvesh K
                          904439 wrote:
                          Hi purvesh many thanks for giving the Required query. But i need procedure for this requiremnt.

                          could you please provide complete procedure with this Logic.


                          Thanks,
                          I will not provide you with the procedure. I provided you with the query.

                          It is simply a matter of few minutes to include the query into a procedure and since you did not specify, if you just with to display the data or store it for futher analysis etc, I cannot guide what you should be doing with the query.

                          Hence, if you just wish to read the data at your command prompt/IDE, then include the query as a Cursor and print it by looping though the data.
                          If you wish to store the data for further analysis, then
                          insert into your_target_table select column_list from my_query;
                          If you can post your best attempt, I will be delighted to help you. But you will have to provide your best effort and the place where you are stuck.
                          • 10. Re: Looping issue in stored procedure
                            907442
                            Hi purvesh K , i accepted your statements.


                            But i have one query. what it is, i executed your query on test Table it showing error result.


                            my table DDL & DML statements

                            CREATE TABLE test_1 (c1 NUMBER,c2 VARCHAR2(30),c3 VARCHAR2(20));

                            INSERT INTO test_1 VALUES(10,'abcdefg','abcdefg');
                            INSERT INTO test_1 VALUES(20,'Hi jklm','hij klmn');


                            i need the output like below.

                            Number of spaces : 0 column_name :c2
                            Number of uppercase char : 0 column_name :c2
                            Number of lowercase char :7 column_name :c2
                            Number of spaces : 0 column_name :c3
                            Number of uppercase char : 0 column_name :c3
                            Number of lowercase char :7 column_name :c3
                            Number of spaces : 1 column_name :c2
                            Number of uppercase char : 1 column_name :c2
                            Number of lowercase char :5 column_name :c2
                            Number of spaces : 1 column_name :c3
                            Number of uppercase char : 0 column_name :c2
                            Number of lowercase char :7 column_name :c2


                            But your query given below result :


                            COL_NAME COUNT_LOWER COUNT_UPPER COUNT_SPACE
                            ---------------------------------------------------- ---------------------- ---------------------- ----------------------
                            C2 2 0 1
                            C2 1 0 0
                            C3 2 0 1
                            C3 1 0 0



                            Check the above two results of values.. there is difference in lowercase character count and uppercase character count and spacecount.


                            please check it and let me know.


                            Thanks,
                            • 11. Re: Looping issue in stored procedure
                              vijayrsehgal-Oracle
                              From Purvesh's query,test this and play with it to derive the answer you seek.
                              select c2, 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,
                              c3,
                              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
                              • 12. Re: Looping issue in stored procedure
                                Purvesh K
                                Sorry, I did not pay much attention towards the outcome of Counts. Perhaps paid a bit too attention towards the Unpivot.

                                Use this sql as your Inner View and check if it fetches the desired result set.
                                select c2, 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,
                                                        c3,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
                                • 13. Re: Looping issue in stored procedure
                                  APC
                                  It's bad enough that what us to find the syntax errors in your shonky code. The least you could do by way of apology is format your code so it's readable.

                                  In future please top and tail your posted code with {noformat}
                                  {noformat} tags.  Note these are not Markup tags, so the closing {noformat}
                                  {noformat} tag does not have a backslash.

                                  Thus ...


                                  {noformat}
                                       select * from emp
                                      
                                  {noformat}

                                  ... renders as ...
                                       select * from emp
                                      
                                  Cheers, APC
                                  • 14. Re: Looping issue in stored procedure
                                    907442
                                    Hi Purvesh,

                                    Now your logic is fine.defiantly will be useful for my requirement. but i had written the procedure for this requirement with other logic it is not working.

                                    Here is the procedure : (at compile time no issue but at run time got error)

                                    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;
                                    
                                    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 := 'CURSOR c2 IS select '||k.column_name||' from '||intab ;
                                          EXECUTE IMMEDIATE v_query INTO v_str;
                                          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;
                                       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;
                                    Here is the Error Message :
                                    ERROR : Error starting at line 1 in command:
                                    EXECUTE string_count('BB');
                                    Error report:
                                    ORA-00900: invalid SQL statement
                                    ORA-06512: at "TESTUSER.STRING_COUNT", line 17
                                    ORA-06512: at line 1
                                    00900. 00000 - "invalid SQL statement"
                                    Please suggest me on this error and even i had tried to create a procedure with your logic. but i am blank to put your logic in the procedure.

                                    Here is the proceduere of ur logic(incomplete) :
                                    create or replace procedure string_count1 (intab IN VARCHAR2)
                                    AS
                                    lowercase_count NUMBER;
                                    uppercase_count NUMBER;
                                    space_count NUMBER;
                                    v_str VARCHAR2(200);
                                    v_query VARCHAR2(200);
                                    CURSOR c1 IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(intab) AND DATA_TYPE IN ('VARCHAR2');
                                    BEGIN
                                    OPEN c1 ;
                                    LOOP
                                    FETCH c1 INTO v_str;
                                    EXIT WHEN c1%NOTFOUND;
                                    v_query := 'SELECT regexp_count('||v_str||','|| '''[a-z]'''||',' ||'1'||','|| '''c'''||') FROM '||intab;
                                    EXECUTE IMMEDIATE v_query INTO lowercase_count;
                                    DBMS_OUTPUT.PUT_LINE(lowercase_count);
                                    --DBMS_OUTPUT.PUT_LINE(v_query);
                                    END LOOP;
                                    CLOSE c1;
                                    END string_count1;
                                    My ouput should be like :
                                    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);
                                    Thanks,
                                    1 2 Previous Next