Select LPAD Through OCI Gives Wrong Length Result (Doc ID 1401584.1)

Last updated on OCTOBER 07, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

After upgrading the current production environment from Oracle 9i to Oracle 11.2.0.2 or 11.2.0.3 a SQL SELECT returns a different length when using the LPAD function within an OCI 7 application.

Here is the SQL being executed using OCI: "select lpad('abcd',6,'z') from dual"

Against an Oracle 9i environment the result is a length of 6.
Against an Oracle 11g 11.2.0.2 or 11.2.0.3 environment the result is a length of 4000.

Changes

Cause

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING. However, for applications with many similar statements, setting CURSOR_SHARING can significantly improve cursor sharing, resulting in reduced memory usage, faster parses, and reduced latch contention. Consider this approach when statements in the shared pool differ only in the values of literals, and when response time is poor because of a very high number of library cache misses.

If stored outlines were generated with CURSOR_SHARING set to EXACT, then the database does not use stored outlines generated with literals. To avoid this problem, generate outlines with CURSOR_SHARING set to FORCE or SIMILAR and use the CREATE_STORED_OUTLINES parameter.

Setting CURSOR_SHARING to FORCE or SIMILAR has the following drawbacks:

The database must perform extra work during the soft parse to find a similar statement in the shared pool.

There is an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals in a SELECT statement. However, the actual length of the data returned does not change.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.