procedure GetBOMMaterial(routingno in edm_routing_head.routing_no%type,ret_cursor out ds_cursor) as tempParentCode varchar2(50); begin select Parent_Code into tempParentCode from edm_bom where child_code=routingno and rownum<2;
open ret_cursor for select distinct child_code,child_name, sum(eval(substr(sys_connect_by_path(child_qty,'*'),2))) as Amount from edm_bom where IsParent(child_code)='N' group by child_code,child_name start with child_code = routingno and Parent_code = tempParentCode connect by Parent_code= prior child_code;
end GetBOMMaterial;
sum(eval(substr(sys_connect_by_path(child_qty,'*'),2))) as Amount中 eval是我自己做的一个函数用来计算 类似于 1*2*4这样的表达式。系统无法认出 sys_connect_by_path!!如果我把参数替换掉直接在一个空白的SQL窗口运行时没有问题的
在oracle9.0.1中出现了这个问题,我们最终的解决方法是将使用sys_connect_by_path的SQL用“动态SQL” 来代替执行,成功!你可以试一试!!上面的可以改成: open ret_cursor for 'select distinct child_code,child_name, sum(eval(substr(sys_connect_by_path(child_qty,''*''),2))) as Amount from edm_bom where IsParent(child_code)='N' group by child_code,child_name start with child_code = routingno and Parent_code = tempParentCode connect by Parent_code= prior child_code ';
tempParentCode varchar2(50);
begin
select Parent_Code into tempParentCode from edm_bom where child_code=routingno and rownum<2;
open ret_cursor for
select distinct child_code,child_name,
sum(eval(substr(sys_connect_by_path(child_qty,'*'),2))) as Amount
from edm_bom where IsParent(child_code)='N' group by child_code,child_name
start with child_code = routingno and Parent_code = tempParentCode
connect by Parent_code= prior child_code;
end GetBOMMaterial;
eval是我自己做的一个函数用来计算 类似于 1*2*4这样的表达式。系统无法认出
sys_connect_by_path!!如果我把参数替换掉直接在一个空白的SQL窗口运行时没有问题的
如果对eval无权限,,系统会认为eval是表的一列
open ret_cursor for
'select distinct child_code,child_name,
sum(eval(substr(sys_connect_by_path(child_qty,''*''),2))) as Amount
from edm_bom where IsParent(child_code)='N' group by child_code,child_name
start with child_code = routingno and Parent_code = tempParentCode
connect by Parent_code= prior child_code ';