时间戳

首页 > 解决方案 > Oracle 存储过程运行时 ORA-01007 变量不在选择列表中

oracle - Oracle 存储过程运行时 ORA-01007 变量不在选择列表中

问题描述

我得到了一个存储过程（编译时没有错误/警告），如下所示：

create or replace procedure my_schema.SP_UPDATE_MEMBER(noP in varchar2, nameP in varchar2, idNoP in varchar2, birthdayP in varchar2, emailP in varchar2, phoneP in varchar2, passwordP in varchar2, tableP in varchar2)
is
v_prg_name varchar2(20) := 'SP_UPDATE_MEMBER';
v_cnt      number(8)    := 0;
sys_sql    varchar2(1000);

begin
  Insertlog(SYSDATE, v_prg_name, '1.0 Start'); --It's a simple log, tried remove it didn't solve my problem
  sys_sql :=  sys_sql || 'update '|| tableP || ' set ';
  if nameP is not null then
    sys_sql :=  sys_sql || '       name=''' || nameP || ''',';
  end if;
  if idNoP is not null then
    sys_sql :=  sys_sql || '       id_no=''' || idNoP || ''',';
  end if;
  if birthdayP is not null then
    sys_sql :=  sys_sql || '       birthday=to_date(''' || birthdayP || ''',''yyyy/MM/dd''),';
  end if;
  if emailP is not null then
    sys_sql :=  sys_sql || '       email=''' || emailP || ''',';
  end if;
  if phoneP is not null then
    sys_sql :=  sys_sql || '       mobile=''' || phoneP || ''',';
  end if;
  if passwordP is not null and length(passwordP) > 0 then 
    sys_sql :=  sys_sql || '       password=md5_hash(''' || passwordP || '''),';
  end if;
    sys_sql :=  sys_sql || '       no=''' || noP || ''' '; --I put it to prevent from , issue
    sys_sql :=  sys_sql || 'where  no=''' || noP || '''';

  EXECUTE IMMEDIATE sys_sql INTO v_cnt;
  commit;
  Insertlog(SYSDATE, v_prg_name, '2.0 Finished w/o error'); --It's a simple log, tried remove it didn't solve my problem

  exception
  when others then
    declare
      error_time VARCHAR2(30) := RTRIM(TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS'));
      error_code NUMBER := SQLCODE;
      error_msg  VARCHAR2(300) := SQLERRM;
    begin
      rollback;
      DBMS_OUTPUT.PUT_LINE(error_time || ',' || TO_CHAR(error_code) || ',' || error_msg); --It's a simple log, tried remove it didn't solve my problem
      Insertlog(SYSDATE, v_prg_name,  error_msg || ', 3.0 ERROR, sql:' || sys_sql); --It's a simple log, tried remove it didn't solve my problem
    end;
end;
/

并使用此脚本在 TOAD 中调用它（使用已调用方法的反馈且没有错误消息）：

call my_schema.SP_UPDATE_MEMBER('00112233','UserName','A123456789','1970/01/01','somemail@mail.com','0912346578','123abc','member');

但是会员数据没有更新，所以我检查了我的错误日志，它说：

ORA-01007: variable not in select list, 3.0 ERROR, sql:
update member
set    name='UserName',
       id_no='A123456789',
       birthday=to_date('1970/01/01','yyyy/MM/dd'),
       email='somemail@mail.com',
       mobile='0912345678',
       password=md5_hash('123abc'),
       no='00112233'
       where  no='00112233'

我没有找到哪里会导致这个 ORA-01007 错误，你能帮我找到它吗？或一些提示...

标签： oraclestored-proceduresplsqldynamic-sql

解决方案

你像这样执行你的汇编语句：

EXECUTE IMMEDIATE sys_sql INTO v_cnt;

但这是一个 UPDATE 语句，它什么也不返回。所以 INTO 子句是错误的。

大概您想知道更新了多少行？试试这个：

EXECUTE IMMEDIATE sys_sql;
v_cnt := sql%rowcount;

推荐阅读