Oracle数据库中PL/SQL中的INSERT语句失败 [英] INSERT Statement in PL/SQL fails in Oracle database

问题描述

使用

DBMS_SQL.EXECUTE('insert into tablename VALUES(aNumber)');

它给出了ASCII字符错误. 当直接插入时，它会成功插入.

It gives an ASCII character error. And when inserting it directly it succeeds and inserts it.

推荐答案

在PL/SQL中，您可以直接编写INSERT语句.

In PL/SQL you could write the INSERT statement directly.

DECLARE
  tablevalue      varchar2(200);
BEGIN
  tablevalue := 'Hello World!';

  INSERT   INTO tablename
         VALUES (tablevalue);
END;

您的语句失败，因为这不是DBMS_SQL.EXECUTE的工作方式.查看文档和示例: http://docs .oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#BABBFFFJ

Your statement fails because that is not the way DBMS_SQL.EXECUTE works. Check out the documentation and the example: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#BABBFFFJ

根据参考文档中给出的示例，您应该这样做(首先准备语句，然后绑定变量然后运行它).

According to the example given in the reference documentation you should do it like this (first you prepare the statement, then bind the variable and then run it).

CREATE OR REPLACE PROCEDURE demo(tablevalue IN varchar2) AS
    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(cursor_name, 'INSERT INTO tablename VALUES(:x)',
                   DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', tablevalue);
    rows_processed := DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
    raise;
END;

然后像这样使用它

 exec demo('something');

希望有帮助

这篇关于Oracle数据库中PL/SQL中的INSERT语句失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！