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

Select clause inside a Procedure with cursor

aee0ea55-7837-4049-a089-793d2a978474 Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    I'm reseting just before the Select Clause.

  • 10. Re: Select clause inside a Procedure with cursor
    stratmo Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points