INSERT INTO + COMMIT in packages

what is the best solution to write a oracle package for record persistence? I've always written something like this:

    create or replace
    PACKAGE BODY           "USP_PRICELIST" AS

      PROCEDURE  usp_TABLE1Save 
        (
        pErrorCode              OUT NUMBER,
        pMessage                OUT VARCHAR2,       
        pPARAM1                 IN CHAR,
        pPARAM2             IN CHAR
        )

        IS

    BEGIN

        pErrorCode := 0;

        INSERT INTO 开发者_JAVA百科TABLE1
          (PARAM1, PARAM2)
        VALUES
          (pPARAM1, pPARAM2);
        EXCEPTION
                 WHEN OTHERS THEN pErrorCode := SQLCODE; pMessage := SQLERRM;

    END usp_TABLE1Save;

END USP_PRICELIST;

and I was wondering if I have to COMMIT after the INSERT INTO.

Alberto

I would not put a commit in the procedure, and leave that to the code that calls the procedure. This allows the procedure to be used as part of a larger transaction. The insert is not implicitly committed.

It really depends on whether you want your operation to take part in a transaction or to be atomic.

Be careful, if you place the commit in the package it will commit the entire transaction

create table testcommit (colA varchar2(50)) ;

DECLARE
  PROCEDURE SELFCOMMIT(VAL IN TESTCOMMIT.COLA%TYPE) AS
                BEGIN
                     INSERT INTO TESTCOMMIT(COLA) VALUES(VAL);
                     COMMIT ;
                END SELFCOMMIT ;
  PROCEDURE NOCOMMIT(VAL IN TESTCOMMIT.COLA%TYPE) AS
                BEGIN
                     INSERT INTO TESTCOMMIT(COLA) VALUES(VAL);
                END NOCOMMIT ;              


BEGIN
  INSERT INTO TESTCOMMIT(COLA) VALUES('INITIAL');
  SELFCOMMIT('FIRST SELF COMMIT');
  ROLLBACK ; --KILL TRANSACTION

  INSERT INTO TESTCOMMIT(COLA) VALUES('SECOND MAIN INSERT');
  NOCOMMIT('NO AUTO COMMIT');
  ROLLBACK;


END ;
/
SELECT * FROM TESTCOMMIT;

-->
COLA                                               
-------------------------------------------------- 
INITIAL                                            
FIRST SELF COMMIT  
-->NOTE THE SELFCOMMIT AFFECTS THE ENTIRE TRANSACTION, THUS RENDERING THE ROLLBACK MOOT

--drop table testcommit;

You should also look at the concept of autonomous transactions

By default Oracle has no auto-commit, so you have to.