得到ORA-01747: invalid user.table.列,表.当使用动态插入插入数据时，在Oracle中指定列

Question

代码如下:

p_id number,
p_mc varchar2

上述两个形参都将在过程调用时传入。

EXECUTE IMMEDIATE 'INSERT INTO COUNT_MASTER_TEMP ' || 'SELECT COUNT (ar.'|| p_mc  || ')' ||
        '
        FROM app_recipient ar
       WHERE EXISTS (SELECT r.' || p_mc ||
                       ' FROM app_recipient r
                      WHERE r.ID =' || p_id || ' AND ar.'|| p_mc || '= r.'|| p_mc ||')';

---

存储过程如下:

输入参数是p_id(这是一个数值)，p_mc实际上是一个列名。示例数据为p_id = 6372325，其中p_mc为MC1、MC2、MC14。

CREATE OR REPLACE PROCEDURE HCP_360.sp_get_all_records 
(p_id NUMBER, 
p_mc varchar2,
p_detail       OUT   SYS_REFCURSOR)
IS
   v_count       NUMBER;
   v_master_id   NUMBER;
   v_sql         VARCHAR2(2000);
   --v_sql2        VARCHAR2(2000);
   v_mc VARCHAR2(255):=p_mc;
   cur_detail                 SYS_REFCURSOR;
   BEGIN
    BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM COUNT_MASTER_TEMP';
execute immediate 'INSERT INTO COUNT_MASTER_TEMP ' ||
     'SELECT COUNT (ar.'|| v_mc|| ')' || '
   FROM app_recipient ar
   WHERE EXISTS (SELECT r.' || v_mc || ' FROM app_recipient r
   WHERE r.ID =' || p_id || ' AND ar.'|| v_mc || '= r.'|| v_mc||')';
    END;
    BEGIN
    select c_count
    into v_count
    from COUNT_MASTER_TEMP;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_count := 0;
   END;
   BEGIN
    IF v_count > 0
      THEN
               v_sql := 'SELECT   master_id
             FROM app_recipient
            WHERE ' ||  v_mc || ' IN (SELECT r.'||v_mc ||
                             ' FROM app_recipient r
                            WHERE r.ID = ' || p_id || ')
              AND ROWNUM <= 1
              AND master_id IS NOT NULL
         ORDER BY master_id DESC';
         EXECUTE IMMEDIATE 'DELETE FROM COUNT_MASTER_TEMP';
         EXECUTE IMMEDIATE 'INSERT INTO COUNT_MASTER_TEMP ' || v_sql;
     END IF;

    select c_count
    into v_master_id
    from COUNT_MASTER_TEMP;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_master_id := 0;
   END;

   BEGIN
   v_sql :=
    '
    SELECT r.ID,
       r.master_id,
       v.RECIPIENT_STATUS,
       v.PARENT_OR_CHILD,
       nvl(v.CHILD_COUNT, 0) CHILD_COUNT,
       r.IS_PICKABLE,
       r.IS_GOLDEN,
       r.request_wf_state,
       r.record_type,
       r.first_name,
       r.last_name, 
       r.'||v_mc ||
       ',r.middle,
       r.title,
       r.name_of_organization,
       r.name_of_business,
       r.address,
       r.city,
       r.state,
       r.country,
       v.HCP_TYPE,
       v.HCP_SUBTYPE,
       v.is_edit_locked,
       v.record_type as rec_type,
       v.DATA_SOURCE_NAME,
       v.DEA_DATA,
       v.NPI_DATA,
       v.STATE_DATA,
       RPPS,
       v.finess,
       v.siren_number
  FROM app_recipient r
  left join V_MASTER_RECIP_W_TRXN_OP v
    on r.id = v.id
    or r.master_id =' || v_master_id||
 'WHERE' ||'r.'||v_mc || '= ANY
 (SELECT ar.'||v_mc || 'FROM app_recipient ar WHERE r.ID =' || p_id || ')
UNION ALL
SELECT r.ID,
       r.master_id,
       v.RECIPIENT_STATUS,
       v.PARENT_OR_CHILD,
       nvl(v.CHILD_COUNT, 0) CHILD_COUNT,
       r.IS_PICKABLE,
       r.IS_GOLDEN,
       r.request_wf_state,
       r.record_type,
       r.first_name,
       r.last_name, 
       r.'||v_mc ||
       ',r.middle,
       r.title,
       r.name_of_organization,
       r.name_of_business,
       r.address,
       r.city,
       r.state,
       r.country,
       v.HCP_TYPE,
       v.HCP_SUBTYPE,
       v.is_edit_locked,
       v.record_type as rec_type,
       v.DATA_SOURCE_NAME,
       v.DEA_DATA,
       v.NPI_DATA,
       v.STATE_DATA,
       RPPS,
       v.finess,
       v.siren_number
  FROM app_recipient r
  left join V_MASTER_RECIP_W_TRXN_OP2 v
    on r.id = v.id
    or r.master_id =' || v_master_id ||'
 WHERE r.'||v_mc ||'= ANY (SELECT ar.'||v_mc ||'FROM app_recipient ar WHERE r.ID =' ||v_master_id || ')';
 open   cur_detail for v_sql;
 p_detail := cur_detail;
 end;
   END;
