Applications with SQL Maintained Outside of Stored Procedures

AshokTechnical TipsLeave a Comment

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

Oracle Rename Column – alter table, rename column Example SQL Syntax

AshokTechnical Tips7 Comments

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

AshokTechnical Tips5 Comments

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
(
  'xyz@mandsconsulting.com', 
  instr('xyz@mandsconsulting.com','@',1)+1
) 
FROM dual
--returns: mandsconsulting.com

Base URL

Oracle TO_DATE Function – SQL Syntax Examples

AshokTechnical Tips2 Comments

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

AshokTechnical Tips8 Comments

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

AshokTechnical TipsLeave a Comment

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