1 2 Previous Next 20 Replies Latest reply: Mar 20, 2013 1:02 PM by 996721 Go to original post RSS
      • 15. Re: Doubts with 2 cursors with a procedure
        996721
        SQL> SET ROLE NONE;
        
        Role set.
        
        SQL>  exec update_potencia;
        BEGIN update_potencia; END;
        
        *
        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 "TEST.UPDATE_POTENCIA", line 23
        ORA-06512: at line 1
        SQL>

        By adding these lines,
        EXCEPTION
                         WHEN OTHERS THEN
                         DBMS_OUTPUT.PUT_LINE ('Last Error:' | | DBMS_SQL.LAST_ERROR_POSITION ());
                         DBMS_SQL.close_cursor (source_cursor);
                         RAISE;
        I can see:

        ERROR at line 1:
        ORA-00923: FROM keyword not found where expected
        ORA-06512: at "TEST.UPDATE_POTENCIA", line 54
        ORA-06512: at line 1



        Thanks!

        Edited by: user12086565 on 19/03/2013 09:58
        • 16. Re: Doubts with 2 cursors with a procedure
          BluShadow
          Have you tried to put your SQL statement string in a varchar2 variable and output it with DBMS_SQL.PUT_LINE before the code tries to parse it?
          If you do that, you will be able to see the exact SQL statement that is being passed to DBMS_SQL, and then you are more likely to see where the error is. It could be as simple as a missing space or a missing quote.
          • 17. Re: Doubts with 2 cursors with a procedure
            996721
            Thanks, I found an error in the sql, were very helpful advice. The procedure compiles, but does not perform the insert.

            When I do the insert without variables runs OK
            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, 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 - 4 AND SYSDATE - 3)
                                                                AND A.utctimemax  BETWEEN SYSDATE - 4 AND SYSDATE - 3
                                                                AND B.utctime BETWEEN SYSDATE - 4 AND SYSDATE - 3
                                                                AND C.utctime BETWEEN SYSDATE - 4 AND SYSDATE - 3
                                                                AND a.utctimemax = b.utctime
            1 row created.

            SQL> select * from noa_ave_max_pot;

            POINTNUMBER HORA_MAX_ VALOR_MAX_RTC HORA_NOA VALOR_NOA HORA_MAX_ VALOR_MAX_NOA
            118000 17-MAR-13 3000 17-MAR-13 1 17-MAR-13 200


            But testing the insert within the procedure, even without variables, no insert is done, here the code again:
            CREATE OR REPLACE procedure TEST.update_potencia2
            
            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  (Pointnumber, 
                                                                                               hora_max_rtc, valor_max_rtc, 
                                                                                               hora_noa, valor_noa, 
                                                                                               hora_max_noa, valor_max_noa ) 
                                                                (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 - 4 AND SYSDATE - 3)
                                                                AND A.utctimemax  BETWEEN SYSDATE - 4 AND SYSDATE - 3
                                                                AND B.utctime  BETWEEN SYSDATE - 4 AND SYSDATE - 3
                                                                AND C.utctime  BETWEEN SYSDATE - 4 AND SYSDATE - 3
                                                                AND a.utctimemax = b.utctime)',DBMS_SQL.NATIVE );
                                                                
                            DBMS_OUTPUT.put_line ('Last Error: ' || DBMS_SQL.LAST_ERROR_POSITION ());
                                                               
                            ignore := DBMS_SQL.EXECUTE (source_cursor);
                            
                              
                            DBMS_SQL.CLOSE_CURSOR (source_cursor);
            
                        END LOOP;
             
                    CLOSE from_table;
                                  
            
            END;
            /
            The final code should include ' || v_from_table.table_name ||' iinstead of noa_ave table name.

            I'm looking for the error in the procedure, you can also look at it?

            Thank you very much!

            Edited by: user12086565 on 20/03/2013 05:48

            Edited by: user12086565 on 20/03/2013 06:28
            • 18. Re: Doubts with 2 cursors with a procedure
              BluShadow
              Here's an example of putting the statement in a variable and outputting it so you can see exactly what is being parsed by DBMS_SQL...
              SQL> create table testnumber (x number);
              
              Table created.
              
              SQL> ed
              Wrote file afiedt.buf
              
                1  create or replace procedure ins_number is
                2     cursor cur_tbl is
                3       select table_name
                4       from user_tables
                5       where table_name = 'EMP';
                6     dbmscur integer;
                7     dummy   integer;
                8     v_sql varchar2(32767);
                9  begin
               10    for x in cur_tbl
               11    loop
               12      v_sql := 'insert into testnumber (x)
               13                (select empno from '||x.table_name||')';
               14      dbms_output.put_line('INSERT STATEMENT: '||v_sql);
               15      dbmscur := DBMS_SQL.OPEN_CURSOR;
               16      DBMS_SQL.PARSE(dbmscur,v_sql,DBMS_SQL.NATIVE);
               17      dummy := DBMS_SQL.EXECUTE(dbmscur);
               18      DBMS_SQL.CLOSE_CURSOR(dbmscur);
               19    end loop;
               20* end;
              SQL> /
              
              Procedure created.
              
              SQL> set serverout on
              SQL> exec ins_number;
              INSERT STATEMENT: insert into testnumber (x)
                            (select empno from EMP)
              
              PL/SQL procedure successfully completed.
              
              SQL> select * from testnumber;
              
                       X
              ----------
                    7369
                    7499
                    7521
                    7566
                    7654
                    7698
                    7782
                    7788
                    7839
                    7844
                    7876
                    7900
                    7902
                    7934
              
              14 rows selected.
              Now, do the same with your code, and see what statement is going to be executed by the DBMS_SQL package, and then you will get the exact statement it's being provided with so you can check if it's a valid statement. I won't be repeating this again.

              AND remember to use code tags properly, as described in the FAQ: {message:id=9360002}. It's horrible trying to read unformatted code.
              • 19. Re: Doubts with 2 cursors with a procedure
                996721
                Thanks, it works now!

                I added the lines;
                source_cursor varchar2 (32767);
                .
                .
                dbms_output.put_line('INSERT STATEMENT: '|| source_cursor);
                and I see that the insert is made.

                I'll try with a range of tables, to see that the collection of data is inserted correctly, with data from multiple tables.

                Apologies for asking errors, I will correct from now!

                Regards
                • 20. Re: Doubts with 2 cursors with a procedure
                  996721
                  Thanks a lot. The procedure works fine!.

                  Regards
                  1 2 Previous Next