Dev Corner

Problem
You need to find information about all invalid database objects in an Oracle Database. Read the rest of this entry »

You can use the SQL implicit cursor to retrieve the number of rows, affected by SQL query: SQL%ROWCOUNT. Read the rest of this entry »

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 »

Problem: You have a string encoded in UTF-8 and you need to convert it to two byte UTF encoded string. For example, you might want to display convert the string to CP1251 (Windows-1251) encoding using standard encoding tables. Read the rest of this entry »