Oracle ORA-00972:Identification too long

Question

create or replace function F_CHECK_SPECIALMAT_ORDER(AN_ORDER_ID  in NUMBER,AS_ORDER_TYPE in varchar2) return number is /* 
   参数：AN_ORDER_ID  in NUMBER
        AS_ORDER_TYPE in varchar2
  返回：number
  */
  
  ls_table_name varchar2(30);
  ls_sql_string varchar2(4000);
  ln_count      number;
begin
   if SUBSTR(trim(AS_ORDER_TYPE),1,1) = '0'    then
     ls_table_name := 'mat_purchase_plan_detail';--计划
   ELSIF SUBSTR(trim(AS_ORDER_TYPE),1,2) = '11'then
     ls_table_name := 'mat_quotation_request_detail'; --询  
   ELSIF (SUBSTR(trim(AS_ORDER_TYPE),1,2) = '12' or SUBSTR(trim(AS_ORDER_TYPE),1,2) = '13') then
     ls_table_name := 'mat_supplier_confirm_detail'; --确
    ELSIF SUBSTR(trim(AS_ORDER_TYPE),1,1) = '2'then
     ls_table_name := 'mat_purchase_contract_detail';--采
    ELSIF SUBSTR(trim(AS_ORDER_TYPE),1,1) = '3'then
     ls_table_name := 'mat_purchase_in_store_detail';--采
    ELSIF SUBSTR(trim(AS_ORDER_TYPE),1,1) = '4'then
     ls_table_name := 'mat_use_out_store_detail';--领
    else
    return 1; 
   end if;
  
 ls_sql_string :=  'select count(*) 
                    from equ_sys_info_detail 
                    where info_type_code = '||'420'||'
                          and 
                          exists(select 1 from'||ls_table_name||
                                           'where to_char(order_id) ='||to_char(AN_ORDER_ID)||' 
                                           and 
                                           MATERIALCODE LIKE info_detail_code||'||'%'||')
                                           and 
                                           INFO_DETAIL_STATE = '||'N'||'
                                           and exists ( select 1 from v_message_unit_emp 
                                                                      where unit_type = '||'3'||' 
                                                                      and 
                                                                      emp_code = f_get_cur_user_id() 
                                                                      and 
                                                                      (info_detail_desc like '||'%'||'||unit_code||'||'%'||'))';
   execute immediate ls_sql_string into ln_count;
   if ln_count>0 then
     return 1;
  else
    return 0 ;
  end if;
end F_CHECK_SPECIALMAT_ORDER;
此数据库函数报错。

Answer 1

那段sql有问题，你仔细检查下，'的地方用的不对，或者直接把正常sql贴出来看看

Answer 2

我要在Pb datawindow中调用的。谢谢。

Answer 3

把可以运行的sql语句贴出来，这里没有必要使用动态sql
直接select count(*) into ln_count就好了

Answer 4

select count(*) 
               from equ_sys_info_detail 
                    where info_type_code = '420'
                          and 
                          exists(select 1 from mat_purchase_plan_detail--为表名变量
                                           where to_char(order_id) =to_char(124) 
                                           and 
                                           MATERIALCODE LIKE 'T370'||'%')
                                           and 
                                           INFO_DETAIL_STATE = 'N'
                                           and exists ( select 1 from v_message_unit_emp 
                                                                      where unit_type = '3'
                                                                      and 
                                                                      emp_code = f_get_cur_user_id() 
                                                                      and 
                                                                      (info_detail_desc like '%'||unit_code||'%'));

Answer 5

上面的sql语句运行正确。正确转换为动态sql?

Answer 6

1.不用动态sql，直接select into ，因为你的sql并不需要拼接处理，没有必要
2.用动态sql，如下
'select count(*) 
from   equ_sys_info_detail 
where  info_type_code   = ''420''
and    exists(select 1 from '||ls_table_name||
              'where to_char(order_id)  = to_char(124) 
              and   MATERIALCODE LIKE ''T370%'') 
and   INFO_DETAIL_STATE = ''N''  
and exists ( select 1 from v_message_unit_emp 
             where unit_type = ''3''
             and   emp_code = f_get_cur_user_id() 
             and  (info_detail_desc like ''%''||unit_code||''%''))';

Answer 7

and  (info_detail_desc like ''%''||unit_code||''%'')这句可能会问题，没有测试，换成下面这种形式and instr(info_detail_desc,unit_code) >= 1

Answer 8

