This discussion is archived
8 Replies Latest reply: Nov 17, 2012 1:41 AM by stratmo RSS

pl sql procedure is hung

597673 Newbie
Currently Being Moderated
Below procedure takes forever. No result comes out. just hang!!! would you please take a look below my logic.
I think my logic is wrong. would you please correct my logic.

Thank you so much!!!!
###
declare
v_emp_id number;
v_emp_name varchar2(10);
v_emp_grade varchar2(10);      
v_salary number;
v_bonus number;

          
     CURSOR cur_emp
IS
SELECT emp_id,emp_name,emp_grade
FROM employee_t;
     
     TYPE ref_cur_empsalary is REF CURSOR;
     type_ref_cur_empsalary ref_cur_empsalary ;
     
query_str VARCHAR2(1000);
     

BEGIN
open cur_emp;
          fetch cur_emp into v_emp_id,v_emp_name,v_emp_grade;
          while cur_emp%found loop
          
          query_str := 'SELECT emp_id, sum(a.salary) as salary ,sum(a.bonus) as
bonus from test_cost a ';

          open type_ref_cur_empsalary for query_str ;
                    while type_ref_cur_empsalary %found loop
                    fetch type_ref_cur_empsalary into v_emp_id,
v_salary,v_bonus;
                         
DBMS_OUTPUT.PUT_LINE (v_emp_id||v_salary||v_bonus);
end loop;
               close type_ref_cur_empsalary ;                    
                    
               end loop;
               close cur_emp;
     end ;

