Skip to Main Content
  • Questions
  • How to loop a select statement while inserting the values in a table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mukti.

Asked: July 14, 2016 - 1:13 pm UTC

Last updated: July 14, 2016 - 3:50 pm UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

create or replace PROCEDURE PROC_PROJ_ID_AUTO_GENERATE(op_error_code OUT VARCHAR2,
op_succ_msg OUT VARCHAR2)

BEGIN


FOR i
IN (select a.program_id , a.PRODUCTIVITY_IDENTIFIER ,TARGET_START_DATE ,TARGET_COMP_DATE , PRGM_OWNER ,PRGM_DESCRIPTION,
(case when a.PRODUCTIVITY_IDENTIFIER = 1 then 'PRDTY' || p.prgm_name else p.prgm_name end ) prgm_name
from (SELECT distinct PROGRAM_ID, PRODUCTIVITY_IDENTIFIER FROM PROJECT_ID_CREATION where PRJ_TYPE = 'T') a ,
program p where active =1 and p.program_id = a.program_id) k


LOOP
INSERT into PROJECT_ID_CREATION (project_id,PROGRAM_ID,PROJ_NAME,PROJ_OWNER_SID,PRJ_CLARITY_STATUS,Location,PROJ_COST_CENTRE,PROJ_LOCAL_COST_CENTRE,TARGET_START_DATE,TARGET_COMPLETION_DATE,PROJ_IN_OUT_PLAN,PRODUCTIVITY_IDENTIFIER,PROJ_DESCRIPTION)

values (GET_PROJECT_ID_TEST (a.L_PROGRAM_ID,a.L_PRODUCTIVITY_IDENTIFIER),k.PROGRAM_ID,k.prgm_name,v_PRGM_OWNER_SID,'DRAFT','','','',k.TARGET_START_DATE,k.TARGET_COMP_DATE,1,k.PRODUCTIVITY_IDENTIFIER,k.PRGM_DESCRIPTION)
END LOOP;

COMMIT;

op_succ_msg := '0';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
OP_ERROR_CODE := SQLCODE;
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (OP_ERROR_CODE || ' :' || SQLERRM);
END;

how can I insert values into the table PROJECT_ID_CREATION based on the select statement written inside the for loop? I have created a function GET_PROJECT_ID_TEST to auto generate the project id.
Please let me know what needs to be change in the above code.

and we said...

Delete the loop. Insert the result of the query directly into the table!

for example:

create or replace procedure proc_proj_id_auto_generate (
    op_error_code out varchar2,
    op_succ_msg out varchar2 )
as
begin
  insert
  into project_id_creation
    (
      project_id,program_id,proj_name,
      proj_owner_sid,prj_clarity_status,location,
      proj_cost_centre,proj_local_cost_centre,target_start_date,
      target_completion_date,proj_in_out_plan,productivity_identifier,
      proj_description
    )
  select get_project_id_test ( a.l_program_id,a.l_productivity_identifier ) , a.program_id, (
    case
      when a.productivity_identifier = 1 then 'PRDTY' ||
        p.prgm_name
      else p.prgm_name
    end ) prgm_name , v_prgm_owner_sid,'DRAFT',
    '', '','',
    target_start_date , target_comp_date , 1,
    a.productivity_identifier ,prgm_description
  from
    ( select distinct program_id, productivity_identifier
    from project_id_creation
    where prj_type = 'T'
    ) a , program p
  where active     =1
  and p.program_id = a.program_id;
end;
/


Why have you created GET_PROJECT_ID_TEST? Why not just use a sequence?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why not just DML error logging?

Duke Ganote, July 14, 2016 - 8:51 pm UTC

DML error logging seems more direct...

http://tkyte.blogspot.com/2005/07/how-cool-is-this.html