I have seen multiple custom solutions and packaged applications store their SQL outside of stored procedures. I am focusing this article on custom enterprise solutions, which I feel is one of the fastest growing areas in development. Some people who choose to store SQL outside of their database have touted database independence/abstraction, others easier maintenance, and I’m sure many other “excuses” have been communicated. In my opinion, if you have 1) a custom enterprise application and 2) an Oracle database(s) as the backend, all of your SQL — and associated DB logic — should be written, stored, maintained, etc. within … Read More
SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples
The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text.
LIKE provides a utility to conduct simple text pattern matching without the full power regular expressions, and hence, is simpler than using regular expressions.
Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)
This article provides common examples of TO_CHAR using dates.
Oracle Rename Column – alter table, rename column Example SQL Syntax
Following is simple example syntax for renaming a column to an Oracle table using “alter table” SQL:
1 2 | ALTER TABLE [my_table_name] RENAME COLUMN [current_column_name] TO [new_column_name]; |
A simple example would be:
1 2 | ALTER TABLE employee RENAME COLUMN f_name TO first_name; |
Like renaming an Oracle table, you should be aware of any dependencies in code, etc. that reference a table column by name.
Oracle SUBSTR with INTSR Function – SQL Syntax Examples
Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are some examples uses that will exemplify how to take advantage of their combined power.
Top Level Domain (TLD) From An Email Address
1 2 3 4 5 6 7 8 | SELECT substr ( '[email protected]', instr('[email protected]','@',1)+1 ) FROM dual --returns: mandsconsulting.com |
Base URL
…
Oracle TO_DATE Function – SQL Syntax Examples
The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle DATETIME value.
Below shows the TO_DATE function along with the arguments it takes:
1 | to_date(string) |
1 | to_date(string, date_format) |
1 | to_date(string, date_format, options) |
The Oracle TO_DATE function returns a DATETIME representation of the string input using the date_format input.
Oracle INSTR Function – SQL Syntax Examples
The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming languages.
Below shows the INSTR function along with the arguments it takes:
1 | instr(string, SUBSTRING) |
1 | instr(string, SUBSTRING, start_position) |
1 | instr(string, SUBSTRING, start_position, occurrence) |
The Oracle INSTR function returns the position (an integer) within string of the first character in substring that was found while using the corresponding start_position and occurrence.
Oracle Rename Table – Example Syntax and Dependencies
Renaming a table in Oracle is simple. Following is the generic Oracle table rename syntax: 1 ALTER TABLE current_table_name RENAME TO new_table_name;alter table current_table_name rename to new_table_name; In practice, you might realize you left off a prefix that you are using as a naming standard. Changing the table name can be accomplished with the following example syntax: 1 ALTER TABLE employee RENAME TO dw_employee;alter table employee rename to dw_employee; If the table is already in use, I recommend that you review custom code, Oracle packages, stored procedures, and any other references to the table. You can use tools like TOAD … Read More