Using a Variable in a Table Creation Script

Question

I'm having some difficulty in creating a PL/SQL block. The purpose of my script is to pull a report based on the last month's totals in a table (which has already been created as table name "countpull"). I want to use the name of the month in my script to create the table, but Oracle is returning an error "ORA-00900: invalid SQL statement", pointing at the CREATE TABLE command below.

DECLARE
  curMonthChar NVARCHAR2(25);
  curTableName NVARCHAR2(50);
  tableexists NUMBER := 0;

BEGIN
  SELECT TO_CHAR(ADD_MONTHS(sysdate,-1),'fmMONTH') INTO curMonthChar FROM DUAL;
  SELECT ('QP17414_'||curMonthChar) INTO curTableName FROM DUAL;

  --Check to see if current month's count table exists
  SELECT COUNT(1) INTO tableexists FROM all_tab_columns WHERE OWNER = (SELECT USER FROM DUAL) AND table_name = curTableName;

  --If current month's count table exists, drop it
  IF tableexists > 0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE '||curTableName;
  END IF;

  --Create current month's count table
  EXECUTE IMMEDIATE '
  CREATE TABLE '||curTableName||' AS (
    SELECT * FROM (
      SELECT movetype, phone_flag, state FROM countpull
      )
    PIVOT (
      COUNT(state)
      FOR state IN ('''||'AA'||''','''||'AE'||''','''||'AK'||''','''||'AL'||'''
        ,'''||'AP'||''','''||'AR'||''','''||'AS'||''','''||'AZ'||''','''||'CA'||'''
        ,'''||'CO'||''','''||'CT'||''','''||'DC'||''','''||'DE'||''','''||'FL'||'''
        ,'''||'FM'||''','''||'GA'||''','''||'GU'||''','''||'HI'||''','''||'IA'||'''
        ,'''||'ID'||''','''||'IL'||''','''||'IN'||''','''||'KS'||''','''||'KY'||'''
        ,'''||'LA'||''','''||'MA'||''','''||'MD'||''','''||'ME'||''','''||'MH'||'''
        ,'''||'MI'||''','''||'MN'||''','''||'MO'||''','''||'MP'||''','''||'MS'||'''
        ,'''||'MT'||''','''||'NC'||''','''||'ND'||''','''||'NE'||''','''||'NH'||'''
        ,'''||'NJ'||''','''||'NM'||''','''||'NV'||''','''||'NY'||''','''||'OH'||'''
        ,'''||'OK'||''','''||'OR'||''','''||'PA'||''','''||'PR'||''','''||'PW'||'''
        ,'''||'RI'||''','''||'SC'||''','''||'SD'||''','''||'TN'||''','''||'TX'||'''
        ,'''||'UT'||''','''||'VA'||''','''||'VI'||''','''||'VT'||''','''||'WA'||'''
        ,'''||'WI'||''','''||'WV'||''','''||'WY'||''')
    ))';

END;

The script above works just fine when I don't try to use a variable for the table name. But since I want the table name to dynamically change every time the script is run, I want to avoid using a static name.

Why is it invalid to create a table with a dynamic table name in a PL/SQL block?

Additional info:

The table schema for countpull is

state NVARCHAR2(2),
movetype NVARCHAR2(1),
phone_flag NVARCHAR2(1)

Alex Poole · Accepted Answer · 2013-10-16T09:04:30.123

For reasons that aren't immediately clear to me, the problem is that the variable you're using is nvarchar2. It works if you declare it as varchar2:

  curTableName VARCHAR2(50);

Validated via SQL Fiddle; that fiddle fails if you just change the declaration of the table name variable to nvarchar2.

As @jonearles points out, the documentation does state that the command string's 'type must be either CHAR, VARCHAR2, or CLOB'. And although the documentation for the concatenation operator doesn't refer to it, the related concat function does state that 'if one of the arguments is a national data type, then the returned value is a national data type' - so your command string is nvarchar2 because the variable you're using in the concatenation is nvarchar2, making the argument to the execute immediate statement illegal.

It's not a great idea to create tables on the fly though. Schema objects should normally be created once. You could have a table - possibly a global temporay table - with a month column, or at a push twelve tables that you empty and populate as needed.

Also, all your select from dual statements could be simplified:

  curMonthChar := TO_CHAR(ADD_MONTHS(sysdate,-1),'fmMONTH');
  curTableName := 'QP17414_'||curMonthChar);

etc., even in your subquery:

... WHERE OWNER = user AND ...

... and I have no idea why you're using so much concatenation in your pivot clause.

The [manual](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#LNPLS1201) does not explain why, it only says the dynamic SQL string "...must be either CHAR, VARCHAR2, or CLOB." — Jon Heller, Oct 16 '13 at 00:36
@jonearles - thanks - I had that section open in another tab and still couldn't see it. I've added doc links for the concatenation making the overall dynamic string `nvarchar2` as well, which also wasn't immediately obvious. — Alex Poole, Oct 16 '13 at 09:05
Thank you Alex, that seems to have been the problem. This is my first real foray into PL/SQL after switching over from SQL Server so I've got quite a bit to learn. The concatenation in my pivot is a result of the many steps I had taken to try to figure out what was "invalid" about my SQL. I thought that I must be setting up the pivot up incorrectly. Thank you also for the suggestions simplification. As far as using, nvarchar2, I have to chalk that up to developing a learned bad habit of not paying close enough attention to using the correct data types for the need. — Emma, Oct 16 '13 at 14:00

Using a Variable in a Table Creation Script

1 Answers1