Oracle FAQS for JNTU CSE students andPL/SQL developers

How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this: FOR records IN my_cursor LOOP ...do some stuff... COMMIT;END LOOP;COMMIT;
... to ... FOR records IN my_cursor LOOP ...do some stuff... i := i+1; IF mod(i, 10000) = 0 THEN -- Commit every 10000 records COMMIT; END IF;END LOOP;COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
What is the difference between stored procedures and functions?
Functions are normally used for computations where as procedures are normally used for executing business logic.
Functions MUST return a value, procedures doesn't need to.
You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query. For example, if you have a function that is updating a table, you cannot call that function from a SQL query.
- select myFunction(field) from sometable; will throw error.
Function returns 1 value only. Procedure can return multiple values (max 1024).
Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values(SQL Server). Stored procedure is precompiled execution plan where as functions are not.
A procedure may modify an object where a function can only return a value.
PS: In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.