This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Mar 20, 2013 11:02 AM by 996721 RSS

Doubts with 2 cursors with a procedure

996721 Newbie
Currently Being Moderated
Hello, I would like information about how to work with two cursors on a procedure.

In a package, I have two procedures:

UPDATE_ALL_POINTNUMBER

Gets the list of tables and jump to another procedure (see line 100)

'update_max_x_table (v_owner | |'. '| | v_table_name, v_table_name | |' POT 'pdate, p_commit);'


UPDATE_MAX_X_TABLE the other procedure, is responsible for obtaining the data and assemble sql_dinamico necessary update.

What I find is how to pass the 2nd procedure variables to find the cursor, that is, how to combine the two cursors listings obtained.

The idea is to build a dynamic SQL to do updates on a 3rd table.

Please, I need to find a solution, can someone help me?

Thank you very much!

Here the code:
--header

CREATE OR REPLACE PACKAGE TEST.PKG_POINTNUMBER_MAX_VALUE IS

    PROCEDURE UPDATE_MAX_X_TABLE (P_TABLE_NAME_NOA    IN VARCHAR2,
                                  P_TABLE_NAME_RTC    IN VARCHAR2,
                                  P_DEST_TABLE_NAME IN VARCHAR2, 
                                  P_DATE            IN DATE,                                                          
                                  P_COMMIT          IN BOOLEAN DEFAULT FALSE);
-- *****************************************************************
    PROCEDURE UPDATE_ALL_POINTNUMBER(P_DATE      IN DATE,
                                     --P_OWNER   IN VARCHAR2 DEFAULT NULL,
                                     P_COMMIT  IN BOOLEAN DEFAULT FALSE);

END;
/

--body

