Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    17

    Unanswered: PL/SQL: Dynamic SQL issue

    I'm having some issues with a PL/SQL script I'm working on. Here is the script:

    PHP Code:
    DECLARE
      
    TYPE c_ref IS REF CURSOR;

      
    c_table        c_ref;
      --
    c_table_rec   c_table%ROWTYPE;
      
    v_plant_no     varchar2);
      
    v_count        number := 0;
      
    v_owner        varchar212 );
      
    v_table_name   varchar231 );
      
    v_sql          varchar2500 );
      
    v_errorcode    number;
      
    v_errortext    varchar2512 );

      
    CURSOR c_main
      IS
          SELECT a
    .ownera.table_name
            FROM sys
    .dba_tab_cols a JOIN sys.dba_tables b
                   ON a
    .owner || a.table_name b.owner || b.table_name
           WHERE a
    .owner IN ('ABC''XYZ')
             AND 
    column_name 'PLANT_NO'
             
    AND ( a.table_name LIKE 'QCTRL%'
               
    OR a.table_name LIKE 'QHIST%'
               
    OR a.table_name LIKE 'QRPTS%'
               
    OR a.table_name LIKE 'QSTAG%'
               
    OR a.table_name LIKE 'QTRAN%'
               
    OR a.table_name LIKE 'QXREF%' )
        
    ORDER BY a.ownera.table_name;
    BEGIN
      OPEN c_main
    ;

      
    LOOP
        FETCH c_main INTO v_owner
    v_table_name;

        EXIT 
    WHEN c_main%NOTFOUND;

        
    v_count := v_count 1;

        
    DBMS_OUTPUT.put_line
        
    (
          
    LPADv_owner10 ) || '.' || RPADv_table_name33 ) || ' ' || LPADv_count)
        );

        
    v_sql :=
          
    'UPDATE ' ||
          
    v_owner ||
          
    '.' ||
          
    v_table_name ||
          
    ' SET PLANT_NO = ''C'' WHERE PLANT_NO IN (''A'',''B'') AND 1=2;';
        
    DBMS_OUTPUT.put_linev_sql );

        
    EXECUTE IMMEDIATE v_sql

      
    END LOOP;

      
    COMMIT;

      
    CLOSE c_main;
    EXCEPTION
      WHEN OTHERS
      THEN
        ROLLBACK
    ;
        
    v_errorcode := SQLCODE;
        
    v_errortext := SQLERRM;

        
    qfc_qrmtips.qpisa_process_msg_logNULLNULLNULLNULL'USER''ERROR',
        
    'Error in plant consolidation script at location.',
        
    TO_CHARv_errorcode ) || ' :: ' || v_errortext );
    END;

    When executed like this, it appears to stop as soon as it hits the "Execute Immediate" statement because my DBMS_OUTPUT only shows one record. But I do not get an error. When I comment our the EI statement, it loops like it is supposed to. What am I missing?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    For the beginning, I'd completely remove the WHEN OTHERS exception handler and let Oracle tell its opinion.

    If you know what exactly you want to handle (such as TOO MANY ROWS or DUP VAL ON INDEX or ...), handle it. For any other error, let Oracle handle it. Don't worry, it will, actually, tell you what has happened and will not obfuscate additional information (such as error code line), which your handling successfully does.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I might be missing something, but if I understand your code correctly the UPDATE statement is created with a WHERE condition that will never update anything:

    WHERE PLANT_NO IN ('A', 'B') AND 1=2;

    So what's the intention here?

  4. #4
    Join Date
    Jan 2006
    Posts
    17
    Quote Originally Posted by shammat
    I might be missing something, but if I understand your code correctly the UPDATE statement is created with a WHERE condition that will never update anything:

    WHERE PLANT_NO IN ('A', 'B') AND 1=2;

    So what's the intention here?
    Haha. Yeah. I had another DBMS_OUTPUT line in there earlier that printed the SQL string. I put the 1=2 code in there just so I can leave the rest of the statement without it executing. When I loop the procedure and see that it is working correctly, then i'll remove the 1=2.

  5. #5
    Join Date
    Jan 2006
    Posts
    17
    Quote Originally Posted by Littlefoot
    For the beginning, I'd completely remove the WHEN OTHERS exception handler and let Oracle tell its opinion.

    If you know what exactly you want to handle (such as TOO MANY ROWS or DUP VAL ON INDEX or ...), handle it. For any other error, let Oracle handle it. Don't worry, it will, actually, tell you what has happened and will not obfuscate additional information (such as error code line), which your handling successfully does.
    Hmmmm..... Invalid character in my SQL string. Odd. Like I said, I output the SQL string and it look fine.

  6. #6
    Join Date
    Jan 2006
    Posts
    17
    Think I got it. Apparently, the EXECUTE IMMEDIATE doesn't like the ending semicolon in the v_sql variable.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So now you know: WHEN OTHERS is bad, WHEN OTHERS is evil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •