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

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.

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

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_tableis  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'));