EXECUTE IMMEDIATE CREATE TABLE

Question

Can anyone help me with this error? I wanted to Execute Immediate a Create Table statement that concatenates a table name that will change everyday/month.

This is my code...

DECLARE
    v_table_name   VARCHAR2(100);
    v_get_fromdate VARCHAR2(200);
BEGIN
    v_table_name   := 'mpiat_after_bs_' || SUBSTR(TO_CHAR(SYSDATE, 'ddmonyy'), 3, 5) || '_t';
    v_get_fromdate := 'select GET_FROMDATE(to_date(''01/'|| SUBSTR(TO_CHAR(SYSDATE, 'mmddyy'), 1, 2
    ) ||'/2017 00:00:00'',''dd/mm/yyyy hh24:mi:ss''),''R'') from dual;';
    EXECUTE IMMEDIATE ''

    CREATE TABLE ' || v_table_name || ' AS
    SELECT column1 ,
        column2 ,
        column3 ,
        column4 ,
        column5
    FROM table_name
    WHERE column1 >=' || v_get_fromdate ||'
    AND column3 LIKE ''tbl_%''';    
END;

this is the error the I am getting...

Error report -
ORA-00936: missing expression
ORA-06512: at line 7
00936. 00000 -  "missing expression"
*Cause:    
*Action:


Line 7 is EXECUTE IMMEDIATE 'CREATE TABLE ' || v_table_name || '  AS    SELECT column1

I've searched a lot of code regarding this... but I haven't seen anyone concatenates table name as a variable while creating table.

I just wanna know if this is possible. And i am open for any suggestion for this to work. Thank You!

Check your quotes. E.g. `EXECUTE IMMEDIATE ''`should be `EXECUTE IMMEDIATE '`. Also you can't use `;` in a subquery (v_get_fromdate ) — daZza, Aug 22 '17 at 12:38
this is the exact code, I just change the values for data protection. thank you — Luis, Aug 22 '17 at 12:52
@Luis - I would consider not doing this - it is usually a terrible idea to create many tables like this and the actual correct solution is either archiving or partitioning — Milney, Aug 22 '17 at 14:56

Sudipta Mondal · Answer 1 · 2017-08-22T12:37:08.537

1

Line -

EXECUTE IMMEDIATE ''

You only need 1 quote and not 2 quotes

Change it to

EXECUTE IMMEDIATE '

Line

v_get_fromdate := 'select GET_FROMDATE(to_date(''01/'|| SUBSTR(TO_CHAR(SYSDATE, 'mmddyy'), 1, 2
) ||'/2017 00:00:00'',''dd/mm/yyyy hh24:mi:ss''),''R'') from dual;';

that should be

v_get_fromdate := '(select GET_FROMDATE(to_date(''01/'||   SUBSTR(TO_CHAR(SYSDATE, 'mmddyy'), 1, 2
) ||'/2017 00:00:00'',''dd/mm/yyyy hh24:mi:ss''),''R'') from dual)';

Semi-colons cannot be used within a sub query.

edited Aug 22 '17 at 12:37

answered Aug 22 '17 at 12:26

Sudipta Mondal

2,550
1
19
20

My apologies... It should be 1 quote but I've put here 2 quotes. But this is not the error. thank you. – Luis Aug 22 '17 at 12:31
thank you. I've already updated the code, but still getting same error. – Luis Aug 22 '17 at 12:49
@Luis - even after adding the brackets in `v_get_fromdate` ? – Sudipta Mondal Aug 22 '17 at 15:08

score 0 · Answer 2 · answered Aug 23 '17 at 09:07

As per your code, we donr need SELECT statement to fetch value from oracle function. We should avoid as much as possible dynamic queries. Hope this below snippet helps.

DECLARE
  v_table_name   VARCHAR2(100);
  v_get_fromdate VARCHAR2(200);
BEGIN
  v_table_name   := 'mpiat_after_bs_' || SUBSTR(TO_CHAR(SYSDATE, 'ddmonyy'), 3, 5) || '_t';
  V_GET_FROMDATE := GET_FROMDATE(TO_DATE('01/'|| SUBSTR(TO_CHAR(SYSDATE, 'mmddyy'), 1, 2) ||'/2017 00:00:00','dd/mm/yyyy hh24:mi:ss'),'R');
  EXECUTE IMMEDIATE '    
      CREATE TABLE ' || v_table_name || ' AS    
      SELECT column1 ,        
      column2 ,        
      column3 ,        
      column4 ,        
      column5    
      FROM table_name    
      WHERE column1 >=' || v_get_fromdate ||'    
      AND column3 LIKE ''tbl_%''';
END;
/

EXECUTE IMMEDIATE CREATE TABLE

2 Answers2