Insert Text Literal and Concatenation in Oracle SQL

In Oracle, it’s possible to insert literal or concatenate 2 or more charater strings or SQL result output together. This manipulation allows you to manipulate the format of data returned by SQL query.

Two solid vertical bar || operator is used to concatenate 2 or more strings. Beside, Oracle also provides the CONCAT character function as an alternative to the vertical bar operator just in case there is situation where it is difficult or impossible to control translation performed by operating system or network utilities. This function should be used in applications that will be moved between environments with differing character sets.

Oracle will automatically casts values into types which can be concatenated. As Oracle interprets NULL as the empty (zero-length) character string, it doesn’t return NULL if an operand is NULL, meaning concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.

Concatenating two strings results in another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the strings’ datatypes.

For literal insertion, put the strings between the single quote ‘ in the SQL statement.

Example and Usage:

SELECT ‘Name is ‘ || name FROM table;

Name is whatever_name

SELECT number || ‘ – ‘ || description FROM table ORDER BY number

1 – description 1
2 – description 2

Advance Usage:

It’s possible to use Concatenation and Literal Insertion to generate a set of SQL query language automatically, especially when need to perform same operation to lots of tables, i.e dropping a lot of tables. To do this, format the SQL data query language to output the query result in valid SQL format, and spool the SQL query results to a file. Then execute the file that contains SQL statements.

Example:

SELECT ‘DROP TYPE ‘ || type_name || ‘;’

will generates:

DROP TYPE type_name

that can be run at SQL*Plus by calling the file with @filename. All types that been selected from first SQL statements will be dropped from the database.

Related Articles

• How to Escape Characters in Oracle PL/SQL Queries
• Use {Literal} Smarty Tag to Add Google Analytics Tracking Code to BlogSome Hosted Blogs
• Oracle EXP-00091 Error When Export Database
• Insert and Fill Random Filler Text and Lorem Ipsum Into Office Word, Execel and PowerPoint 2007
• Change Oracle Database User Password
• Check Oracle Version
• ORA-01502 Oracle Index in Unusable State
• IMP-00016 Required Character Set Conversion Not Supported Error when Import to Oracle Database
• Manual and Clean Uninstall Oracle for Windows
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error

This entry was posted on Saturday, January 14th, 2006 at 11:46 am and is filed under Databases, Programming Languages. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.