CREATE OR REPLACE PACKAGE BODY TEST.PKG_POINTNUMBER_MAX_VALUE AS

                                                 
    PROCEDURE UPDATE_MAX_X_TABLE(P_TABLE_NAME_NOA  IN VARCHAR2,
                                 P_TABLE_NAME_REC  IN VARCHAR2,
                                 P_DEST_TABLE_NAME IN VARCHAR2, 
                                 P_DATE            IN DATE, 
                                 P_COMMIT          IN BOOLEAN DEFAULT FALSE) 
    IS
        CUR_POINT_NUMBERS VARCHAR2(1000);
        CUR_UTC_TIME_MAX VARCHAR(500);
        
        P_CURSOR_POINTS_NUMBERS  SYS_REFCURSOR;
        P_CURSOR_UTC_TIME_MAX    SYS_REFCURSOR;
        
        V_CANT NUMBER;
        
        INSERT_SQL VARCHAR2(1000);
        UPDATE_SQL VARCHAR2(1000);

        v_utctimemax_rtc    DATE;
        v_value_max_rtc     FLOAT;
        v_utctime_max_noa   DATE;
        v_value_max_noa     FLOAT;
        v_utctime_noa       DATE;
        v_value_noa         FLOAT;        
       
    BEGIN
       
        CUR_POINT_NUMBERS := ' select a.utctimemax, a.value, b.utctime, b.value, c.utctime, c.value '
                            || ' from '
                            || P_TABLE_NAME_REC
                            || ' a, '
                            || P_TABLE_NAME_NOA
                            || ' b '
                            || P_TABLE_NAME_NOA
                            || ' c '
                            || '   where a.value in (select max(value) from rtc_estaciones) '
                            || '   and c.value in  (select max(value) from noa_ave where utctime between sysdate -2 and sysdate -1)'
                            || '   and b.utctime between sysdate -2 and sysdate -1 '
                            || '   and c.utctime between sysdate -2 and sysdate -1 '
                            || ' and a.utctimemax = b.utctime rtc ';
                                                                             
        OPEN P_CURSOR_POINTS_NUMBERS FOR CUR_POINT_NUMBERS USING P_DATE, P_DATE, P_DATE, P_DATE;
        LOOP
            FETCH P_CURSOR_POINTS_NUMBERS INTO  v_utctimemax_rtc, v_value_max_rtc, v_utctime_max_noa, v_value_max_noa, v_utctime_noa, v_value_noa;
            EXIT WHEN P_CURSOR_POINTS_NUMBERS%NOTFOUND;
            
            UPDATE_SQL := 'UPDATE ' || p_dest_table_name
                                    || '    SET   HORA_MAX_RTC  =: v_utctimemax_rtc, '
                                            || '    SET   VALOR_MAX_RTC =: v_value_max_rtc, '
                                            || '    SET   HORA_AVE      =: v_utctime_noa, '
                                            || '    SET   VALOR_AVE     =: v_value_noa, '
                                            || '    SET   HORA_MAX_AVE  =: v_utctime_max_noa, '
                                            || '    SET   VALUE_MAX_AVE =: v_value_max_noa ';

            EXECUTE IMMEDIATE UPDATE_SQL USING v_utctimemax_rtc, v_value_max_rtc, v_utctime_max_noa, v_value_max_noa, v_utctime_noa, v_value_noa;
            V_CANT  := SQL%ROWCOUNT;

        END LOOP;  
        CLOSE P_CURSOR_POINTS_NUMBERS;
        
        IF (P_COMMIT) THEN COMMIT; END IF;


    END;                                                 
                                                    
    PROCEDURE UPDATE_ALL_POINTNUMBER(P_DATE               IN DATE,
                                     --P_OWNER            IN VARCHAR2 DEFAULT NULL,
                                     P_COMMIT           IN BOOLEAN DEFAULT FALSE)
    IS
       CURSOR c_lista_tablas_noa IS
            SELECT *
            FROM USER_TABLES
            WHERE TABLE_NAME LIKE 'NOA%'
            AND TABLE_NAME NOT LIKE '%MAX%'
            AND TABLE_NAME NOT LIKE '%MIN%'
            ;
       
       CURSOR c_lista_tablas_rtc IS
            SELECT *
            FROM USER_TABLES
            WHERE TABLE_NAME LIKE 'RTC%'
            ; 
     
        
    --v_owner all_tables.owner%type;
    v_table_name all_tables.table_name%type;
            
    BEGIN
        NULL;
        
        OPEN c_lista_tablas_noa;
        LOOP
           FETCH c_lista_tablas_noa INTO  v_table_name_noa;
           EXIT WHEN c_lista_tablas_noa%NOTFOUND;
           
           BEGIN
           
                    update_max_x_table(v_table_name_noa,  v_table_name_noa || '_POT' ,p_date, p_commit);
                    
           EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
           END;
                       
        END LOOP;
        CLOSE c_lista_tablas_noa; 
        
        OPEN c_lista_tablas_rtc;
        LOOP
           FETCH c_lista_tablas_rtc INTO  v_table_name_rtc;
           EXIT WHEN c_lista_tablas_rtc%NOTFOUND;
           
           BEGIN
           
                    update_max_x_table(v_table_name_rtc,  v_table_name_rtc || '_POT' ,p_date, p_commit);
                    
           EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);
           END;
                       
        END LOOP;
        CLOSE c_lista_tablas_rtc;         
        
    END;                                                                
                                                                 
END PKG_POINTNUMBER_MAX_VALUE;
/
Edited by: user12086565 on 13/03/2013 12:57

