CODEWITHSUNDEEP
sqloracle-databaseplsqlcursorexecute-immediate
Rajini Rajas
Rajini Rajas

Reputation: 1

Oracle PL SQL - Issue with EXECUTE IMMEDIATE

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

Upvotes: 0

Views: 811

Answers (2)

MT0
MT0

Reputation: 167962

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.)

Upvotes: 1

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

Reputation: 50017

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.

Upvotes: 2

Related Questions