ls_sql_string :=  'select count(*) 
                    from equ_sys_info_detail 
                    where info_type_code = '||'420'||' 
                          and 
                          exists(select 1 from'||ls_table_name|| --'from'后应该加个空格
                                          'where to_char(order_id) ='||to_char(AN_ORDER_ID)||' 
                                          and 
                                          MATERIALCODE LIKE info_detail_code||'||'''%'''||') 
                                          and 
                                          INFO_DETAIL_STATE = '||'N'||' 
                                          and exists ( select 1 from v_message_unit_emp 
                                                                      where unit_type = '||'3'||' 
                                                                      and 
                                                                      emp_code = f_get_cur_user_id() 
                                                                      and 
                                                                      (info_detail_desc like '||'''%'''||'||unit_code||'||'''%'''||'))'; 
                                                                      --两处like后面表示得也有问题，改下

Answer 9

还有两处需要修改
我把修改后的给你
你再试试
ls_sql_string :=  'select count(*) 
                    from equ_sys_info_detail 
                    where info_type_code = ''420'' 
                          and 
                          exists(select 1 from '||ls_table_name|| 
                                          'where to_char(order_id) ='||to_char(AN_ORDER_ID)||' 
                                          and 
                                          MATERIALCODE LIKE info_detail_code||'||'''%'''||') 
                                          and 
                                          INFO_DETAIL_STATE = ''N'' 
                                          and exists ( select 1 from v_message_unit_emp 
                                                                      where unit_type = ''3'' 
                                                                      and 
                                                                      emp_code = f_get_cur_user_id() 
                                                                      and 
                                                                      (info_detail_desc like '||'''%'''||'||unit_code||'||'''%'''||'))';

Answer 10

还是漏了一处...
ls_sql_string :=  'select count(*) 
                    from equ_sys_info_detail 
                    where info_type_code = ''420'' 
                          and 
                          exists(select 1 from '||ls_table_name|| 
                                          'where to_char(order_id) ='''||to_char(AN_ORDER_ID)||''' 
                                          and 
                                          MATERIALCODE LIKE info_detail_code||'||'''%'''||') 
                                          and 
                                          INFO_DETAIL_STATE = ''N'' 
                                          and exists ( select 1 from v_message_unit_emp 
                                                                      where unit_type = ''3'' 
                                                                      and 
                                                                      emp_code = f_get_cur_user_id() 
                                                                      and 
                                                                      (info_detail_desc like '||'''%'''||'||unit_code||'||'''%'''||'))';

Answer 11

 ls_sql_string :=  'select count(''1'') 
                      from equ_sys_info_detail 
                      where info_type_code = ''420''
                            and 
                            exists ( select ''1'' from ' ||ls_table_name||'
                                             where to_char(order_id) ='||to_char(AN_ORDER_ID)||' 
                                             and 
                                             MATERIALCODE LIKE info_detail_code ||''%'')
                                             and 
                                             INFO_DETAIL_STATE = ''N''
                                             and exists ( select ''1'' from v_message_unit_emp 
                                                                        where unit_type = ''3'' 
                                                                        and 
                                                                        emp_code = f_get_cur_user_id() 
                                                                        and 
                                                                        (info_detail_desc like ''%''|| unit_code ||''%''))';
调试通过了。谢谢。

Answer 12

 ls_sql_string :=  'select count(''1'') 
                      from equ_sys_info_detail 
                      where info_type_code = ''420''
                            and 
                            exists ( select ''1'' from ' ||ls_table_name||'
                                             where to_char(order_id) ='||to_char(AN_ORDER_ID)||' 
                                             and 
                                             MATERIALCODE LIKE info_detail_code ||''%'')
                                             and 
                                             INFO_DETAIL_STATE = ''N''
                                             and exists ( select ''1'' from v_message_unit_emp 
                                                                        where unit_type = ''3'' 
                                                                        and 
                                                                        emp_code = f_get_cur_user_id() 
                                                                        and 
                                                                        (info_detail_desc like ''%''|| unit_code ||''%''))';

Answer 13

我执行成功了呀，检查下设置吧
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
Connected as XXXSQL> create or replace function F_CHECK_SPECIALMAT_ORDER(AN_ORDER_ID  in NUMBER,AS_ORDER_TYPE in varchar2)
  2  return number
  3  is
  4     ls_table_name varchar2(30);
  5     ls_sql_string varchar2(4000);
  6     ln_count      number;
  7  
  8  begin
  9     if substr(trim(as_order_type),1,1) = '0'    then
 10        ls_table_name := 'mat_purchase_plan_detail';
 11     elsif substr(trim(as_order_type),1,2) = '11'then
 12        ls_table_name := 'mat_quotation_request_detail';
 13     elsif (substr(trim(as_order_type),1,2) = '12' or substr(trim(as_order_type),1,2) = '13') then
 14        ls_table_name := 'mat_supplier_confirm_detail';
 15     elsif substr(trim(as_order_type),1,1) = '2'then
 16        ls_table_name := 'mat_purchase_contract_detail';
 17     elsif substr(trim(as_order_type),1,1) = '3'then
 18        ls_table_name := 'mat_purchase_in_store_detail';
 19     elsif substr(trim(as_order_type),1,1) = '4'then
 20        ls_table_name := 'mat_use_out_store_detail';
 21     else
 22      return 1;
 23     end if;
 24  
 25     ls_sql_string := 'select count(*)
 26                       from   equ_sys_info_detail
 27                       where  info_type_code   = ''420''
 28                       and    exists(select 1 from '||ls_table_name||
 29                             'where to_char(order_id)  = to_char(124)
 30                              and   MATERIALCODE LIKE ''T370%'')
 31                              and   INFO_DETAIL_STATE = ''N''
 32                       and    exists ( select 1 from v_message_unit_emp
 33                       where  unit_type = ''3''
 34                       and    emp_code = f_get_cur_user_id()
 35                       and    instr(info_detail_desc,unit_code) >= 1)';
 36     execute immediate ls_sql_string into ln_count;
 37     if ln_count>0 then
 38        return 1;
 39     else
 40        return 0 ;
 41     end if;
 42  
 43  end F_CHECK_SPECIALMAT_ORDER;
 44  /Function createdExecuted in 0.734 secondsSQL>