Author: Dev

How to list all procedures from a schema of Oracle database To list all procedures from a schema of Oracle database you can query views: USER_PROCEDURES, ALL_PROCEDURES, DBA_PROCEDURES, USER_OBJECTS. USER_PROCEDURES view contains information about stored procedures that are owned by the current user. The view has several columns that provide information such as the procedure…(Continue Reading)

How to list all tables from a schema of Oracle database To list all tables from a schema of Oracle database you can use tables: USER_TABLES, USER_ALL_TABLES, TABS, ALL_TABLES, DBA_TABLES, USER_OBJECTS. Certain tables require privileges from the database DBA. The most used tables are USER_TABLES and USER_OBJECTS. Examples SELECT * FROM USER_TABLES; SELECT * FROM…(Continue Reading)

Data Dictionary is a part of Oracle database. Data Dictionary is a read-only set of tables that provides information about the database. The data dictionary is structured in tables and views. A data dictionary contains: The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers). Informations…(Continue Reading)

ORA-04091: table is mutating, trigger/function may not see it Oracle PL/SQL error message: ORA-04091: table is mutating, trigger/function may not see it Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the…(Continue Reading)

ORA-06503: PL/SQL: Function returned without value Oracle PL/SQL error message: ORA-06503: PL/SQL: Function returned without value Cause: A call to PL/SQL function completed, but no RETURN statement was executed. Solution: Rewrite PL/SQL function, making sure that it always returns a value of a proper type. Example: create or replace function get_name(p_id number) return varchar2 as…(Continue Reading)