8 Replies Latest reply: Nov 17, 2012 3:41 AM by stratmo RSS

    pl sql procedure is hung

    597673
      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
          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
            >
            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
              Billy~Verreynne
              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
                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
                  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
                    >
                    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
                      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
                        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