oracle pl/sql ora-01722错误

发布于 2024-07-22 20:49:17 字数 391 浏览 6 评论 0原文

我的程序中有一个简单的 oracle 语句：

update org.security_training_question a 
set a.actv_indr = 'N' where a.qstn_id in (v_qstns_to_delete);

v_qstns_to_delete 是一个正在传递的参数。它是一个 varchar2 字段，a.qstn_id 是一个数字字段。

调用存储过程时，对于 v_qstns_to_delete，我传递以下字符串：“24, 43, 23, 44, 21”。

当我运行语句输出存储过程时，它运行正常，但是当我将其作为存储过程运行时，我在上面的行中收到错误，提示“无效数字”。

有什么线索吗？

原文

I have a simple oracle statement in my procedure:

update org.security_training_question a 
set a.actv_indr = 'N' where a.qstn_id in (v_qstns_to_delete);

v_qstns_to_delete is a parameter being passed. It is a varchar2 field and a.qstn_id is a numeric field.

When calling the Stored Procedure, for v_qstns_to_delete I am passing the following String: "24, 43, 23, 44, 21".

When I run the statement output the stored procedure thenn it runs fine but when I run it as a stored procedure I get an error on the above line saying Invalid Number.

Any clue?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寂寞笑我太脆弱 2024-07-29 20:49:17

您不能将“in”子句与这样的变量一起使用。一种解决方法是

declare stmt varchar2(4000);
begin
  stmt := 'update org.security_training_question a set a.actv_indr = ''N'' where a.qstn_id in ('||v_qstns_to_delete||')';
  execute immediate stmt;
end;

You can't use a "in" clause with a variable like that. One way around it is

declare stmt varchar2(4000);
begin
  stmt := 'update org.security_training_question a set a.actv_indr = ''N'' where a.qstn_id in ('||v_qstns_to_delete||')';
  execute immediate stmt;
end;

回复收藏 0 原文

原来是傀儡 2024-07-29 20:49:17

如果 v_qstns_to_delete 是 varchar，您需要对其进行某种转换，以便让 Oracle 了解其中可能有多个项目。一种方法是将字符串转换为项目表。

假设 qstn_id 是一个 NUMBER 列，您可以：

SQL> CREATE TYPE tab_number AS TABLE OF NUMBER;
  2  /

Type created

SQL> CREATE OR REPLACE FUNCTION to_tab_number(p_in VARCHAR2,
  2                                           p_separator VARCHAR2 DEFAULT ',')
  3        RETURN tab_number AS
  4     l_result tab_number := tab_number();
  5     l_tail   LONG := p_in;
  6  BEGIN
  7     WHILE l_tail IS NOT NULL LOOP
  8        l_result.EXTEND;
  9        IF instr(l_tail, p_separator) != 0 THEN
 10           l_result(l_result.COUNT) := to_number(substr(l_tail,
 11                                                 1,
 12                                                 instr(l_tail, p_separator) - 1));
 13           l_tail := substr(l_tail, instr(l_tail, p_separator) + 1);
 14        ELSE
 15           l_result(l_result.COUNT) := to_number(l_tail);
 16           l_tail := NULL;
 17        END IF;
 18     END LOOP;
 19     RETURN l_result;
 20  END;
 21  /

Function created

然后您可以将字符串从 SQL 转换为数字表：

SQL> SELECT * FROM TABLE(to_tab_number('24, 43, 23, 44, 21'));

COLUMN_VALUE
------------
          24
          43
          23
          44
          21

要在列表中执行变量：

SQL> SELECT object_id, owner
  2    FROM all_objects
  3   WHERE object_id IN (SELECT column_value FROM TABLE(to_tab_number('18,19,20')));

 OBJECT_ID OWNER
---------- ------------------------------
        18 SYS
        19 SYS
        20 SYS

有关同一主题的更多信息，请参见 askTom。

if v_qstns_to_delete is a varchar, you would need to convert it somewhat to let Oracle understand that there may be several items in it. One method would be to convert the string to a table of items.

Supposing qstn_id is a NUMBER column, you would:

SQL> CREATE TYPE tab_number AS TABLE OF NUMBER;
  2  /

Type created

SQL> CREATE OR REPLACE FUNCTION to_tab_number(p_in VARCHAR2,
  2                                           p_separator VARCHAR2 DEFAULT ',')
  3        RETURN tab_number AS
  4     l_result tab_number := tab_number();
  5     l_tail   LONG := p_in;
  6  BEGIN
  7     WHILE l_tail IS NOT NULL LOOP
  8        l_result.EXTEND;
  9        IF instr(l_tail, p_separator) != 0 THEN
 10           l_result(l_result.COUNT) := to_number(substr(l_tail,
 11                                                 1,
 12                                                 instr(l_tail, p_separator) - 1));
 13           l_tail := substr(l_tail, instr(l_tail, p_separator) + 1);
 14        ELSE
 15           l_result(l_result.COUNT) := to_number(l_tail);
 16           l_tail := NULL;
 17        END IF;
 18     END LOOP;
 19     RETURN l_result;
 20  END;
 21  /

Function created

You could then convert a string to a table of number from SQL:

SQL> SELECT * FROM TABLE(to_tab_number('24, 43, 23, 44, 21'));

COLUMN_VALUE
------------
          24
          43
          23
          44
          21

To do a variable in-list:

SQL> SELECT object_id, owner
  2    FROM all_objects
  3   WHERE object_id IN (SELECT column_value FROM TABLE(to_tab_number('18,19,20')));

 OBJECT_ID OWNER
---------- ------------------------------
        18 SYS
        19 SYS
        20 SYS