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
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.
This article provides common examples of TO_CHAR using dates.
Following is simple example syntax for renaming a column to an Oracle table using “alter table” SQL:
ALTER TABLE [my_table_name] RENAME COLUMN [current_column_name] TO [new_column_name];
A simple example would be:
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.
Top Level Domain (TLD) From An Email Address
1 2 3 4 5 6 7 8
SELECT substr ( 'firstname.lastname@example.org', instr('email@example.com','@',1)+1 ) FROM dual --returns: mandsconsulting.com
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:
to_date(string, date_format, options)
TO_DATE function returns a DATETIME representation of the
string input using the
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:
instr(string, SUBSTRING, start_position)
instr(string, SUBSTRING, start_position, occurrence)
INSTR function returns the position (an integer) within
string of the first character in
substring that was found while using the corresponding
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