Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: Oracle procedure to read the rows

    Hi guys,

    I need to modify the procedure to read all the lines of a text.Actually in my table
    there is a personnel number column with duplicates and a TEXT column.I need to
    create a procedure to read all the text per personnel number and insert it into a another table.The below code is the procedure which is reading the two lines of text field.Please let me know how can I modify it to read all the text per personnel number.

    Thanks in advance.

    create PROCEDURE hr_load_restict_text_proc
    as
    CURSOR cur_hr_restrict
    IS
    SELECT personnel_number, line
    FROM hr_restriction_text_temp
    ORDER BY personnel_number, examination_date;

    lv_hr_rec cur_hr_restrict%ROWTYPE := NULL;
    lv_hr_perno hr_restriction_text_temp.personnel_number%TYPE := NULL;
    lv_upd_perno hr_restriction_text_temp.personnel_number%TYPE := 'X';
    lv_commit_num PLS_INTEGER := 19999;
    lv_counter_num PLS_INTEGER := 0;
    lv_prod_obj VARCHAR2 (3) := 'AMR';
    BEGIN
    -- log_err('hr_load_restict_text_proc: ',lv_prod_obj, 'START' ) ;
    /* truncate DIM table */
    EXECUTE IMMEDIATE 'TRUNCATE TABLE hr_restriction_text_dim';

    lv_counter_num := 0;

    <<hr_loop>>
    FOR lv_hr_rec IN cur_hr_restrict
    LOOP
    IF (lv_upd_perno <> lv_hr_rec.personnel_number)
    THEN
    BEGIN
    lv_hr_perno := lv_hr_rec.personnel_number;

    INSERT INTO hr_restriction_text_dim (personnel_number,
    restriction_text)
    VALUES (lv_hr_rec.personnel_number, lv_hr_rec.line
    );

    lv_counter_num := lv_counter_num + SQL%ROWCOUNT;
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX
    THEN
    UPDATE hr_restriction_text_dim
    SET restriction_text =
    RTRIM (restriction_text || ' ' || lv_hr_rec.line)
    WHERE personnel_number = lv_hr_rec.personnel_number;

    lv_upd_perno := lv_hr_rec.personnel_number;
    lv_counter_num := lv_counter_num + SQL%ROWCOUNT;
    END;
    END IF;

    adm_load_dims_pkg.adm_do_commit (lv_counter_num, lv_commit_num);
    END LOOP hr_loop;
    END hr_load_restict_text_proc;
    exec hr_load_restict_text_proc;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why all the PL/SQL code when task can be done using a single SQL statement

    Code:
    INSERT INTO HR_RESTRICTION_TEXT_DIM SELECT * FROM .....
    BTW - temp tables are rarely required in Oracle. Do you also use MS SQLServer?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2013
    Posts
    2
    Anacedent has just beat me to it, but I was going to ask the same, why all the PL/SQL for what appears to be a simple INSERT or MERGE operation? Never use PL/SQL when SQL will suffice.

    This would achieve the same thing (assuming rows may already exist for personnel_number in hr_restriction_text_dim). You're basically attempting to replicate the MERGE function by your use of the dup_val_on_index error catching.

    Code:
    MERGE INTO hr_restriction_text_dim hrtd
    USING (  SELECT   hrtd.personnel_number,
                      LISTAGG(line, CHR(10)) WITHIN GROUP (ORDER BY line) as combined_restriction
             FROM     hr_restriction_text_temp
             GROUP BY hrtd.personnel_number ) hrtt
    ON    (hrtd.personnel_number = hrtt.personnel_number)
    WHEN MATCHED THEN
       UPDATE SET restriction = hrtt.combined_restriction
    WHEN NOT MATCHED THEN
       INSERT (personnel_number, restriction)
       VALUES (hrtt.personnel_number, combined_restriction);

  4. #4
    Join Date
    Jun 2013
    Posts
    3

    oracle procedure to read the rows

    Hi XVR,
    Thank you for replying.
    I am getting the below error when I try to execute your code.
    Please help me and modify the procedure to read all text.

    Pleaseeeeeeeeeeeeeee..

    Error at line 1
    ORA-00923: FROM keyword not found where expected

  5. #5
    Join Date
    Jun 2013
    Posts
    2
    Quote Originally Posted by niranjan479 View Post
    Hi XVR,
    Thank you for replying.
    I am getting the below error when I try to execute your code.
    Please help me and modify the procedure to read all text.

    Pleaseeeeeeeeeeeeeee..

    Error at line 1
    ORA-00923: FROM keyword not found where expected
    Try this, the code above was untested, just ran a quick test case and this works fine:

    Code:
    --Create test data
    CREATE TABLE hr_restriction_text_dim (personnel_number VARCHAR2(5), restriction VARCHAR2(2000));
    CREATE TABLE hr_restriction_text_temp (personnel_number VARCHAR2(5), line VARCHAR2(2000));
    INSERT INTO hr_restriction_text_temp VALUES (1, 'line 1');
    INSERT INTO hr_restriction_text_temp VALUES (1, 'line 2');
    INSERT INTO hr_restriction_text_temp VALUES (1, 'line 3');
    INSERT INTO hr_restriction_text_temp VALUES (2, 'line 1 for personnel 2');
    INSERT INTO hr_restriction_text_dim VALUES (1, 'existing data');
    
    --Perform merge
    MERGE INTO hr_restriction_text_dim hrtd
    USING (  SELECT   personnel_number,
                      LISTAGG(line, CHR(10)) WITHIN GROUP (ORDER BY line) as combined_restriction
             FROM     hr_restriction_text_temp
             GROUP BY personnel_number ) hrtt
    ON    (hrtd.personnel_number = hrtt.personnel_number)
    WHEN MATCHED THEN
       UPDATE SET restriction = hrtt.combined_restriction
    WHEN NOT MATCHED THEN
       INSERT (personnel_number, restriction)
       VALUES (hrtt.personnel_number, combined_restriction);
     
    --Verify results
    SELECT * FROM hr_restriction_text_dim;
       1  line 1
          line 2
          line 3
       2  line 1 for personnel 2

Tags for this Thread

Posting Permissions

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