动态SQL: ORA-00925和ORA-06512

Question

这段代码没有任何意义，只是为了训练。但是这里有这样的错误，当调用它时：

EXEC ADD_ROW( 'fds', 'hgd', '243-650-215', 2);

1 ORA-00925:缺少关键字ORA-06512: at "MYDB.ADD_ROW"，第8行ORA-06512: at第2行Procedure:MYDB.ADD_ROW(MyDB@localhost:ORCL) 8 1

CREATE OR REPLACE PROCEDURE MYDB.add_row(
  v1 IN CLIENT.FNAME%TYPE, v2 IN CLIENT.LNAME%TYPE, v3 IN CLIENT.DOC_ID%TYPE, v4 IN CLIENT.BENEFIT_ID%TYPE)
  IS
  sql_stmnt VARCHAR(200);
  tab_name VARCHAR2(20) := 'CLIENT';
  BEGIN
    sql_stmnt := 'INSERT INTO' || tab_name ||  'VALUES(100, :1,:2,:3,:4)';
    EXECUTE IMMEDIATE sql_stmnt USING  v1, v2, v3, v4;
  END;

Answer 1

我想你只是失去了一个空间，不管是哪一边：' || tab_name || '

CREATE OR REPLACE PROCEDURE MYDB.add_row(
  v1 IN CLIENT.FNAME%TYPE, v2 IN CLIENT.LNAME%TYPE, v3 IN CLIENT.DOC_ID%TYPE, v4 IN CLIENT.BENEFIT_ID%TYPE)
  IS
  sql_stmnt VARCHAR(200);
  tab_name VARCHAR2(20) := 'CLIENT';
  BEGIN
    sql_stmnt := 'INSERT INTO ' || tab_name ||  ' VALUES(100, :1,:2,:3,:4)';
    EXECUTE IMMEDIATE sql_stmnt USING  v1, v2, v3, v4;
  END;

Answer 2

您的表名周围缺少空格。此代码将生成表单INSERT INTOmy_tableVALUES(100, :1,:2,:3,:4)的SQL。

只需修改sql生成：

sql_stmnt := 'INSERT INTO ' || tab_name ||  ' VALUES(100, :1,:2,:3,:4)';