This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Mar 20, 2013 11:02 AM by 996721 Go to original post RSS
  • 15. Re: Doubts with 2 cursors with a procedure
    996721 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot. The procedure works fine!.

    Regards
1 2 Previous Next

Legend

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