1 Reply Latest reply: Jan 30, 2013 5:12 PM by rp0428 RSS

    Stored procedure

    934896
      I have a stored procedure. I do not want to use a cursor but I want to have a direct insert into a table using a select statement for performance issues.

      Here is the select statement.

      SELECT DISTINCT rspon.code program_office,
      c.first_name First_Name,
      c.last_name Last_Name,
      TRIM (c.first_name) || ' ' || TRIM (c.last_name) Grant_Manager,
      c.active active
      FROM gms_assignment a,
      gms_app_assign_int int,
      gms_contact c,
      gms_application app,
      gms_solicitation sol,
      ref_spons_entity rspon,
      gms_award awd,
      gms_award_summary awds
      WHERE app.gms_application_id = int.gms_application_id
      AND a.gms_assignment_id = int.gms_assignment_id
      AND c.gms_contact_id = a.gms_contact_id
      AND sol.active = 'Y'
      AND a.end_date IS NULL
      AND a.ref_code_id_type = 24636
      AND app.gms_solicitation_id = sol.gms_solicitation_id
      AND sol.ref_spons_entity_id <> 11 -- G T
      AND sol.REF_SPONS_ENTITY_ID = rspon.ref_spons_entity_id
      AND app.gms_application_id = awd.gms_application_id
      AND awd.gms_award_summary_id = awds.gms_award_summary_id
      AND awds.award_status IN ('60', '90', '91');

      This runs fine.

      Now I want to insert it into a table that has these columns (PROGRAM_OFFICE,FIRST_NAME,LAST_NAME,GRANT_MANAGER,ACTIVE)

      If I write a statement

      INSERT INTO grant_manager_dim(GRANT_MANAGER_KEY,PROGRAM_OFFICE,FIRST_NAME,LAST_NAME,GRANT_MANAGER,ACTIVE)
      SELECT DISTINCT rspon.code program_office,
      c.first_name First_Name,
      c.last_name Last_Name,
      TRIM (c.first_name) || ' ' || TRIM (c.last_name) Grant_Manager,
      c.active active
      FROM gms_assignment a,
      gms_app_assign_int int,
      gms_contact c,
      gms_application app,
      gms_solicitation sol,
      ref_spons_entity rspon,
      gms_award awd,
      gms_award_summary awds
      WHERE app.gms_application_id = int.gms_application_id
      AND a.gms_assignment_id = int.gms_assignment_id
      AND c.gms_contact_id = a.gms_contact_id
      AND sol.active = 'Y'
      AND a.end_date IS NULL
      AND a.ref_code_id_type = 24636
      AND app.gms_solicitation_id = sol.gms_solicitation_id
      AND sol.ref_spons_entity_id <> 11 -- G T
      AND sol.REF_SPONS_ENTITY_ID = rspon.ref_spons_entity_id
      AND app.gms_application_id = awd.gms_application_id
      AND awd.gms_award_summary_id = awds.gms_award_summary_id
      AND awds.award_status IN ('60', '90', '91');

      This also runs fine.

      Now I want to add another column GRANT_MANAGER_KEY and want to use a sequence for it. So I create a sequence and write a stored proc


      CREATE OR REPLACE PROCEDURE EIG_GMS.Insert_grant_mgr IS
      v_gm_key NUMBER;

      BEGIN

      INSERT INTO grant_manager_dim(GRANT_MANAGER_KEY,PROGRAM_OFFICE,FIRST_NAME,LAST_NAME,GRANT_MANAGER,ACTIVE)
      SELECT EIG_GMS.FOR_DIM_GRANT_MANAGER.NEXTVAL,
      DISTINCT rspon.code program_office,
      c.first_name First_Name,
      c.last_name Last_Name,
      TRIM (c.first_name) || ' ' || TRIM (c.last_name) Grant_Manager,
      c.active active
      FROM gms_assignment a,
      gms_app_assign_int int,
      gms_contact c,
      gms_application app,
      gms_solicitation sol,
      ref_spons_entity rspon,
      gms_award awd,
      gms_award_summary awds
      WHERE app.gms_application_id = int.gms_application_id
      AND a.gms_assignment_id = int.gms_assignment_id
      AND c.gms_contact_id = a.gms_contact_id
      AND sol.active = 'Y'
      AND a.end_date IS NULL
      AND a.ref_code_id_type = 24636
      AND app.gms_solicitation_id = sol.gms_solicitation_id
      AND sol.ref_spons_entity_id <> 11 -- G T
      AND sol.REF_SPONS_ENTITY_ID = rspon.ref_spons_entity_id
      AND app.gms_application_id = awd.gms_application_id
      AND awd.gms_award_summary_id = awds.gms_award_summary_id
      AND awds.award_status IN ('60', '90', '91');

      COMMIT;
      END;
      /

      this does not compile and gives me an error at DISTINCT , is there a work around, I really do not want to use a cursor.

      Thanks
        • 1. Re: Stored procedure
          rp0428
          Welcome to the forum!

          Unfortunately you posted in the wrong forum. This forum, as the title says, is for SQL Developer (Not for general SQL/PLSQL questions.

          Mark this question ANSWERED and repost it in the SQL and PL/SQL forum.
          PL/SQL

          In your new thread provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).