How to use Oracle “OPatch Utility” to find out information about Oracle Home

Oracle has a useful tool to list information about its products: OPatch Utility. You can apply patches or get information about the installed updates by using this command line tool.

One of the commands that come with this tool is lsinventory. It lists the patches applied to Oracle Home.

Steps

  1. Search for “OPatch” in Windows
    There might be several folders with this name if you have installed Oracle Data Access Components (ODAC) more than once
  2. Open command prompt in the actively used “OPatch” fodler
  3. Run the command below
opatch lsinventory

References

Solved: “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”

If you store a large text data as a CLOB data type in your Oracle database, you may come across to the error messages below when you query your table:

ORA-06502: numeric or value error: character string buffer too small
ORA-01489: result of string concatenation is too long

Background

CLOB data type is used to store large text data (up to 2 GB in MySQL and up to 128 TB in Oracle). Similarly, BLOB data type is used to store large binary data (videos, images, documents).

If you want to retrieve data from a CLOB field, you should convert data to a string data type. VARCHAR2 is commonly used string data type. However, it has a limitation of 4000 characters.

In my case, I use the following query to retrieve the data. Since I am requesting 5000 characters and VARCHAR2 only supports 4000, I receive the errors above.

select dbms_lob.substr(column1,5000,1)
from DIM_SW_TEST;

Solution

Write the same column name twice in the same SELECT query. So basically you divide the content in two VARCHAR2 fields (total of up to 8000 characters). Make sure to increase the buffer size with the SET BUFFER parameter.

set buffer 10000;
select dbms_lob.substr(column1,4000,1),
dbms_lob.substr(column1,4000,4001)
from DIM_SW_TEST;

How to convert a long timestamp to a short date in Oracle?

Many developers agree on working with dates is more challenging than working with other data types. I recently had to convert some long timestamp strings to short date values. Here are the functions I used:

Original value: 09-MAY-16 10.11.50.596605 AM -04:00
Target value: 09/05/2016

Code:

TO_CHAR(TO_TIMESTAMP_TZ(V_DATE_TIME, 'DD-MON-RR HH:MI:SS.FF AM TZH:TZM'), 'DD/MM/YYYY')

Original value: 09-MAY-16 10.11.50.596605 AM -04:00
Target value: 09/05/2016 10:11 AM

Code:

TO_CHAR(TO_TIMESTAMP_TZ(V_DATE_TIME, 'DD-MON-RR HH:MI:SS.FF AM TZH:TZM'), 'DD/MM/YYYY HH:MI AM')

As you see in the examples above, I first convert the string value in V_DATE_TIME to timestamp data type by using TO_TIMESTAMP_TZ. Then I convert timestamp data type back to string in the new format by using TO_CHAR function.