ORA-00932: Inconsistent data types: should be-, but get-

The error occurs because a read cursor reads less than one or more columns during the execution of a stored procedure operation.

However, this does not seem likely to happen because the compile phase will be an error.

The following shows the occurrence of the error and how to resolve it.

1. Create test table users.

CREATE TABLE "USERS2" (

"USERNAME" VARCHAR2 (BYTE),

"Userpass" VARCHAR2 (BYTE),

"COUNTER" Number (11,0)

) ;

2. Create a test process Proc_demo, intentionally less read a column.

Create or replace

PROCEDURE Proc_demo as

uname varchar2 (20);

UPass varchar2 (20);

Counter number;

BEGIN

DECLARE CURSOR Cur_users is

SELECT * from USERS;

BEGIN

OPEN cur_users;

LOOP

FETCH cur_users into Uname,upass;

EXIT when Cur_users%notfound;

Dbms_output.put_line (' uname: ' | | uname);

Dbms_output.put_line (' UPass: ' | | UPass);

Dbms_output.put_line (' Counter: ' | | Counter);

End LOOP;

Close cur_users;

End;

End Proc_demo;

Compiling the above procedure, an error occurred:

Error (12,5): Pl/sql:sql Statement ignored

Error (12,5): PLS-00394: in the FETCH the statement into An error occurred in the number of values in the list

Even compiling is not a problem, let alone the following error when executing: ORA-00932: Data type inconsistency: should be-, but obtained-.

The above is a test performed in SQL developer. The Proc_demo declares the cursor cur_users and reads, if one or more of the columns are read less than the compile pass. If the cursor cur_users is not displayed in the procedure but is obtained by invoking the return cursor type data, it may be possible to read less of one or some of the column compilations.

3. Create a procedure for returning cursors and base type data.

The process is encapsulated in a package, and the package declares the following:

Create or replace PACKAGE package_test as

TYPE Cursor_type is REF CURSOR;

PROCEDURE Proc_test (

COUNTER out number,

Cur_users out Cursor_type

);

End Package_test;

Create the package body as follows:

Create or replace PACKAGE body package_test

Is

PROCEDURE Proc_test (

COUNTER out number,

Cur_users out Cursor_type

)

Is

Select_sql VARCHAR2 (200);

BEGIN

Select_sql: = ' SELECT COUNT (*) from USERS ';

EXECUTE IMMEDIATE select_sql into COUNTER;

Select_sql: = ' SELECT username,userpass from USERS ';

OPEN cur_users for Select_sql;

End Proc_test;

End Package_test;

4. Create a test process proc_client.

Create or replace

PROCEDURE Proc_client as

TYPE Cursor_type is REF CURSOR;

Cur_users Cursor_type;

COUNTER number;

uname varchar2 (20);

UPass varchar2 (20);

BEGIN

--Call the procedure in Package_test proc_test

Package_test. Proc_test (counter,cur_users);

--Print The result information

Dbms_output. Put_Line (' COUNTER: ' | | COUNTER);

Dbms_output. Put_Line (' The data in the cursor returned: ');

LOOP

FETCH cur_users into uname;

EXIT when Cur_users%notfound;

Dbms_output.put_line (' uname: ' | | uname);

--dbms_output.put_line (' UPass: ' | | UPass);

End LOOP;

Close cur_users;

End Proc_client;

The following error occurred while running proc_client:

ORA-00932: inconsistent data types: should be-, but they get--

ORA-06512: in "DEMO." Proc_client ", Line

ORA-06512: in line 2

The reason is package_test. Proc_test returns a cursor cur_users is a table users two columns

Username and userpass result sets, but only username were read when the cursor was read in proc_client.

Workaround:

Modify the statement that reads the cursor as follows:

FETCH cur_users into Uname,userpass;

EXIT when Cur_users%notfound;

Dbms_output.put_line (' uname: ' | | uname);

Dbms_output.put_line (' UPass: ' | | UPass);

In conclusion, this is a very superficial error, just because the cursor is declaring and generating the dataset from another process, so it escapes a lot of compilation errors until the runtime is a baffling error.