Edited by: Wen on Nov 15, 2012 7:39 PM
  • 1. Re: pl sql procedure is hung
    sb92075 Guru
    Currently Being Moderated
    why are you doing this in PL/SQL when it can be done in plain SQL?
    DECLARE 
        v_emp_id               NUMBER; 
        v_emp_name             VARCHAR2(10); 
        v_emp_grade            VARCHAR2(10); 
        v_salary               NUMBER; 
        v_bonus                NUMBER; 
        CURSOR cur_emp IS 
          SELECT emp_id, 
                 emp_name, 
                 emp_grade 
          FROM   employee_t; 
        TYPE ref_cur_empsalary IS ref CURSOR; 
        type_ref_cur_empsalary REF_CUR_EMPSALARY; 
        query_str              VARCHAR2(1000); 
    BEGIN 
        OPEN cur_emp; 
    
        FETCH cur_emp INTO v_emp_id, v_emp_name, v_emp_grade; 
    
        WHILE cur_emp%FOUND LOOP 
            query_str := 'SELECT emp_id, sum(a.salary) as salary ,sum(a.bonus) as  bonus from spt_schema.fxe_intl_curr_cost a '; 
    
            OPEN type_ref_cur_empsalary FOR query_str; 
    
            WHILE type_ref_cur_empsalary %FOUND LOOP 
                FETCH type_ref_cur_empsalary INTO v_emp_id, v_salary, v_bonus; 
    
                dbms_output.Put_line (v_emp_id 
                                      ||v_salary 
                                      ||v_bonus); 
            END LOOP; 
    
            CLOSE type_ref_cur_empsalary; 
        END LOOP; 
    
        CLOSE cur_emp; 
    END; 
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: pl sql procedure is hung
    rp0428 Guru
    Currently Being Moderated
    >
    Below procedure takes forever. No result comes out. just hang!!! would you please take a look below my logic.
    I think my logic is wrong. would you please correct my logic.
    >
    No - you need to correct the logic.

    This code takes forever
    while true loop
      null;
    end loop;
    Why would that code NOT take forever? There is no way to exit the loop
    fetch cur_emp into v_emp_id,v_emp_name,v_emp_grade;
    while cur_emp%found loop
    Of course your code takes forever. There is no way to exit the loop. You do one FETCH of cur_emp BEFORE the loop so if that fetch is successful the WHILE loop will just keep looping since you never FETCH that cursor again.

    You should use the standard CURSOR FOR LOOP as shown in example 4-24 'Using the EXIT Statement in a FOR Loop' in the PL/SQL Language doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/controlstructures.htm#CJACCEAC
    SQL> DECLARE
      2     v_employees employees%ROWTYPE;
      3     CURSOR c1 is SELECT * FROM employees;
      4  BEGIN
      5    OPEN c1;
      6    -- Fetch entire row into v_employees record:
      7    FOR i IN 1..10 LOOP
      8      FETCH c1 INTO v_employees;
      9      EXIT WHEN c1%NOTFOUND;
     10      -- Process data here
     11    END LOOP;
     12    CLOSE c1;
     13  END;
     14  /
    In your case since you use a cursor you would use something like
      OPEN c1;
      LOOP                                                 --Loop added
        FETCH c1 INTO v LIMIT 3; -- process 3 records at a time
        EXIT WHEN c1%NOTFOUND;
       . . . 
  • 3. Re: pl sql procedure is hung
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    rp0428 wrote:

    In your case since you use a cursor you would use something like
    OPEN c1;
    LOOP                                                 --Loop added
    FETCH c1 INTO v LIMIT 3; -- process 3 records at a time
    EXIT WHEN c1%NOTFOUND;
    . . . 
    The exit check needs to follow the processing - so it should look something as follows:
    open cursor;
    loop
      fetch cursor bulk collect into collection_array limit max_rows_to_fetch;
      .. process collection (e.g. for i in 1..collection.Count )
      exit when cursor%NotFound;
    end loop;
    close cursor;
    @OP: However - the above (or similar FOR) cursor loop structure is very seldom needed. It is row-by-row processing and is slow and expensive and almost never how one processes data sets in Oracle.
  • 4. Re: pl sql procedure is hung
    stratmo Newbie
    Currently Being Moderated
    Hi wen,

    what are you looking for? Is this just an example for a much more complex task? Or are you just looking to print the result of a select to the screen/file. In this case you should use pure SQL in SQL*Plus and spool the results. You may have to use some SET options for your needs to get the output the way you need it.
    See: [url http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#BACGAJIC] SQL*Plus SET-options
    Your first look should go to HEADING, LINESIZE, ECHO, FEEDBACK, TRIMSPOOL and PAGESIZE.

    Well, as always SQL should be faster than PL/SQL. I cant't remember an example in which it is the other way round.

    Regards

    stratmo

    Edited by: stratmo on Nov 16, 2012 7:16 AM ("than" in last line forgotten)
  • 5. Re: pl sql procedure is hung
    597673 Newbie
    Currently Being Moderated
    I want to do as following;
    step 1 : cursor cur_emp //get emp_id and emp_grade

    step 2: query_str(get variable emp_grade dynamically from cursor cur_emp in bonus table)

    step 3: open type_ref_cur_empsalary for query_str //i.g-using emp_grade(aa) check each row in bonus table if match with grade(aa) then get sum(salary )and sum(bonus)

    would you mind tell me what is sample structure(query or pl sql)
    again thank you so much for your answer!!
  • 6. Re: pl sql procedure is hung
    rp0428 Guru
    Currently Being Moderated
    >
    The exit check needs to follow the processing
    >
    No - I forgot to remove the limit clause to just do a single row fetch. Thanks though. ;)
  • 7. Re: pl sql procedure is hung
    597673 Newbie
    Currently Being Moderated
    Thank you so much for your help. It helps me a lot to understand pl sql.
    Thank YOu!
  • 8. Re: pl sql procedure is hung
    stratmo Newbie
    Currently Being Moderated
    Hi Wen,

    this is all about your first posted code assuming that the topic "exiting" is corrected as needed.
    >
    step 1 : cursor cur_emp //get emp_id and emp_grade
    >
    So you want to cycle over your table "EMPLOYEE_T" and get all data-sets ("employees") from this table
    OK! But here you fetch the columns emp_grade and emp_name. Afterwards this Info is never used in your code, isnt it?
    Why do you do this?

    >
    step 2: query_str(get variable emp_grade dynamically from cursor cur_emp in bonus table)
    >

    See above.

    >
    step 3: open type_ref_cur_empsalary for query_str //i.g-using emp_grade(aa) check each row in bonus table if match with grade(aa) then get sum(salary )and sum(bonus)
    >
    Where do you join the tables employee_t and test_cost?
    I had a real close look at your code, but I couldn't find the connection between those two selects on employee_t and test_cost. As a result you perform a cross-join on those two tables. If your "query_str" would work which it doesn't.
    -- a select like this
    query_str := 'SELECT  emp_id as emp_id
                          , sum(a.salary) as salary 
                          , sum(a.bonus) as bonus 
                  from test_cost a ';
    /* would result into the error
    The "group by a.emp_id" is missing in your first version,isn't it? You have got 
    aggregate-functions on col two and three and no aggregate-function on emp_id
    this should result in
    "ORA-00937: not a single-group group function
    00937. 00000 -  "not a single-group group function"
    *Cause:    
    *Action:
    Fehler in Zeile: Y Spalte: X"*/
    You need a group by clause on a.emp_id to make this work.

    Adding all your steps, I think, you would like to get a result showing emp_id, emp_grade, emp_name, sum_salary per employee, sum_bonus per employee

    This can be achieved by pure SQL
    with employee_info as(
    SELECT SELECT emp_id, 
           emp_name, 
           emp_grade 
    FROM   employee_t),
    employee_cash as(
    SELECT a.emp_id
           , sum(a.salary)    as salary 
           , sum(a.bonus)     as bonus 
    from   spt_schema.fxe_intl_curr_cost a 
    group by a.emp_id -- ADDED!!!
    )
    select   employee_info.emp_id       as employee_id
             , employee_info.emp_name   as emp_name
             , employee_info.emp_grade  as emp_grade
             , employee_cash.salary     as salary_sum
             , employee_cash.bonus      as bonus_sum
    from  employee_info info
          , employee_cash cash
    -- Join on emp_id this is the natural join!
    -- Please check!
    where info.emp_id = cash.emp_id;
    Don't judge me by any errors which may happen. I couldn't test it. I did not have your tables.
    This only works if one employee has exactly one emp_grade and one emp_name.

    If this does what you need then you may try to get your result via this statement in SQL*Plus and spool the result into a file. (See above)
    If you are by any means want to use PL/SQL you can use this statement for your Cursor Loop.

    Just a sketch:
    declare
          lc_sep      varchar2(10) :=','; -- Seperator for columns
    begin
    
       for icur_emp_data 
       in ( with 
       employee_info as(
               SELECT emp_id, 
                      emp_name, 
                      emp_grade 
               FROM   employee_t),
       employee_cash as(
               SELECT a.emp_id
                      , sum(a.salary) as salary 
              ,sum(a.bonus) as  bonus 
       from   spt_schema.fxe_intl_curr_cost a 
       group by a.emp_id -- ADDED!!!
       ),
       select   info.emp_id       as employee_id
                , info.emp_name   as emp_name
                , info.emp_grade  as emp_grade
                , cash.salary     as salary_sum
                , cash.bonus      as bonus_sum
       from  employee_info info
             , employee_cash cash
       -- Join on emp_id this is the natural join!
       -- Please check!
       where info.emp_id = cash.emp_id)
       loop
       dbms_output.Put_line (  
          icur_emp_data.employee_id || lc_sep ||
          icur_emp_data.emp_name || lc_sep ||
          icur_emp_data.salary||v_salary || lc_sep ||
          icur_emp_data.bonus);
       end loop;
    
    end;
    /
    Best regards

    stratmo

Legend

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