Oracle PL SQL - Issue with EXECUTE IMMEDIATE

Question

DECLARE
  start_date VARCHAR2(12);
  end_date VARCHAR2(12);
  start_epochtime VARCHAR2(15);
  end_epochtime VARCHAR2(15);
  v_sql VARCHAR2(1024);
BEGIN
  SELECT to_char(current_date,'YYYY-MM-DD') into start_date from dual;
  SELECT to_char(current_date - 30,'YYYY-MM-DD') into end_date from dual;
  dbms_output.put_line(start_date);
  dbms_output.put_line(end_date);
  /* Below section will convert date to epochtime with hard code date value     */
  SELECT CAST((TO_DATE('2016-01-01','YYYY-MM-DD') - TO_DATE('1970-01-    01','YYYY-MM-DD') ) * 24 * 60 * 60 * 1000 AS VARCHAR(15)) into start_epochtime FROM DUAL;
  SELECT CAST((TO_DATE('2016-01-01','YYYY-MM-DD') - TO_DATE('1970-01-01','YYYY-MM-DD') - 30) * 24 * 60 * 60 * 1000 AS VARCHAR(15)) into end_epochtime FROM DUAL;
  dbms_output.put_line(start_epochtime);
  dbms_output.put_line(end_epochtime);
  /* Below section will convert date to epochtime with a variable */
  SELECT CAST((TO_DATE(start_date,'YYYY-MM-DD') - TO_DATE('1970-01-01','YYYY-MM-DD') ) * 24 * 60 * 60 * 1000 AS VARCHAR(15)) into start_epochtime FROM DUAL;
  SELECT CAST((TO_DATE(end_date,'YYYY-MM-DD') - TO_DATE('1970-01-01','YYYY-MM-DD') - 30) * 24 * 60 * 60 * 1000 AS VARCHAR(15)) into end_epochtime FROM DUAL;
  dbms_output.put_line(start_epochtime);
  dbms_output.put_line(end_epochtime);
  EXECUTE IMMEDIATE q'[select to_char((TO_DATE('1970-01-01','yyyy-mm-dd') + (m.CREATIONDATE/1000/24/60/60)),'YYYY-MM-DD'),count(1) from jivemessage_us m where m.CREATIONDATE >= :start_epochtime and m.CREATIONDATE <= :end_epochtime group by to_char((TO_DATE('1970-01-01','yyyy-mm-dd') + (m.CREATIONDATE/1000/24/60/60)),'YYYY-MM-DD') order by 1]';
END;
/

当我运行这个 pl sql 时，我ORA-01008: not all variables bound收到此错误。并且，除EXECUTE IMMEDIATE q''外，所有语句都运行良好;

Answer 1

这里似乎没有理由使用EXECUTE IMMEDIATE。您不是在构建动态查询，也不是在执行 DDL 语句。我建议将EXECUTE IMMEDIATE替换为

select to_char((TO_DATE('1970-01-01','yyyy-mm-dd') + 
                  (m.CREATIONDATE/1000/24/60/60)),'YYYY-MM-DD'),
       count(1)
  from jivemessage_us m
  where m.CREATIONDATE >= start_epochtime and
        m.CREATIONDATE <= end_epochtime
  group by to_char((TO_DATE('1970-01-01','yyyy-mm-dd') +
                      (m.CREATIONDATE/1000/24/60/60)),'YYYY-MM-DD')
  order by 1

祝你好运。

Answer 2

如果你真的想在PL/SQL中完成所有工作，那么你可以做：

VARIABLE cur REFCURSOR;
DECLARE
  start_date      VARCHAR2(12);
  end_date        VARCHAR2(12);
  start_epochtime VARCHAR2(15);
  end_epochtime   VARCHAR2(15);
  v_sql           VARCHAR2(1024);
BEGIN
  start_date := TO_CHAR(current_date,      'YYYY-MM-DD');
  end_date   := TO_CHAR(current_date - 30, 'YYYY-MM-DD');
  dbms_output.put_line(start_date);
  dbms_output.put_line(end_date);
  /* Below section will convert date to epochtime with hard code date value     */
  start_epochtime := ( DATE '2016-01-01' - DATE '1970-01-01' ) * 24 * 60 * 60 * 1000;
  end_epochtime   := ( DATE '2016-01-01' - DATE '1970-01-01' - 30 ) * 24 * 60 * 60 * 1000;
  dbms_output.put_line(start_epochtime);
  dbms_output.put_line(end_epochtime);
  /* Below section will convert date to epochtime with a variable */
  start_epochtime := ( CURRENT_DATE      - DATE '1970-01-01' ) * 24 * 60 * 60 * 1000;
  end_epochtime   := ( CURRENT_DATE - 30 - DATE '1970-01-01' ) * 24 * 60 * 60 * 1000;
  dbms_output.put_line(start_epochtime);
  dbms_output.put_line(end_epochtime);
  OPEN :cur FOR
  select   to_char(DATE '1970-01-01' + CREATIONDATE/1000/24/60/60,'YYYY-MM-DD'),
           count(1)
  from     jivemessage_us
  where    CREATIONDATE BETWEEN start_epochtime and end_epochtime
  group by CREATIONDATE
  order by 1;
END;
/
PRINT cur;

但是在SQL中这样做会更简单：

select   to_char(DATE '1970-01-01' + CREATIONDATE/1000/24/60/60,'YYYY-MM-DD'),
         count(1)
from     jivemessage_us
where    CREATIONDATE BETWEEN ( CURRENT_DATE      - DATE '1970-01-01' )*24*60*60*1000
                    AND     ( CURRENT_DATE - 30 - DATE '1970-01-01' )*24*60*60*1000
group by CREATIONDATE
order by 1;

（注意：我保留了您的逻辑，但将其从不断的上下文从 PL/SQL 切换到 SQL，以尽可能多地使用 PL/SQL 和 ANSI Date 文字;但是，我确实认为您将-30放在错误的地方，因为它应该用于start_epochtime而不是end_epochtime。