select count(*) into vCount from tab t, COL c
where t.tname = c.TNAME and t.tname = upper(piTablename) and c.CNAME = upper(piColName);上面的这条语句,我直接在SQL Window里执行的话,vCount的值就是1,
但是我如果放到存储过程里执行的话,vCount的值就是0了
这是怎么回事啊?
我已经赋权限了呀
GRANT SELECT ANY TABLE TO HD40 WITH ADMIN OPTION;
where t.tname = c.TNAME and t.tname = upper(piTablename) and c.CNAME = upper(piColName);上面的这条语句,我直接在SQL Window里执行的话,vCount的值就是1,
但是我如果放到存储过程里执行的话,vCount的值就是0了
这是怎么回事啊?
我已经赋权限了呀
GRANT SELECT ANY TABLE TO HD40 WITH ADMIN OPTION;
贴SQL脚本出来看看
(
piTableName in varchar2, --表名
piColName in varchar2, --列名
piTypeStr in varchar2 --字段类型
) is
vCount int;
begin
select count(*) into vCount from tab t, COL c
where t.tname = c.TNAME and t.tname = upper(piTablename) and c.CNAME = upper(piColName);
if vCount <= 0 then
execute immediate 'alter table ' || piTableName || ' disable all triggers';
execute immediate 'alter table ' || piTableName || ' add ' || piColName || ' ' || piTypeStr;
execute immediate 'alter table ' || piTableName || ' enable all triggers';
end if;
end;
于是就会报错
改为
vCount number;
试试,oracle里到底支不支持int,
以前还真没注意过这个问题
ORA-06512: 在"HD40.HDADDCOLUMN", line 13
ORA-06512: 在line 1
where t.tname = c.TNAME and upper(t.tname) = upper(piTablename) and upper(c.CNAME) = upper(piColName);
Username Privilege Admin Option
40 ALTER USER YES
40 CREATE PUBLIC DATABASE LINK NO
40 CREATE SEQUENCE NO
40 SELECT ANY TABLE YES
40 UNLIMITED TABLESPACE YES User - Granted Roles
Username Granted Role Admin Option Default Role Os Granted
40 DBA YES YES NO
40 ROLE_HDAPP YES YES NO
40 ROLE_HDQRY YES YES NO