SQL Oracle 00932. 00000 - “不一致的数据类型：预期 %s 得到 %s”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30153645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 00932. 00000 - "inconsistent datatypes: expected %s got %s"
提问by thekucays
well i'm still new in oracle, i'm trying to query a table with a subquery..it looks like this
好吧，我还是 oracle 的新手，我正在尝试使用子查询查询表..它看起来像这样
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?
任何的想法？
采纳答案by Orcun Yucel
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:
还有一些在 Oracle 中使用变量类型的技巧：
1. VARCHAR is obsolete, use VARCHAR2 instead.
2. Instead of using INTEGER type, use NUMBER.
回答by thekucays
i already figured it out.. thanks for user4884704 (i've marked his answer already)
我已经想通了..感谢用户4884704（我已经标记了他的答案）
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'));
回答by dnoeth
Your query returns 2 columns, but there's only one column defined in INTO.
您的查询返回 2 列，但 INTO 中仅定义了 1 列。
