1 2 Previous Next 24 Replies Latest reply: Sep 10, 2013 6:55 AM by aee0ea55-7837-4049-a089-793d2a978474 RSS

    Select clause inside a Procedure with cursor

    aee0ea55-7837-4049-a089-793d2a978474

      Good Moring guys,

       

      I run into a problem that is giving me serious troubles.... I have a Select clause:

       

                    SELECT count(*)

                   FROM TD004_ENT_ORGAO_UO TD004

                   WHERE TD004.CODG_ENTIDADE = 1121742

                   AND TD004.NUMR_ANO_EXERCICIO = 2011

                   AND TD004.CODG_ORGAO = 02

                   AND TD004.CODG_UO = 001

       

      My table is empty and this query returns a count = 0, so far so good but, when I'm using this same query inside a Procedure that implements a cursor, I'm implementing a way of controling when to or not insert the records of this cursor in my table:

       

      OPEN cDIMENSAO;

       

      LOOP

             FETCH cDIMENSAO INTO EXERCICIO, CODG_ORGAO, NOME_ORGAO, CODG_UO, NOME_UO, CODG_ENTIDADE, SK_ENTIDADE;

             EXIT WHEN cDIMENSAO%NOTFOUND;

             begin

                   v_count := 0;

                   SELECT count(*) into V_COUNT

                   FROM TD004_ENT_ORGAO_UO TD004

                   WHERE TD004.CODG_ENTIDADE = CODG_ENTIDADE

                   AND TD004.NUMR_ANO_EXERCICIO = EXERCICIO

                   AND TD004.CODG_ORGAO = CODG_ORGAO

                   AND TD004.CODG_UO = CODG_UO;

             

                   IF V_COUNT = 0 THEN

                      INSERT INTO APLIC.TD004_ENT_ORGAO_UO(SK_ENT_ORGAO_UO, NUMR_ANO_EXERCICIO, CODG_ORGAO, NOME_ORGAO, CODG_UO, NOME_UO, CODG_ENTIDADE, SK_ENTIDADE, DATA_CARGA)

                      VALUES (APLIC.SK004_ENT_ORGAO_UO.NEXTVAL, EXERCICIO, CODG_ORGAO, NOME_ORGAO, CODG_UO, NOME_UO,CODG_ENTIDADE,SK_ENTIDADE,v_data_carga);

                      COMMIT;

                   END IF;

       

      The problem occurs here, my v_count controler just returns 1 even if the record isnt inside my table, is like the Where clasue is being ignored. What can be done ??

       

      I'm using the Oracle XE 11g.

       

      Thnks all.

        • 1. Re: Select clause inside a Procedure with cursor
          34MCA2K2

          There is a difference betweeen

           

          SELECT count(*)

                       FROM TD004_ENT_ORGAO_UO TD004

                       WHERE TD004.CODG_ENTIDADE = 1121742

                       AND TD004.NUMR_ANO_EXERCICIO = 2011

                       AND TD004.CODG_ORGAO = 02

                       AND TD004.CODG_UO = 001

           

          and

           

          SELECT count(*)

                       FROM TD004_ENT_ORGAO_UO TD004

                       WHERE APLIC.TD004.CODG_ENTIDADE = 1121742

                       AND TD004.NUMR_ANO_EXERCICIO = 2011

                       AND TD004.CODG_ORGAO = 02

                       AND TD004.CODG_UO = 001

          • 2. Re: Select clause inside a Procedure with cursor
            aee0ea55-7837-4049-a089-793d2a978474

            I'm sorry, consider all in the same schema.... just forgot to take that out.

            • 3. Re: Select clause inside a Procedure with cursor
              GPU

              I hope your V_COUNT is returning zero. You need to change your conditional logic

               

              IF V_COUNT > 0 THEN

               

              Sorry, I meant if count is greater than zero then ignore the inserts. Like

               

              IF v_count > 0 THEN

                        null;  -- do nothing

              Else

                   -- Process insert

              END IF;

               

              Remember  count() is a group function it will always return one record even if the table is empty.

               

              example:

               

              select count(*) from dual where 1=2; -- This query will always return 1 record, in this case it is zero

               

              Thanks,

              GPU

              • 4. Re: Select clause inside a Procedure with cursor
                aee0ea55-7837-4049-a089-793d2a978474

                I'm returning it to 0.... just forget to put it on the code up... If I have 1 record on the table the v_count value is set to 1..... if I have 2 records, the v_count becomes 2..... is like the where clause is being ignored.

                As I'm working on some Dimensional table for BI output... this is a ETL process, so I need the v_count to be = 0 so no records can be duplicated if some one execute the procedure several times with same parameters.

                • 5. Re: Select clause inside a Procedure with cursor
                  34MCA2K2

                  Is your procedure in the same schema? the procedure might be referring to a different table if it is not..

                  • 6. Re: Select clause inside a Procedure with cursor
                    GPU

                    See below. Where clause will never ignore any condition

                     

                    Note: you need handle null values  specially

                     

                    SQL>
                    SQL> select * from v$version;

                    BANNER                                                                         
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production   
                    PL/SQL Release 11.2.0.2.0 - Production                                         
                    CORE 11.2.0.2.0 Production                                                     
                    TNS for Linux: Version 11.2.0.2.0 - Production                                 
                    NLSRTL Version 11.2.0.2.0 - Production                                         

                    SQL>
                    SQL> set serveroutput on;
                    SQL>
                    SQL> DECLARE
                      2     CURSOR c1
                      3     IS
                      4        WITH dmn
                      5             AS (SELECT 1 col1, 'TEST' col2, NULL col3 FROM DUAL
                      6             UNION ALL
                      7                 SELECT 2 col1, 'TESTING' col2, 'COL3' col3 FROM DUAL
                      8              UNION ALL
                      9                 SELECT 3 col1, 'TESTING123' col2, 'COL3' col3 FROM DUAL
                    10                )
                    11        SELECT *
                    12          FROM dmn;
                    13 
                    14     v_col1    VARCHAR2 (10);
                    15     v_col2    VARCHAR2 (10);
                    16     v_col3    VARCHAR2 (10);
                    17     v_count   NUMBER;
                    18  BEGIN
                    19     OPEN c1;
                    20 
                    21     LOOP
                    22        FETCH c1
                    23        INTO v_col1, v_col2, v_col3;
                    24        EXIT WHEN c1%NOTFOUND;
                    25 
                    26           WITH txn
                    27             AS (SELECT 1 col1, 'TEST' col2, NULL col3 FROM DUAL
                    28                 UNION ALL
                    29                 SELECT 2 col1, 'TESTING' col2, 'COL3' col3 FROM DUAL
                    30                )
                    31        SELECT COUNT (*)
                    32          INTO v_count
                    33          FROM txn
                    34         WHERE  col1 = v_col1
                    35               AND col2 = v_col2
                    36               AND nvl(col3,'##') = nvl(v_col3, '##');
                    37 
                    38           DBMS_OUTPUT.put_line ('V_count = ' || v_count);
                    39        IF v_count = 0
                    40        THEN
                    41           DBMS_OUTPUT.put_line ('Insert is processed for ' || v_col1);
                    42        ELSE
                    43           DBMS_OUTPUT.put_line ('Insert is Ignored for ' || v_col1);
                    44        END IF;
                    45        v_count := 0;
                    46     END LOOP;
                    47  END;
                    48  /
                    V_count = 1                                                                    
                    Insert is Ignored for 1                                                        
                    V_count = 1                                                                    
                    Insert is Ignored for 2                                                        
                    V_count = 0                                                                    
                    Insert is processed for 3                                                      

                    PL/SQL procedure successfully completed.

                     

                    Thanks,

                    GPU

                    • 7. Re: Select clause inside a Procedure with cursor
                      aee0ea55-7837-4049-a089-793d2a978474

                      Thnks guys for the effort, but, still a no go for me..... Yes, the procedure is at the same schema as the tables... and there are no NULL entries in my tables. Executing the script below all the output is equal to 1, becouse I have 1 record on the destiny table, if I insert another record the v_count become 2 on the output, and those records dont meet the requeriments on the select clause after the FETCH.

                       

                      Declare

                       

                       

                         CURSOR cDIMENSAO IS

                      --

                             SELECT O.EXERCICIO, O.ORG_CODIGO, upper(trim(O.ORG_NOME)), UO.UNOR_CODIGO, upper(trim(UO.UNOR_NOME)), E.CODG_ENTIDADE, E.SK_ENTIDADE

                             FROM APLIC.UNIDADE_ORCAMENTARIA UO, APLIC.ORGAO O, APLIC.TD002_ENTIDADE E

                             WHERE UO.ENT_CODIGO = E.CODG_ENTIDADE

                             AND UO.ENT_CODIGO = O.ENT_CODIGO

                             AND UO.ORG_CODIGO = O.ORG_CODIGO

                             AND UO.EXERCICIO = O.EXERCICIO

                             AND UO.EXERCICIO = 2011;

                      --

                             EXERCICIO          APLIC.TD004_ENT_ORGAO_UO.NUMR_ANO_EXERCICIO%TYPE;

                             CODG_ORGAO         APLIC.TD004_ENT_ORGAO_UO.CODG_ORGAO%TYPE;

                             NOME_ORGAO         APLIC.TD004_ENT_ORGAO_UO.NOME_ORGAO%TYPE;

                             CODG_UO            APLIC.TD004_ENT_ORGAO_UO.CODG_UO%TYPE;

                             NOME_UO            APLIC.TD004_ENT_ORGAO_UO.NOME_UO%TYPE;

                             CODG_ENTIDADE      APLIC.TD002_ENTIDADE.CODG_ENTIDADE%TYPE;

                             SK_ENTIDADE        APLIC.TD004_ENT_ORGAO_UO.SK_ENTIDADE%TYPE;

                      --

                             v_sk_ent_orgao_uo  number  :=0;

                             v_sk_exercicio     number  :=0;

                             v_count            number  :=0;

                             v_ano              numeric :=0;

                             v_data_carga       varchar2(10);

                      --

                         BEGIN

                      --

                           v_data_carga := TO_CHAR(SYSDATE, 'DD/MM/YYYY');

                      --

                           IF 2011 is null or 2011 = 0 THEN

                              v_ano := to_number(to_char(sysdate, 'YYYY'));

                           ELSE

                              v_ano := 2011;

                           END IF;

                      --

                           SELECT COUNT(*) INTO v_count

                           FROM   TD004_ENT_ORGAO_UO

                           WHERE  NUMR_ANO_EXERCICIO = v_ano;

                           IF v_count = 0 THEN

                              v_sk_ent_orgao_uo := (v_ano * -1);

                              INSERT INTO TD004_ENT_ORGAO_UO(SK_ENT_ORGAO_UO, NUMR_ANO_EXERCICIO, CODG_ORGAO, NOME_ORGAO, CODG_UO, NOME_UO, CODG_ENTIDADE, SK_ENTIDADE)

                              VALUES (v_sk_ent_orgao_uo,v_ano,'0','NOT INFORMED or NOT FOUNDED','0','NOT INFORMED or NOT FOUNDED','0',-1);

                              COMMIT;

                           END IF;

                      --

                           OPEN cDIMENSAO;

                           LOOP

                             FETCH cDIMENSAO INTO EXERCICIO, CODG_ORGAO, NOME_ORGAO, CODG_UO, NOME_UO, CODG_ENTIDADE, SK_ENTIDADE;

                             EXIT WHEN cDIMENSAO%NOTFOUND;

                             BEGIN

                                   v_count := 0;

                                   SELECT count(td004.sk_ent_orgao_uo) INTO v_count

                                   FROM APLIC.TD004_ENT_ORGAO_UO TD004

                                   WHERE TD004.CODG_ENTIDADE = CODG_ENTIDADE

                                   AND TD004.NUMR_ANO_EXERCICIO = EXERCICIO

                                   AND TD004.CODG_ORGAO = CODG_ORGAO

                                   AND TD004.CODG_UO = CODG_UO;

                                 

                            

                               dbms_output.put_line(v_count);

                             END;

                           END LOOP;

                           CLOSE cDIMENSAO;

                         END;

                      • 8. Re: Select clause inside a Procedure with cursor
                        GPU

                        Are you reseting the v_count variable? I don't see it in your code.

                         

                        v_count  := 0; -- At the end of the loop

                        • 9. Re: Select clause inside a Procedure with cursor
                          aee0ea55-7837-4049-a089-793d2a978474

                          I'm reseting just before the Select Clause.

                          • 10. Re: Select clause inside a Procedure with cursor
                            stratmo

                            Hello,

                             

                            if you like to avoid duplicates in your dimension table, you may think of a different approach:

                            1. What does the PK on the destination-table look like? Could you use it in combination with the "DUP_VAL_ON_INDEX"-exception to avoid those duplicates. (In the case of an exception, you could probably write a log.)

                            2. If the first approach isn't working for you: Could you use the MERGE-statement? See:

                            MERGE

                            If this doesn't help. Could you provide a code snippet (DDL and inserts) which shows your problem.

                             

                            Bye

                            stratmo

                            • 11. Re: Select clause inside a Procedure with cursor
                              34MCA2K2

                              Can you check by dbms_output.put_line what values are fetched and post it here? Please put the create table and insert statements in here if you want solution. There seems to be a oversight instead of an issue here. Can you also try different names of fetch variables than column names?(That may not be an issue, however it removes the confusion).

                               

                              Also if you don't show the values in the parameters after fetch, we are not sure whether you are matching them with the suitable columns.

                               

                              Regards,

                              • 12. Re: Select clause inside a Procedure with cursor
                                aee0ea55-7837-4049-a089-793d2a978474

                                I'm debugging it with PL/SQL Developer, and the fetch is returning the right values for the comparison in the SELECT clause, as so if I copy the select and execute it with this parameters outside the procedure the Count returns 0.

                                • 13. Re: Select clause inside a Procedure with cursor
                                  stratmo

                                  Hi,

                                   

                                  SELECT count(*)

                                               FROM TD004_ENT_ORGAO_UO TD004

                                               WHERE TD004.CODG_ENTIDADE = 1121742

                                               AND TD004.NUMR_ANO_EXERCICIO = 2011

                                               AND TD004.CODG_ORGAO = 02

                                               AND TD004.CODG_UO = 001

                                  Could you tell the datatypes of the columns TD004.CODG_ORGAO and TD004.CODG_UO? Why do you write "02" and "001". In the case they are numbers "2" and "1" are ok.

                                  If they are chars the please add quotes.

                                  If this doesn't work. Please send us your code (DDL, INSERTS, ...)

                                   

                                  Bye

                                  stratmo

                                  • 14. Re: Select clause inside a Procedure with cursor
                                    aee0ea55-7837-4049-a089-793d2a978474

                                    I'm sorry, just forgot to add the quotes as I wrote the script here.... see as

                                     

                                                 SELECT count(*)

                                                 FROM TD004_ENT_ORGAO_UO TD004

                                                 WHERE TD004.CODG_ENTIDADE = '1121742'

                                                 AND TD004.NUMR_ANO_EXERCICIO = '2011'

                                                 AND TD004.CODG_ORGAO = '02'

                                                 AND TD004.CODG_UO = '001'

                                     

                                    all fields are VARCHAR2, in the destiny table and in the target table. Types are the same.

                                    1 2 Previous Next