6 Replies Latest reply on Feb 28, 2012 8:00 PM by rp0428

    Stored procedure using insert select and parameter

    tharpa
      I wish to create a stored procedure that performs a select insert , but uses the parameter as the value for one of the columns. This is one thing I've tried:
      create or replace PROCEDURE           "CRT_FUEL_QRTRLY_ADTV_RCRDS" (fuel_id_new varchar2)
      IS
        
        FUEL_ADDITIVE_record_count     number;
       BEGIN
        select count(1) into FUEL_ADDITIVE_record_count
        from FUEL_ADDITIVE
        where FUEL_ID = fuel_id_new;
        
        if FUEL_ADDITIVE_record_count = 0
        then
                insert into FUEL_QUARTERLY_ADDITIVE
                (FUEL_ID, ADDITIVE_ID,  YEAR, QUARTER)PL/SQL and SQL
                values(
                     (select FUEL_ID, ADDITIVE_ID, YEAR, QUARTER
                     from FUEL_ADDITIVE
                     where FUEL_ID = fuel_id_new));
                     
           end if;  
       END;
      but when I compile it, I get the error "ORA-00947; not enough values."

      What is the simplest way of doing this?
        • 1. Re: Stored procedure using insert select and parameter
          Kim Berg Hansen
          VALUES clause of the select is used when you insert one row with variables or constant expressions:
          insert into FUEL_QUARTERLY_ADDITIVE
                    (FUEL_ID, ADDITIVE_ID,  YEAR, QUARTER)
                    values( 1, 'abc', 2012, 4 );
          When inserting the result of a select, do not use VALUES:
          insert into FUEL_QUARTERLY_ADDITIVE
                    (FUEL_ID, ADDITIVE_ID,  YEAR, QUARTER)
                         select FUEL_ID, ADDITIVE_ID, YEAR, QUARTER
                         from FUEL_ADDITIVE
                         where FUEL_ID = fuel_id_new;
          1 person found this helpful
          • 2. Re: Stored procedure using insert select and parameter
            AdamMartin
            Try this:
            create or replace PROCEDURE CRT_FUEL_QRTRLY_ADTV_RCRDS(fuel_id_new varchar2) IS
            BEGIN
                insert into FUEL_QUARTERLY_ADDITIVE
                  (FUEL_ID, ADDITIVE_ID, YEAR, QUARTER)
                select FUEL_ID, ADDITIVE_ID, YEAR, QUARTER
                from FUEL_ADDITIVE
                where FUEL_ID = fuel_id_new;
            END;
            Get rid of the COUNT and the IF. Take out the VALUES keyword.
            • 3. Re: Stored procedure using insert select and parameter
              Manguilibe KAO
              Hi,

              You just can modify the insert into part of your procedure like this:
              if FUEL_ADDITIVE_record_count = 0
                then
                        insert into FUEL_QUARTERLY_ADDITIVE
                        (FUEL_ID, ADDITIVE_ID,  YEAR, QUARTER)
                        select FUEL_ID, ADDITIVE_ID, YEAR, QUARTER
                             from FUEL_ADDITIVE
                             where FUEL_ID = fuel_id_new;
                             
                   end if;
              • 4. Re: Stored procedure using insert select and parameter
                Kim Berg Hansen
                Oh, and by the way - Read Adam Martins reply ;-)

                You do not need to first count and then only insert if rows exist.
                Just do the insert - if there are no rows, it will simply insert nothing.
                • 5. Re: Stored procedure using insert select and parameter
                  tharpa
                  Sorry, Kim, both you and Adam misread the purpose of the count. :) It only inserts records if there are none in the destination table, not the other way around. However, it's now solved anyway. Thanks for your help.
                  • 6. Re: Stored procedure using insert select and parameter
                    rp0428
                    Is there some reason you don't just use MERGE for this? SQL will always be faster, you don't need the procedure and you don't need the extra COUNT query.
                    Even if you want to keep the procedure for some reason you should still use the MERGE to eliminate the unnecessary COUNT query.

                    Just use the WHEN NOT MATCHED THEN INSERT clause

                    See the example in the SQL Language Reference - http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
                    MERGE INTO bonuses D
                       USING (SELECT employee_id, salary, department_id FROM employees
                       WHERE department_id = 80) S
                       ON (D.employee_id = S.employee_id)
                       WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
                         DELETE WHERE (S.salary > 8000)
                       WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
                         VALUES (S.employee_id, S.salary*.01)
                         WHERE (S.salary <= 8000);