Oracle 00932. 00000 - "inconsistent datatypes: expected %s got %s"

Question

well i'm still new in oracle, i'm trying to query a table with a subquery..it looks like this

select id_user, count(*) as jumlah from (select * from users where username = 'usr' and pass = 'pwd' and company_id = 'PAN' and status = 1) 
group by id_user;

the above code works. but when i try to put it inside a stored procedure i got some errors like this

here's the stored procedure

create type login_obj is object(jumlah integer);
create type login_table is table of login_obj;
create or replace function startLogin(u varchar, p varchar, cid varchar)
return login_table
is
  tabel login_table := login_table();
  the_count integer;
  the_sql varchar(200);
begin
  the_sql := 'select id_user, count(*) as jumlah from (select * from users where username = ''' || u || ''' and pass = ''' || p || ''' and company_id = ''' || cid || ''' and status = 1) GROUP BY id_user';
  execute immediate the_sql into the_count;

  if the_count IS NOT NULL
  then
  begin
    tabel.extend;
    tabel(1) := login_obj(the_count);
  end;
  end if;
  return tabel;
end;

then executing it by

select * from table (startLogin('usr','pwd','PAN'));

and here are the errors

SQL Error: ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "LUKI.STARTLOGIN", line 14
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

any idea?

You're selecting two columns and trying to stuff the result into a single variable. — Mat, May 10 '15 at 16:19
@Mat . . . It is worse than that, because the query might return more than one row. — Gordon Linoff, May 10 '15 at 16:22
Hum. And the subselect and dynamic SQL are useless apparently too. — Mat, May 10 '15 at 16:25

score 3 · Accepted Answer · answered May 10 '15 at 16:38

3

Add one more variable below the line

the_sql varchar(200);

as

yid_user users.id_user%TYPE;

and change your execute immediate as

execute immediate the_sql into yid_user, the_count;

Also some tips for using variable types in Oracle:

1. VARCHAR is obsolete, use VARCHAR2 instead.
2. Instead of using INTEGER type, use NUMBER.

answered May 10 '15 at 16:38

Orcun Yucel

94
4

thanks a lot dude! it really works. i've put the working code below – thekucays May 10 '15 at 16:46
@thekucays, you should use variable binding instead of embedding the variables into the sql statement aswell. Check USING keyword. – Orcun Yucel May 10 '15 at 16:51

score 2 · Answer 2 · answered May 10 '15 at 16:20

2

Your query returns 2 columns, but there's only one column defined in INTO.

answered May 10 '15 at 16:20

dnoeth

59,503
4
39
56

Remove the `id_user`, your query should return only a single row anyway. – dnoeth May 10 '15 at 16:33
well i need the "id_user" column so i think i wouldn't remove it.. any other ideas? :) – thekucays May 10 '15 at 16:37
how if i put another variable in "INTO"? how could i achieve that? – thekucays May 10 '15 at 16:39
You found the solution, I assumed you know it already, as this is very basic SQL knowledge :-) – dnoeth May 10 '15 at 17:02
yes, that's my bad.. thanks for your help anyway :-) – thekucays May 10 '15 at 17:48

score 1 · Answer 3 · answered May 10 '15 at 16:46

i already figured it out.. thanks for user4884704 (i've marked his answer already)

so here's the working code..put the results in different variable

create type login_obj is object(id_user integer, jumlah integer);
create type login_table is table of login_obj;
create or replace function startLogin(u varchar, p varchar, cid varchar)
return login_table
is
  tabel login_table := login_table();
  id_user integer;
  the_count integer;
  the_sql varchar(200);
begin
  the_sql := 'select id_user, count(*) as jumlah from (select * from users where username = ''' || u || ''' and pass = ''' || p || ''' and company_id = ''' || cid || ''' and status = 1) GROUP BY id_user';
  execute immediate the_sql into id_user, the_count;

  if the_count IS NOT NULL
  then
  begin
    tabel.extend;
    tabel(1) := login_obj(id_user, the_count);
  end;
  end if;
  return tabel;
end;

then i execute it as

select * from table (startLogin('usr','pwd','PAN'));

Oracle 00932. 00000 - "inconsistent datatypes: expected %s got %s"

3 Answers3