Category Archives: Oracle

Database triggers implement an object-oriented observer pattern, which means they listen for an event and then take action. A trigger name must be unique among triggers but can duplicate the name of any other object in a schema because triggers … Continue reading →

You must have the CREATE TRIGGER system privilege to create a trigger on an object that you own. If the object is owned by another user, you’ll need that user to grant you the ALTER privilege on the object. Alternatively, … Continue reading →

The largest one is that the trigger body can be no longer than 32,760 bytes. That’s because trigger bodies are stored in LONG datatypes columns. This means you should consider keeping your trigger bodies small. You do that by placing … Continue reading →

1) When programs create and drop temporary tables rather than use Oracle collections. Temporary tables can fragment disk space and degrade database performance over time.

Suppose I am plsql user and I have manage_items package. Granting EXECUTE privilege to purchasing user; syntax : GRANT EXECUTE ON manage_items TO purchasing; How purchasing user can use manage_items ? DESCRIBE plsql.manage_items You can dispense with the schema name … Continue reading →

create or replace package serially_reusable_variables is pragma serially_reusable; protected_constant constant number:=1; unprotected_variable number:=1; end serially_reusable_variables; / create or replace procedure change_package_variable(value number) is begin serially_reusable_variables.unprotected_variable := serially_reusable_variables.unprotected_variable + value; dbms_output.put_line(‘unprotected_variable is ['|| serially_reusable_variables.unprotected_variable||']‘); end; / execute change_package_variable(2); execute change_package_variable(2); execute … Continue reading →

LONG ==> use CLOB or NCLOB now LONG RAW ==> BLOB or BFILE ROWID ==> UROWID (universal rowid)