Listing tables, views, procedures, indices, packages, sequences, synonyms, triggers in Oracle

If you want to list tables in MySQL, you simply type the following command:

show tables

Oracle does not provide such a short cut. You have to type the following command:

select * from user_objects where object_type = 'TABLE'

The word TABLE must be uppercase. This would lisl all tables in the database. The same query could be modified slightly to list functions, procedures, sequences, view and more.

List functions

select * from user_objects where object_type = 'TABLE'

List views

Oracle NVL2 function

NVL2 function extends the funcitonality of NVL function. NVL function prints the value of the variable unless a null value is encountered; in which case a value is substituted for the null. In addition to this, NVL2 also allows you to substitute a value if the value of the variable is not null. Syntax:

NVL2(variable, not_null_substitute, null_substitute)


Concatenation is Oracle

Peculiarly, Oracle uses the double pipe ( || ) as a concatenation operator. Most programming languages use || for the or operator. Example:

insert into mytable (id, name) values (12, 'Mr. ' || lastname);

The field lastname is concatenated to the string Mr.

Oracle Packages

A package is an encapsulated collection of related PL/SQL types, objects, procedures, and other program objects. Packages have a package specification and a package body. Package specification declares constants, variables, types, cursors, exceptions and procedures. Package definition defines cursors and procedures.

Packages provide the advantages of modularity, information hiding, and encapsulation.

Package commands To create a package

CREATE PACKAGE package_name

To create package body