8 Replies Latest reply: Jan 31, 2013 4:38 PM by 934896 RSS

    stored proc

    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 proc
          Solomon Yakobson
          Change insert satement to:
          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, 
                    program_office,
                    First_Name,
                    Last_Name,
                    Grant_Manager,
                    active
              FROM  (
                     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')
                    );
          SY.
          • 2. Re: stored proc
            Frank Kulash
            Hi,
            931893 wrote:
            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. ...
            Good thinking! Inserting rows one at a time would be more error-prone as well as slower.
            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,
            The DISTINCT keyword always goes right after SELECT.
            Also, you can't use sequence.NEXTVAL in a SELECT DISTINCT query. DO the SELECT DISTINCT in a sub-query, before using the sequence.
            INSERT INTO grant_manager_dim(GRANT_MANAGER_KEY,PROGRAM_OFFICE,FIRST_NAME,LAST_NAME,GRANT_MANAGER,ACTIVE)
            WITH   distinct_values     AS
            (
                 SELECT DISTINCT
                          rspon.code     program_office
                 ,      c.first_name     First_Name
                 ,      ...     -- put the rest of your SELECT statement here
            )
            SELECT  eig_gms.for_dim_grant_manager.NEXTVAL
            ,     program_office
            ,     First_Name
            ,     Last_Name
            ,     Grant_Manager
            ,     active active
            FROM      distinct_values
            ;
            Edited by: Frank Kulash on Jan 31, 2013 9:31 AM
            • 3. Re: stored proc
              934896
              Thanks Solomon
              • 4. Re: stored proc
                934896
                Thanks Frank
                • 5. Re: stored proc
                  934896
                  Guys came accross another problem I just wanted to add a UNION to the statement here is it

                  INSERT INTO grant_manager_dim(GRANT_MANAGER_KEY,PROGRAM_OFFICE,FIRST_NAME,LAST_NAME,GRANT_MANAGER,ACTIVE)
                  WITH distinct_values AS
                  (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
                  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'))
                  SELECT eig_gms.for_dim_grant_manager.NEXTVAL,
                  program_office,
                  first_name,
                  last_name,
                  Grant_manager,
                  active
                  FROM distinct_values
                  UNION
                  SELECT -1 grant_manager_key,
                       -1 Program_office,
                  N/A first_name,
                       N/A last_name,
                       N/A Grant_Manager,
                       N/A Active
                       FROM DUAL;



                  I get an error sequence number not allowed here.

                  If I take the Union out it works just fine, I just want to add another row.

                  Thanks
                  • 6. Re: stored proc
                    sb92075
                    How do I ask a question on the forums?
                    SQL and PL/SQL FAQ


                    scroll down to #9 to learn how to use
                     tags!                                                                                                                                                                                                                                                                                                                                            
                    • 7. Re: stored proc
                      Frank Kulash
                      Hi,
                      931893 wrote:
                      Guys came accross another problem I just wanted to add a UNION to the statement here is it

                      INSERT INTO grant_manager_dim(GRANT_MANAGER_KEY,PROGRAM_OFFICE,FIRST_NAME,LAST_NAME,GRANT_MANAGER,ACTIVE)
                      WITH distinct_values AS
                      (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, ...
                      You may have noticed that this site normally doesn't display multiple spaces in a row.
                      Whenever you post formatted text (such as query results) on this site, type these 6 characters:

                      \
                      (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
                      If you do that, your code will look like this:
                      INSERT INTO grant_manager_dim(GRANT_MANAGER_KEY,PROGRAM_OFFICE,FIRST_NAME,LAST_NAME,GRANT_MANAGER,ACTIVE)
                      WITH distinct_values AS
                      (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, ...
                      It's much easier to read and understand with the extra spaces.  (I don't think quite so many extra spaces are really needed in this case, though.)
                      
                      
                      UNION
                      SELECT -1 grant_manager_key,
                           -1 Program_office,
                      N/A first_name,
                      Don't forget to enclose string literals in single-quotes; for example:
                      'N/A' first_name
                           N/A last_name,
                           N/A Grant_Manager,
                           N/A Active
                           FROM DUAL;



                      I get an error sequence number not allowed here.

                      If I take the Union out it works just fine, I just want to add another row.
                      Then just add another row, using a separate INSERT statement. Edited by: Frank Kulash on Jan 31, 2013 1:40 PM There is no net advantage in doing a UNION here.  I know, 1 INSERT statement is (in general) faster than 2, but the cost of doing a UNION will be greater than the cost of doing a separate INSERT statement.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                      • 8. Re: stored proc
                        934896
                        That makes sense, Thanks Frank