Edited by: BluShadow on 14-Mar-2013 09:07
corrected {noformat}
{noformat} tags.  Please read {message:id=9360002} to learn how to post your question correctly.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 1. Re: Doubts with 2 cursors with a procedure
    SomeoneElse Guru
    Currently Being Moderated
    You can start by fixing your code tags:
    Put one of these before all your code and one after.                                                                                                                                                                                                                                            
  • 2. Re: Doubts with 2 cursors with a procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user12086565 wrote:

    The idea is to build a dynamic SQL to do updates on a 3rd table.
    Silly idea. Most of the time.

    Dynamic SQL is the wrong answer to 99% of problems. What makes you think you are facing that 1% exception that can only be solved with dynamic SQL?

    And before answering that - don't you think that one of the reasons you are struggling to get this dynamic SQL "solution" designed and coded, points to the fact that this solution is NOT the correct answer?

    Or are you in the habit of choosing to address problems with such complex solutions, that you are not able to design and code that complex solution? Surely not.

    So why then do exactly that, in this case?

     
    PS. Cannot recall when I last saw an actual sensible and meaningful dynamic SQL solution discussed in this forum. And dynamic SQL questions are posted daily, and often multiple different ones on the same day. So excuse my cynicism and pessimistic view that your problem is not the ellusive 1% exception, that justifies the use of dynamic SQL.
  • 3. Re: Doubts with 2 cursors with a procedure
    996721 Newbie
    Currently Being Moderated
    Mr Billy Verreynne, before anything anyone would love to be treated with kindness and warmth in any field. So please follow the basic rules of courtesy and respect between people.

    Be that you can not help someone who requests it.?

    These responses really make people starting to develop, refuses to deal with people like you, so haughty and arrogant.

    If you do not want to help, surely find the solution elsewhere, which apparently is what you prefer.

    regards

    Edited by: user12086565 on 14/03/2013 05:21
  • 4. Re: Doubts with 2 cursors with a procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user12086565 wrote:
    Mr Billy Verreynne, before anything anyone would love to be treated with kindness and warmth in any field.
    I would prefer accuracy and the truth. Not warm loving fuzzies. After all, this is a technical field where accuracy and correctness are overriding factors in resolving technical problems.
    Be that you can not help someone who requests it.?
    No. In fact I do not want to see people running into a technical brick wall due to poor design and poor code, or due to not understanding the applicable technicalities .

    Nor do I want to see people being helped to move the wall a few metres further, thinking that is the solution, only to run even faster into it.
    These responses really make people starting to develop, refuses to deal with people like you, so haughty and arrogant.
    Sorry you interpret it that way. The fact is that dynamic SQL is one of the MOST abused features in Oracle development. And the MOST insecure.

    Every day we get postings about "+how to make dynamic SQL+" work. The vast majority of them use dynamic SQL incorrectly, and for the wrong reasons. Your posting, unfortunately, seems to be no different.

    That is what I am trying to convey - make you stop and think what you are doing and why you are doing that. As running into the wall hurts a lot.
  • 5. Re: Doubts with 2 cursors with a procedure
    BluShadow Guru Moderator
    Currently Being Moderated
    user12086565 wrote:
    Mr Billy Verreynne, before anything anyone would love to be treated with kindness and warmth in any field. So please follow the basic rules of courtesy and respect between people.

    Be that you can not help someone who requests it.?

    These responses really make people starting to develop, refuses to deal with people like you, so haughty and arrogant.

    If you do not want to help, surely find the solution elsewhere, which apparently is what you prefer.
    Billy hasn't been uncourteous in his reply. It may be blunt and to the point, and lacking the love and hugs of a social forum, but that's because this is a technical forum.
    You've asked a question, essentially saying "I have some dynamic SQL that isn't working well, help me to write my dynamic SQL better", and Billy is correctly pointing out that 99% of the time (I would actually put it at 99.9% myself) people attempt to use dynamic SQL when it's not needed, and it is a known fact that dynamic SQL can lead to all sorts of issues, from buggy code that isn't validated at compile time, to lack of scalability, poor use of bind variables, potential SQL injection issues etc.

    Perhaps if you were to explain what the business logic is that you are trying to solve then, rather than people trying to help you fix poor code (not meant offensively) by providing you with more poor code (but poor code that executes), they can actually advise you of a better way of going about your requirements that doesn't involve (mis-)using dynamic SQL.
  • 6. Re: Doubts with 2 cursors with a procedure
    996721 Newbie
    Currently Being Moderated
    So what are the measures to be taken to write a code rich?

    that examples known to perform inserts / updates in cursors, from a range of tables to a third table Variable?

    you guys can help me with this or not?
  • 7. Re: Doubts with 2 cursors with a procedure
    BluShadow Guru Moderator
    Currently Being Moderated
    user12086565 wrote:
    So what are the measures to be taken to write a code rich?

    that examples known to perform inserts / updates in cursors, from a range of tables to a third table Variable?

    you guys can help me with this or not?
    We could help if we knew what the requirements are that you are trying to solve, but you haven't explained that.
  • 8. Re: Doubts with 2 cursors with a procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user12086565 wrote:
    So what are the measures to be taken to write a code rich?
    Not sure what you imply - good code is not "rich", but "lean".

    Programming is about removing lines of code - not adding lines of code. Simplicity is the ultimate form of elegance when engineering software.

    that examples known to perform inserts / updates in cursors, from a range of tables to a third table Variable?
    you guys can help me with this or not?
    As Blu said - the actual problem statement is unclear. You posted what seems to be a flawed and broken solution, asking how it can be fixed. You did not state what problem this solution is to address. And without understanding the problem, how can we offer meaningful suggestions in how that broken solution can be turned into not only a working solution, but a proper and robust one at that?

    General comment about the code posted.

    Modularise. This is the single most important fundamental in software engineering. Good code is ALWAYS modularised. Without any exception.

    A module needs to do ONE thing only and do that thing well.

    The reason why modularisation is so important, is that a complex problem is not solved with a single brush stroke - with a single large and complex piece of code.

    Complex problems need to be analysed and pulled apart into smaller problems. These smaller problems are then solved individually. And by doing so, the overall complex problem is solved in a logical, understandable and robust way. The result of this process will be modularisation of the code, where each module solves a single specific problem, and all the modules together solve the complex problem.

    Not adhering to this software engineering fundamental... the resulting code WILL be bulky, rigid, inflexible, slow, unable to scale, unable to deal with changes and modifications to the problem, hard to understand, even harder to maintain, etc, etc.

    Get this fundamental right, and most of the problems you have with that mess of code you've posted, will be addressed.
  • 9. Re: Doubts with 2 cursors with a procedure
    996721 Newbie
    Currently Being Moderated
    Thanks, I will try to apply these tips to the code and repeat the query with more tools and 'modules' specific.
    regards
  • 10. Re: Doubts with 2 cursors with a procedure
    996721 Newbie
    Currently Being Moderated
    Hello, after working on a new structure have obtained new lines of code:
    create or replace procedure test.test 
    IS CURSOR from_table
    IS SELECT table_name
        FROM user_tables
        WHERE table_name LIKE '%AVE';
        
    v_from_table from_table%ROWTYPE;
    
    source_cursor INTEGER;
    
    ignore INTEGER;
    
    BEGIN
            OPEN from_table;
                LOOP
                    FETCH from_table INTO v_from_table;
                    EXIT WHEN from_table%NOTFOUND;
                    source_cursor := DBMS_SQL.OPEN_CURSOR;
                    DBMS_SQL.PARSE(source_cursor,  'INSERT INTO noa_ave_max_pot d (d.Pointnumber, 
                                                        d.hora_max_rtc, d.valor_max_rtc, 
                                                        d.hora_noa, d.valor_noa, 
                                                        d.hora_max_noa, d.valor_max_noa ) 
                                                        (SELECT a.pointnumber pointnumber, 
                                                        a.utctimemax hora_max_rtc, valor_max_rtc a.value, 
                                                        b.utctime hora_ave, b.VALUE valor_ave,
                                                        c.utctime hora_max_ave, c.VALUE value_max_ave
                                                        FROM rtc_estaciones a, ' || v_from_table.table_name ||' b, 
                                                                               ' || v_from_table.table_name ||' c
                                                        WHERE A.value IN (SELECT MAX (VALUE)
                                                        FROM rtc_estaciones)
                                                        AND C.VALUE IN (SELECT MAX (VALUE)
                                                        FROM ' || v_from_table.table_name ||'
                                                        WHERE utctime BETWEEN  SYSDATE - 3 AND SYSDATE - 2)
                                                        AND A.utctimemax  BETWEEN SYSDATE - 3 AND SYSDATE - 2
                                                        AND B.utctime  BETWEEN SYSDATE - 3 AND SYSDATE - 2
                                                        AND C.utctime  BETWEEN SYSDATE - 3 AND SYSDATE - 2
                                                        AND a.utctimemax = b.utctime)',DBMS_SQL.NATIVE);
                    ignore := DBMS_SQL.EXECUTE(source_cursor);
                    DBMS_SQL.CLOSE_CURSOR(source_cursor);
                END LOOP;
            CLOSE from_table;
    END; 
    but when run I see this error:

    SQL>/
    DECLARE
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
    ORA-06512: at "SYS.DBMS_SQL", line 39
    ORA-06512: at line 20

    I have the necessary permissions for execution on them but I can not see the contents of these packages, because they are wrapped.

    "SYS.DBMS_SYS_SQL"
    "SYS.DBMS_SQL"

    They could tell me any indication that change to avoid the error?

    Thank you very much.!

    Objective: from a range of tables, for maximum and time / date, with maximum value and time / date of a 2nd table, along with another couple of data value, date / time of it, and then insert the result in a 3rd table.
    Oracle version 10.2.0.3.0
    
    SQL> desc noa_ave --> || v_from_table.table_name || --> table range
    
    SQL> describe noa_ave
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     UTCTIME                                   NOT NULL DATE
     POINTNUMBER                               NOT NULL NUMBER(38)
     VALUE                                              FLOAT(126)
     TLQ                                                NUMBER(38)
    
    SQL> describe rtc_estaciones
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     UTCTIME                                   NOT NULL DATE
     POINTNUMBER                               NOT NULL NUMBER(38)
     VALUE                                              FLOAT(126)
     TLQ                                                NUMBER(38)
     UTCTIMEMAX                                         DATE
    
    SQL> desc noa_ave_max_pot
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     POINTNUMBER                               NOT NULL NUMBER(38)
     HORA_MAX_RTC                              NOT NULL DATE
     VALOR_MAX_RTC                                      FLOAT(126)
     HORA_NOA                                  NOT NULL DATE
     VALOR_NOA                                          FLOAT(126)
     HORA_MAX_NOA                              NOT NULL DATE
     VALOR_MAX_NOA                                      FLOAT(126)
    
    SQL>
    Edited by: user12086565 on 19/03/2013 08:23

    Edited by: user12086565 on 19/03/2013 08:32
  • 11. Re: Doubts with 2 cursors with a procedure
    BluShadow Guru Moderator
    Currently Being Moderated
    You should populate a variable with your SQL string and then use DBMS_OUTPUT.PUT_LINE to see what the result looks like, so you can check you have valid SQL before you try and actually execute it.
  • 12. Re: Doubts with 2 cursors with a procedure
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ


    First make it work; then make it fancy!
  • 13. Re: Doubts with 2 cursors with a procedure
    996721 Newbie
    Currently Being Moderated
    when It executing the sql without variables, it works!
    INSERT INTO noa_ave_max_pot d (d.Pointnumber, 
                                 d.hora_max_rtc, d.valor_max_rtc, 
                                 d.hora_noa, d.valor_noa, 
                                 d.hora_max_noa, d.valor_max_noa )
                                 /*VALUES*/ (SELECT a.pointnumber pointnumber, 
                                 a.utctimemax hora_max_rtc, a.value valor_max_rtc , 
                                 b.utctime hora_ave, b.VALUE valor_ave,
                                 c.utctime hora_max_ave, c.VALUE value_max_ave
                                 FROM rtc_estaciones a, noa_ave b,  noa_ave c
                                 WHERE A.value IN (SELECT MAX (VALUE)
                                                   FROM rtc_estaciones)
                                 AND C.VALUE IN (SELECT MAX (VALUE)
                                                        FROM noa_ave
                                                        WHERE utctime BETWEEN  SYSDATE - 3 AND SYSDATE - 2)
                                                        AND A.utctimemax  BETWEEN SYSDATE - 3 AND SYSDATE - 2
                                                        AND B.utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2
                                                        AND C.utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2
                                                        AND a.utctimemax = b.utctime);
  • 14. Re: Doubts with 2 cursors with a procedure
    sb92075 Guru
    Currently Being Moderated
    privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

    back at the SQL> prompt first do as below

    SET ROLE NONE
    now post results from executing again the SQL statement
1 2 Previous Next

Legend

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