Getting SQL statement ignored in procedure

Question

Am getting pl/sql statement ignored error in my pl/sql procedure where SELECT starts in sql developer while compile

PROCEDURE sort_emp_records (
        p_instance_id   IN VARCHAR2,
        p_filename      IN VARCHAR2,
        p_max_batch_rec IN NUMBER,
        x_status_code   OUT VARCHAR2,
        x_error_message OUT VARCHAR2
    ) IS
BEGIN
insert into LCK_EMPLY_TARG_TBL
select VAL_STATUS trunc((rowno + p_max_batch_rec - 1) / p_max_batch_rec) LOOP_SET_ID
from (
  select VAL_STATUS, emp_id, row_number() over (order by status) rowno
  from LCK_EMPLY_SRC_TBL 
  where VAL_STATUS = 'VALID'
) dst
EXCEPTION
    WHEN OTHERS THEN
        x_status_code := sqlcode;
        x_error_message := sqlerrm;
END sort_emp_records;

Answer 1

Firstly, you are missing your column names in your insert query. In the comments section you said that your table has 26 columns, you need to point them out by doing something like this:

insert into LCK_EMPLY_TARG_TBL (column_name_1,column_name_2)

Another case is that you are missing a separator between your columns in the select:

select VAL_STATUS
       ,trunc((rowno + p_max_batch_rec - 1) / p_max_batch_rec) LOOP_SET_ID

The last problem with your query you are missing a semicolon at the end, as it was previously mentioned by @KoenLostrie in the comment secion.

Finally, your code should more or less look like this (you have to only replace column_name_1,column_name_2 to the real column names that you want to put your values from the select query):

PROCEDURE sort_emp_records (
        p_instance_id   IN VARCHAR2,
        p_filename      IN VARCHAR2,
        p_max_batch_rec IN NUMBER,
        x_status_code   OUT VARCHAR2,
        x_error_message OUT VARCHAR2
    ) IS
BEGIN
insert into LCK_EMPLY_TARG_TBL (column_name_1,column_name_2)
select VAL_STATUS, trunc((rowno + p_max_batch_rec - 1) / p_max_batch_rec) LOOP_SET_ID
from (
  select VAL_STATUS, emp_id, row_number() over (order by status) rowno
  from LCK_EMPLY_SRC_TBL 
  where VAL_STATUS = 'VALID'
) dst;
EXCEPTION
    WHEN OTHERS THEN
        x_status_code := sqlcode;
        x_error_message := sqlerrm;
END sort_emp_records;