Category Archives: Oracle SQL

Frequently used Oracle SQL Developer Short Cut Keys

Short Cut Description Ctrl+Space Invokes code insight on demand Ctrl+Up Replaces worksheet with Previous SQL from SQL History Ctrl+Down Replaces worksheet with Next SQL from SQL History Ctrl+Shift+Up Appends current worksheet with Previous SQL from SQL History Ctrl+Shift+Down Appends current worksheet with Next SQL from SQL History Dhift+F4 opens a Describe window for current object… Read More »

SQL Query to Convert Comma separated String values to Rows

/*********************************************************************** *PURPOSE: SQL Query to Convert Comma separated String values to Rows * *AUTHOR: Shailender Thallam * ***********************************************************************/ WITH DATA AS ( SELECT ‘word1, word2, word3, word4, word5, word6’ str FROM dual ) SELECT TRIM(regexp_substr(str, ‘[^,]+’, 1, LEVEL)) str FROM DATA CONNECT BY instr(str, ‘,’, 1, LEVEL – 1) > 0; ——————- –Real time Example… Read More »

Oracle SQL Regular Expression to find if a Value is String or Number

We can use oracle standard REGEXP_LIKE command to apply a regular expression on value. Below is sample query to find if a value is String or Number: SELECT CASE WHEN REGEXP_LIKE(’111′, ‘^-?[[:digit:],.]*$’) THEN ‘111’ || ‘ is a NUMBER’ ELSE ‘111’ || ‘ is a STRING’ END "O/P: STRING_OR_NUMBER" FROM dual;SELECT CASE WHEN REGEXP_LIKE(‘111’, ‘^-?[[:digit:],.]*$’)… Read More »

Date Range in Oracle – Minimum Possible Date and Maximum Possible Date

Like any other data type in oracle, even DATE data type has a length restriction. A Date data type can hold a maximum data of 7 bytes. You can refer to this article from Oracle corporation on Internal Oracle Database Data Types and their limits which shows the limit restrictions of different data types. Below… Read More »

DB Link in Oracle SQL

A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema. The following link types are supported: Private database link – belongs to a specific schema of a database. Only the owner of… Read More »

Date Format validation on Standard Free Text Fields

I had a requirement to validate date entered in a free text field (standard field on a standard form). I have solved it by writing a function to validate the date format entered and called the function from forms personalization. Here is a function to validate whether date entered is in valid format (‘MM/DD/YYYY’) or… Read More »

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use regularly and how… Read More »