How to query long type in oracle

How to query long type in oracle: 1. TO_LOB function, use "SELECT TO_LOB()" syntax to convert the LONG type column in the table to CLOB type and return it as the query result; 2. SUBSTR function , using "SELECT SUBSTR()" will return the 250 characters starting from the 100th character in the LONG type column in the table, which are returned as VARCHAR type.

#The operating environment of this article: Windows 10 system, Oracle version 19c, dell g3 computer.

In Oracle database, the LONG type is a variable-length character (VARCHAR) storage type. It can store variable-length strings, but since it has been gradually replaced by the CLOB type and was declared obsolete in the 12c version of Oracle, its use should be avoided if possible. If you are still using the LONG type, here is how to query the LONG type:

1. Use the TO_LOB function:

In Oracle, if you need to query the LONG type To operate, you can use the TO_LOB function to convert it to LOB type. For example, the following SQL statement will convert the LONG type column (long_column) in the table (mytable) to the CLOB type and return it as the query result:

SELECT TO_LOB(long_column) FROM mytable;

2. Use the SUBSTR function:

Another way to query the LONG type is to use the SUBSTR function. The SUBSTR function can query part of the content of the LONG type and return it as a VARCHAR type. For example, the following SQL statement will return the 250 characters starting from the 100th character in the LONG type column (long_column) in the table (mytable):

SELECT SUBSTR(long_column, 100, 250) FROM mytable;

Please note that due to the variable length of the LONG type, the query The result will always contain a fixed length of spaces. Therefore, if you need the result to not contain spaces, use the TRIM function.

Summary

In general, although the LONG type was a common data type in early versions of Oracle, it has now been replaced by the CLOB type. supersedes, so it is recommended to avoid using it if possible. If you do need to use LONG data types in database tables, remember how to use the TO_LOB function and SUBSTR function to query them

The above is the detailed content of How to query long type in oracle. For more information, please follow other related articles on the PHP Chinese website!