ORA-00932 Using a Dynamic SELECT Statement on 10G (Doc ID 566818.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.1 to 10.2.0.3 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 26-Jan-2010***

Symptoms

On Oracle 10G, when executing a native dynamic SELECT statement from PL/SQL, if less
variables are specified in the define list than columns specified by the SELECT, an ORA-932 error is raised. The same native dynamic SELECT statement is working fine on lower versions.

 The following code was working fine on releases < 10G. On 10G and 11G it fails with:

ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 7

DECLARE
n NUMBER;
sql_stmt VARCHAR2(255);
type DynaCur IS REF CURSOR;
c DynaCur;
BEGIN
select 'SELECT 1,b.* from (select rownum nrow,a.* from DBA_USERS a where
rownum=1) b' into sql_stmt from dual;
OPEN c FOR sql_stmt;
FETCH c into n;
dbms_output.put_line(n);
CLOSE c;
END;
/

Changes

The problem occurs after upgrade to 10G or 11G from lower versions.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms