Cacher is the code snippet organizer for pro developers

/* Formatted on 2016/02/10 17:06 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR get_emp
   IS
      SELECT rtrim(ltrim(DECODE (SIGN (INSTR (emp_name, '.')),
                     1, SUBSTR (emp_name, INSTR (emp_name, '.', -1) + 1)
                    , emp_name))) emp_name,
             emp_no, gender, '%' || nat || '%' nat, birth, DEGREE, address,
             mobile, email, status, CERTIFICATE, job
        FROM emp_temp_table;

   v_app_no   NUMBER := 1;
BEGIN
   FOR rec IN get_emp
   LOOP
      v_app_no := v_app_no + 1;

      INSERT INTO hrs_applications
                  (application_no, job_no, first_name, father_name, mid_name,
                   last_name, nationality, gender, marital_status,
                   birth_date, address, mobile, application_status,
                   application_date, user_id, religion_code, company_id,
                   e_mail, blood_group, is_citizenship, sponsor_type,
                   degree_code, birth_country, employment_type)
         SELECT v_app_no, a.job_no,
                SUBSTR (rec.emp_name, 1, INSTR (rec.emp_name, ' ') - 1),
                SUBSTR (rec.emp_name,
                        INSTR (rec.emp_name, ' ') + 1,
                        INSTR (rec.emp_name, ' ', 1, 2) - INSTR (rec.emp_name, ' ') 
                       ),  
                SUBSTR (rec.emp_name,
                        INSTR (rec.emp_name, ' ', 1, 2) + 1,
                        decode(INSTR (rec.emp_name, ' ', 1, 3),0,99, INSTR (rec.emp_name, ' ', 1, 3)) - INSTR (rec.emp_name, ' ', 1, 2)
                       ),
                NVL(
                    SUBSTR (rec.emp_name,
                        decode(INSTR (rec.emp_name, ' ', 1, 3),0,99, INSTR (rec.emp_name, ' ', 1, 3)) + 1), '.'
                   ),
                b.country_no, DECODE (rec.gender, 'ذكر', 1, 2),
                DECODE (rec.status, 'أعزب', 1, 'متزوج', 2, 1), rec.birth,
                rec.address, rec.mobile, 1, SYSDATE, 1, 1, 1, rec.email, 9, 0,
                1, c.degree_code, b.country_no, 1
           FROM hrs_jobs a, gen_countries b, hrs_certificate_degrees c
          WHERE a.job_name = rec.job
            AND b.nationality LIKE rec.nat
            AND c.degree_desc = rec.DEGREE
            AND rownum < 2;
   END LOOP;
END;

select rec.emp_name, SUBSTR (rec.emp_name, 1, INSTR (rec.emp_name, ' ') - 1) Part1,
                SUBSTR (rec.emp_name,
                        INSTR (rec.emp_name, ' ') + 1,
                        INSTR (rec.emp_name, ' ', 1, 2) - INSTR (rec.emp_name, ' ') 
                       ) Part2,  
                SUBSTR (rec.emp_name,
                        INSTR (rec.emp_name, ' ', 1, 2) + 1,
                        decode(INSTR (rec.emp_name, ' ', 1, 3),0,99, INSTR (rec.emp_name, ' ', 1, 3)) - INSTR (rec.emp_name, ' ', 1, 2)
                       ) Part3,
                SUBSTR (rec.emp_name,
                        decode(INSTR (rec.emp_name, ' ', 1, 3),0,99, INSTR (rec.emp_name, ' ', 1, 3)) + 1) Part4
from (SELECT rtrim(ltrim(DECODE (SIGN (INSTR (emp_name, '.')),
                     1, SUBSTR (emp_name, INSTR (emp_name, '.', -1) + 1)
                    , emp_name))) emp_name
        FROM emp_temp_table) rec;