/

Answer 1

如果您使用pid作为具有非数字数据的VARCHAR ..您可以将其嵌入在单引号中，在查询形成期间，或将其定义为绑定变量。

总是尽可能使用绑定变量。

EXECUTE IMMEDIATE 
    'INSERT INTO COUNT_MASTER_TEMP ' ||
      'SELECT COUNT (ar.'|| p_mc  || ')' ||
        ' FROM app_recipient ar ' ||
       ' WHERE EXISTS (SELECT r.' || p_mc  ||
                       ' FROM app_recipient r ' ||
                      ' WHERE r.ID = :pid AND ar.'|| p_mc || '= r.'|| p_mc||')'
using pid;

Answer 2

您可以使用DBMS_OUTPUT测试字符串的形成。

例如,

SQL> set serveroutput on
SQL> DECLARE
  2    p_mc VARCHAR2(20);
  3    p_id NUMBER;
  4  BEGIN
  5    p_mc := 'mc';
  6    p_id := 1;
  7    dbms_output.put_line( 'INSERT INTO COUNT_MASTER_TEMP ' ||
  8    'SELECT COUNT (ar.'|| p_mc || ')' || '
  9  FROM app_recipient ar
 10  WHERE EXISTS (SELECT r.' || p_mc || ' FROM app_recipient r
 11  WHERE r.ID =' || p_id || ' AND ar.'|| p_mc || '= r.'|| p_mc||')');
 12  END;
 13  /
INSERT INTO COUNT_MASTER_TEMP SELECT COUNT (ar.mc)
FROM app_recipient ar
WHERE
EXISTS (SELECT r.mc FROM app_recipient r
WHERE r.ID =1 AND ar.mc= r.mc)
PL/SQL procedure successfully completed.
SQL>

始终记住首先使用DBMS_OUTPUT测试动态字符串。一旦确认字符串格式正确，删除/注释dbms_output并使用EXECUTE IMMEDIATE。

更新感谢Alex Poole。实际问题在这里:

', ' | | ' r。'||v_mc || '= ANY

WHERE后没有空格，查询失败。WHERE后需要加空格

'WHERE ' ||'r.'||v_mc || '= ANY

Answer 3

我可以立即看到从插入中获得错误的唯一方法是，如果你正在传递用单引号括起来的列名;您还没有显示调用，并且不太可能从SQL客户端执行此操作(更有可能在JDBC/PHP等中错误地添加引号)。参数)，但是像这样调用:

exec sp_get_all_records(6372325, '''MC1''', :detail);

将尝试执行生成的SQL，如下所示:

INSERT INTO COUNT_MASTER_TEMP SELECT COUNT (ar.'MC1')
   FROM app_recipient ar
   WHERE EXISTS (SELECT r.'MC1' FROM app_recipient r
   WHERE r.ID =6372325 AND ar.'MC1'= r.'MC1')

的列名以非法的单引号字符开头，这确实会抛出:

ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at "SCHEMA.SP_GET_ALL_RECORDS", line 17
ORA-06512: at line 1

和第17行是execute immediate 'INSERT...。

---

您还在最终的v_sql构造中缺少一些空白，这将使open cur_detail获得"ORA-00933: SQL命令未正确结束"。你需要修改

 'WHERE' ||'r.'||v_mc || '= ANY
 (SELECT ar.'||v_mc || 'FROM app_recipient ar WHERE r.ID =' || p_id || ')

 ' WHERE ' ||'r.'||v_mc || '= ANY
 (SELECT ar.'||v_mc || ' FROM app_recipient ar WHERE r.ID =' || p_id || ')

在第一个WHERE的后面和FROM的前面加空格;在并集的后半部分改变

 WHERE r.'||v_mc ||'= ANY (SELECT ar.'||v_mc ||'FROM app_recipient ar WHERE r.ID =' ||v_master_id || ')';

 WHERE r.'||v_mc ||'= ANY (SELECT ar.'||v_mc ||' FROM app_recipient ar WHERE r.ID =' ||v_master_id || ')';

再次在FROM前加空格。

这些不会得到您报告的ORA-01747错误，但是您所显示的插入也不会，它似乎对您传递的参数有效-除非您错误地引用了列名参数，如上面所示。

Answer 4

我正在使用PL/SQL开发人员测试我的过程。我仍然不知道为什么会出现ORA-01747。但是我修改了我的过程，并编写了一个PL/SQL代码来测试输出。

步骤如下:

CREATE OR REPLACE PROCEDURE HCP_360.sp_get_all_records 
(p_id NUMBER, 
p_mc in varchar2,
p_detail       OUT   SYS_REFCURSOR,
      p_count        OUT   NUMBER,
      p_error        OUT   NVARCHAR2)
IS
   v_count       NUMBER;
   v_master_id   NUMBER;
   v_sql         LONG;
   --v_sql2        VARCHAR2(2000);
   v_mc VARCHAR2(255):=p_mc;
   cur_detail                 SYS_REFCURSOR;
   BEGIN
    BEGIN
--      v_sql := '(SELECT COUNT ( ' ||v_mc || ')' ||
--        '
--        FROM app_recipient 
--       WHERE ' ||v_mc || ' IN  (SELECT ' || v_mc ||
--                       ' FROM app_recipient r
--                      WHERE r.ID =' || p_id || ' AND '|| v_mc || '='  || v_mc ||'))';
    v_sql :='(SELECT ar.'||v_mc  || ' as v_count' ||
        '
        FROM app_recipient ar
       WHERE EXISTS (SELECT r.'||v_mc ||
                       ' FROM app_recipient r
                      WHERE r.ID =' || p_id || ' AND ar.'||v_mc || '= r.'||v_mc ||'))'; 
   --EXECUTE IMMEDIATE 'DELETE FROM COUNT_MASTER_TEMP';
   EXECUTE IMMEDIATE ' select nvl(count(*),0) as count from (' || v_SQL || ')   '
                   INTO v_count;
   END;
   BEGIN
    IF v_count > 0
      THEN
               v_sql := 'SELECT   master_id
             FROM app_recipient
            WHERE ' ||  v_mc || ' IN (SELECT r.'||v_mc ||
                             ' FROM app_recipient r
                            WHERE r.ID = ' || p_id || ')
              AND ROWNUM <= 1
              AND master_id IS NOT NULL
         ORDER BY master_id DESC';

   EXECUTE IMMEDIATE ' select nvl(master_id, 0) from (' || v_SQL || ')   '
                   INTO v_master_id;
     END IF;

   END;
   BEGIN 
    EXECUTE IMMEDIATE ' delete from  GTT_RECIPEINTS_FOR_MC';
   execute immediate 'insert into  GTT_RECIPEINTS_FOR_MC
( MATCH_CODE_VALUE ,master_id ,id)
(SELECT r.'||v_mc ||', r.master_id,  r.id 
                  FROM app_recipient r
                                    WHERE r.'||v_mc || '= ANY (SELECT r.'||v_mc || '
                                     FROM app_recipient r
                                    WHERE r.ID =' || p_id||')
                UNION ALL
                SELECT r.'||v_mc ||', r.master_id,  r.id 
                  FROM app_recipient r
                 WHERE r.ID =' ||v_master_id ||')';
end;
   BEGIN

          v_sql :=
            '  
select      ( r.id) M_ID, r.RECIPIENT_STATUS, PARENT_OR_CHILD, nvl(CHILD_COUNT,0), r.IS_PICKABLE, r.IS_GOLDEN,  r.ID,   nvl(r.MASTER_ID,r.id) as MASTER_ID , r.request_wf_state,
      r.TITLE, r.FIRST_NAME, r.MIDDLE, r.LAST_NAME,r.FULL_NAME_LNF, r.FULL_NAME_FNF, r.NAME_OF_ORGANIZATION,r.ADDRESS, 
   r.CITY, r.STATE, r.COUNTRY,  r.HCP_TYPE, r.HCP_SUBTYPE, r.is_edit_locked, r.record_type as  rec_type, 
    DATA_SOURCE_NAME,DEA_DATA,NPI_DATA,STATE_DATA,RPPS  ,finess,siren_number    from v_master_recip_w_trxn_op r join  GTT_RECIPEINTS_FOR_MC p on   r.id=p.id or r.id= p.master_id
          order by  id';
 open   cur_detail for v_sql;
 p_detail := cur_detail;
 p_count :=SQL%ROWCOUNT;
    EXCEPTION
      WHEN OTHERS
      THEN
         p_count := 0;
         ROLLBACK;
 end;
   END;
/

测试的PL/SQL代码如下:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  p_count   number;
  p_error   NVARCHAR2 (255);
M_ID        app_recipient.id%type    ;
l_ID        app_recipient.id%type    ;
l_master_id        app_recipient.master_id%type    ;
l_RECIPIENT_STATUS        V_MASTER_RECIP_W_TRXN_OP2.recipient_status%type;
l_PARENT_OR_CHILD    V_MASTER_RECIP_W_TRXN_OP2.parent_or_child%type;
l_CHILD_COUNT    V_MASTER_RECIP_W_TRXN_OP2.child_count%type;
l_IS_PICKABLE    app_recipient.is_pickable%type;
l_IS_GOLDEN    app_recipient.is_golden%type;
l_request_wf_state    app_recipient.request_wf_state%type;
l_record_type    app_recipient.record_type%type;
l_first_name    app_recipient.first_name%type;
l_last_name    app_recipient.last_name%type;
l_p_mc    app_recipient.MC14%type;
l_middle    app_recipient.middle%type;
l_title    app_recipient.title%type;
l_name_of_organization    app_recipient.name_of_organization%type;
l_name_of_business    app_recipient.name_of_business%type;
l_address    app_recipient.address%type;
l_city    app_recipient.city%type;
l_state    app_recipient.state%type;
l_country    app_recipient.country%type;
l_HCP_TYPE    v_master_recip_w_trxn_op.HCP_TYPE%type;
l_HCP_SUBTYPE    V_MASTER_RECIP_W_TRXN_OP.HCP_SUBTYPE%type;
l_is_edit_locked    V_MASTER_RECIP_W_TRXN_OP.is_edit_locked%type;
l_rec_type    V_MASTER_RECIP_W_TRXN_OP.record_type%type;
l_DATA_SOURCE_NAME    V_MASTER_RECIP_W_TRXN_OP.data_source_name%type;
l_DEA_DATA    V_MASTER_RECIP_W_TRXN_OP.dea_data%type;
l_NPI_DATA    V_MASTER_RECIP_W_TRXN_OP.npi_data%type;
l_STATE_DATA    V_MASTER_RECIP_W_TRXN_OP.state_data%type;
l_RPPS    V_MASTER_RECIP_W_TRXN_OP.rpps%type;
l_finess    V_MASTER_RECIP_W_TRXN_OP.finess%type;
l_siren_number    V_MASTER_RECIP_W_TRXN_OP.siren_number%type;
l_FULL_NAME_LNF    V_MASTER_RECIP_W_TRXN_OP.siren_number%type;
l_FULL_NAME_FNF    V_MASTER_RECIP_W_TRXN_OP.siren_number%type;
BEGIN
  hcp_360.hcp360_application.get_recipients_for_mc(p_id    => &&var,
                p_mc        => &&p_mc,
              p_detail  => l_cursor,
              p_count => p_count,
              p_error => p_error);
  LOOP 
    FETCH l_cursor
    INTO  M_ID  ,
l_RECIPIENT_STATUS  ,
l_PARENT_OR_CHILD   ,
l_CHILD_COUNT   ,
l_IS_PICKABLE,
l_IS_GOLDEN,
l_ID,
L_MASTER_ID,
l_request_wf_state,
l_title,
l_first_name,
l_middle,
l_last_name,
l_FULL_NAME_LNF,
l_FULL_NAME_FNF,
l_name_of_organization,
l_address,
l_city,
l_state,
l_country,
l_HCP_TYPE,
l_HCP_SUBTYPE,
l_is_edit_locked,
l_rec_type,
l_DATA_SOURCE_NAME,
l_DEA_DATA,
l_NPI_DATA,
l_STATE_DATA,
l_RPPS,
l_finess,
l_siren_number;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(M_ID       ||  ' | '   ||
l_RECIPIENT_STATUS      ||  ' | '   ||
l_PARENT_OR_CHILD       ||  ' | '   ||
l_CHILD_COUNT       ||  ' | '   ||
l_IS_PICKABLE       ||  ' | '   ||
l_IS_GOLDEN     ||  ' | '   ||
l_ID        ||  ' | '   ||
L_MASTER_ID     ||  ' | '   ||
l_request_wf_state      ||  ' | '   ||
l_title     ||  ' | '   ||
l_first_name        ||  ' | '   ||
l_middle        ||  ' | '   ||
l_last_name     ||  ' | '   ||
l_FULL_NAME_LNF     ||  ' | '   ||
l_FULL_NAME_FNF     ||  ' | '   ||
l_name_of_organization      ||  ' | '   ||
l_address       ||  ' | '   ||
l_city      ||  ' | '   ||
l_state     ||  ' | '   ||
l_country       ||  ' | '   ||
l_HCP_TYPE      ||  ' | '   ||
l_HCP_SUBTYPE       ||  ' | '   ||
l_is_edit_locked        ||  ' | '   ||
l_rec_type      ||  ' | '   ||
l_DATA_SOURCE_NAME      ||  ' | '   ||
l_DEA_DATA      ||  ' | '   ||
l_NPI_DATA      ||  ' | '   ||
l_STATE_DATA        ||  ' | '   ||
l_RPPS      ||  ' | '   ||
l_finess        ||  ' | '   ||
l_siren_number              
 );
  END LOOP;
  CLOSE l_cursor;
END;