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;
/

I got this error ORA-01008: not all variables bound when i am running this pl sql. And, All statements are running fine except EXECUTE IMMEDIATE q'';

Because :start_epochtime does not have any assignment. You need to add the "USING" clause. — OldProgrammer
– OldProgrammer, Commented Mar 18, 2016 at 11:30

Bob Jarvis - Слава Україні · Accepted Answer · 2016-03-18 11:38:56Z

2

There doesn't appear to be a reason to use EXECUTE IMMEDIATE here. You're not building a dynamic query, nor are you executing a DDL statement. I suggest replacing the EXECUTE IMMEDIATE with

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

Best of luck.

answered Mar 18, 2016 at 11:38

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wernfried Domscheit Over a year ago

I think you can write it sorter GROUP BY m.CREATIONDATE, since all others are constant.

Rajini Rajas Over a year ago

Hey Bob, I got the below error when i m running as u suggested SQL> 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; * ERROR at line 23: ORA-06550: line 23, column 3: PLS-00428: an INTO clause is expected in this SELECT statement

Bob Jarvis - Слава Україні Over a year ago

OK, so either add an INTO clause to the SELECT statement, or turn the SELECT into a cursor and iterate over the cursor. Best of luck.

MT0 · Accepted Answer · 2016-03-18 11:50:02Z

If you really want to do it all in PL/SQL then you can do:

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;

But it would be simpler to do it in 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;

(Note: I've left your logic as-is but moved it from continually context switching from PL/SQL to SQL to just use PL/SQL as much as possible and ANSI Date literals; however, I do think that you have the -30 in the wrong places as it ought to be for the start_epochtime and not the end_epochtime.)

Collectives™ on Stack Overflow

Oracle PL SQL - Issue with EXECUTE IMMEDIATE

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related