oracle - 使用动态插入插入数据时在 Oracle 中获取 ORA-01747 : invalid user. table.column、table.column 或列规范

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..并且在查询形成期间将其嵌入单引号中，或将其定义为绑定(bind)变量。

尽可能使用绑定(bind)变量。

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;