PL/SQL stored procedure Cursor vs Insert into Select * From table_name

IT_ETL
IT_ETL used Ask the Experts™
on
I have written below two stored procedures in PL/SQL. Functionaly of both stored procedures are same. In stored procedure PROC_A cursor was used and in stored procedure PROC_B insert into select * from table_name was used.

I knew that Oracle older version was supporting insert into select * from table_name inside PL/SQL block to process single record but was not supporting if multiple records need to be processed, on the other hand cursor could easily process multiple records.

My obvious question, does Oracle 10g support insert into select * from table_name inside PL/SQL block as I need to process thousands of records. Below stored procedure PROC_B is an example. Can I develop all the codes using format of stored procedure PROC_B. Do I need COMMIT command after the end of insert into select * from table_name statement in PROC_B? Is there any issues/concerns of using format of stored procedure PROC_B but not format of stored procedure PROC_A? Please suggest...

CREATE OR REPLACE PROCEDURE ECHOLIVE.PROC_A
IS
 CURSOR A IS
  SELECT *
  FROM ECHOLIVE.T_MREF_TOUCHPOINT;
 
 A_VAR A%ROWTYPE;
 
BEGIN
 OPEN A;
 FETCH A INTO A_VAR;
 WHILE A%FOUND
  LOOP
   INSERT INTO TMP_DAILY_CNT (ID_MREF_TOUCHPOINT, NAME)
   VALUES (A_VAR.ID_MREF_TOUCHPOINT, A_VAR.NAME);
   COMMIT;
   FETCH A INTO A_VAR;
  END LOOP;
 CLOSE A;
END PROC_A;


CREATE OR REPLACE PROCEDURE METRIC.PROC_B
IS
 
BEGIN
 
 INSERT INTO METRIC.TEST_BONUS_IN_PROGRESS
        (CARDHOLDER_ID, EARNING_RULE_NAME, EARNING_RULE_BANKED_RULE_CODE, EARNING_TIMESTAMP, TRANSACTION_AMOUNT,
        PROGRAM_UNIQUE_IDENTIFIER, TRANSACTION_ID, EARNING_RULE_ID)
 SELECT CARDHOLDER_ID, EARNING_RULE_NAME, EARNING_RULE_BANKED_RULE_CODE, EARNING_TIMESTAMP, TRANSACTION_AMOUNT,
        PROGRAM_UNIQUE_IDENTIFIER, TRANSACTION_ID, EARNING_RULE_ID
 FROM METRIC.BONUS_IN_PROGRESS;
   
END PROC_B;



 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
yes 10g supports,   insert into  select from  as in proc_b

as do all other versions of oracle, at least going back to version 7.
Commented:
10g Supports your coding style in Proc_B , as sdstuber said that has something to do with back Compatibility for older versions of Oracle till Oracle 7 .
Commented:
In Proc B all the records are from METRIC.BONUS_IN_PROGRESS are inserted into METRIC.TEST_BONUS_IN_PROGRESS in one go, so if there are 1000 records all will be commited if the transaction is successful, and none of the records will be inserted if
The pros and cons:
In Proc B all the records  from METRIC.BONUS_IN_PROGRESS are inserted into METRIC.TEST_BONUS_IN_PROGRESS in one go.  If there are 1000 records all will be commited if you specify commit in the end.
In case if there is error (due to bad data) none of the records will be inserted, Where as in Proc A, you can do exception handling and discard the errorneous record as the process is done via cursor.
Proc B is efficient then Proc A.  However if you are considering to move bulk data, its recommended to optimize the server settings.
Hope it helps

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial