Dev Corner

You declare PL/SQL variables, constants and types in declare block. The syntax is Read the rest of this entry »

Oracle Database 11g introduced new feature – virtual column. Instead storing data, the virtual column is calculated based on an expression stored in data dictionary. Let’s assume you have a table that stores customer information. The customer names are stored in two separate columns – first_name and last_name. The database queries often need to return the customer’s full name. Instead of storing the full name into the database or having to type it each time, you can define a new virtual column that is calculated, based on an expression: first_name || ' ' || last_name. Read the rest of this entry »

You can add special characters to an Oracle SQL query by using the CHR function. For example, to add a Tab character, you can use CHR(9). Where 9 is the ASCII code for the Tab character. Read the rest of this entry »

This example illustrates how you can create and use dynamic cursor in Oracle PL/SQL. The example is pretty simple, but I hope you can get the idea and apply it to more complicated cases. Read the rest of this entry »

You are a new database administrator. You are assigned a task to find all obfuscated PL/SQL procedures in an Oracle Database. Here is a simple recipe that solves the problem. After running the SELECT SQL statement you will get a list of obfuscated objects. Read the rest of this entry »

If you need to remove duplicate rows from an Oracle Database Table, you can use different approaches. For example, you can use the DELETE command with a criteria that selects only duplicates. Usually it is faster to create a new table with distinct rows and replace the old table with the new one. Read the rest of this entry »

With this recipe you can count the number of rows for ALL tables in current Oracle Database schema. Read the rest of this entry »

Here is a simple recipe. The following SQL finds the top ten SQL statements with the greatest elapsed time. Read the rest of this entry »

When Oracle executes PL/SQL program code it is interpreted. When compiled to native code, Oracle creates system dependent code. Native code is not interpreted by Oracle, but executed directly by the system. Read the rest of this entry »

You can use Database Link (dblink) to execute SQL against external database as if it was the database you are logged in to. For example, you might need to transfer sales information from a company branch located in Miami. Read the rest of this entry »