can you commit inside functions

[hastha23]

Dear friends,

Is possible commit inside functions?

Regards,
Hastha 23

[rsrinivasan]

Dear friends,

Is possible commit inside functions?

Regards,
Hastha 23

Hi,
Ya. It is possible manually give commit in function. In java you can set commit by this..

conn.setAutoCommit(true);

Thanks,
Srinivas r.

[debasisdas]

U can't commit within a function.

the function will compile but u get error at run time like this

"cannot perform a DDL, commit or rollback inside a query or DML"

but if u commit after the return statment it will compile and execute but the COMMMIT command will not execute because the control will return at the RETURN statment.

[Medhatithi]

Dear friends,

Is possible commit inside functions?

Regards,
Hastha 23

Of course, you can commit inside a function. Also, you can execute ddl statements inside a function uing dynamic sql

[bhushanbagul]

Dear Hastha

Definitely we can use commit inside oracle function but programatically it's not a good practice to use commit inside oracle function because for performing DML operations we have oracle procedures. However i m providing below oracle function for ur reference in which commit has been used.

CREATE OR REPLACE FUNCTION commit_inside_func (pi_dml_opr_flag VARCHAR2)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
po_rec_id NUMBER (10);
BEGIN
CASE
WHEN UPPER (pi_dml_opr_flag) = 'I'
THEN
INSERT INTO test_record
(rec_id, rec_desc
)
VALUES (test_record_sq.NEXTVAL, 'insert performed'
)
RETURNING rec_id
INTO po_rec_id;
WHEN UPPER (pi_dml_opr_flag) = 'U'
THEN
UPDATE test_record
SET rec_desc = 'update performed';

po_rec_id := SQL%ROWCOUNT;
WHEN UPPER (pi_dml_opr_flag) = 'D'
THEN
UPDATE test_record
SET rec_desc = 'delete performed',
rec_status = 'D';

po_rec_id := SQL%ROWCOUNT;
END CASE;

COMMIT;
RETURN (po_rec_id);
EXCEPTION
WHEN OTHERS
THEN
po_rec_id := NULL;
RETURN (po_rec_id);
END commit_inside_func;

I have used test_record table inside function as below
CREATE TABLE TEST_RECORD
(
REC_ID NUMBER(10),
REC_DESC NVARCHAR2(50),
REC_STATUS NVARCHAR2(1) DEFAULT 'A'
);

Regds
Bhushan

[Medhatithi]

Dear Hastha

Definitely we can use commit inside oracle function but programatically it's not a good practice to use commit inside oracle function because for performing DML operations we have oracle procedures. However i m providing below oracle function for ur reference in which commit has been used.

CREATE OR REPLACE FUNCTION commit_inside_func (pi_dml_opr_flag VARCHAR2)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
po_rec_id NUMBER (10);
BEGIN
CASE
WHEN UPPER (pi_dml_opr_flag) = 'I'
THEN
INSERT INTO test_record
(rec_id, rec_desc
)
VALUES (test_record_sq.NEXTVAL, 'insert performed'
)
RETURNING rec_id
INTO po_rec_id;
WHEN UPPER (pi_dml_opr_flag) = 'U'
THEN
UPDATE test_record
SET rec_desc = 'update performed';

po_rec_id := SQL%ROWCOUNT;
WHEN UPPER (pi_dml_opr_flag) = 'D'
THEN
UPDATE test_record
SET rec_desc = 'delete performed',
rec_status = 'D';

po_rec_id := SQL%ROWCOUNT;
END CASE;

COMMIT;
RETURN (po_rec_id);
EXCEPTION
WHEN OTHERS
THEN
po_rec_id := NULL;
RETURN (po_rec_id);
END commit_inside_func;

I have used test_record table inside function as below
CREATE TABLE TEST_RECORD
(
REC_ID NUMBER(10),
REC_DESC NVARCHAR2(50),
REC_STATUS NVARCHAR2(1) DEFAULT 'A'
);

Regds
Bhushan

In fact, if we use DML Operation inside a function, then this function cannot be used in the select clause of any query