CODEWITHSUNDEEP
oracle-databaseplsqloracle11gplsqldeveloper
Emma
Emma

Reputation: 277

Using a Variable in a Table Creation Script

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)

Upvotes: 2

Views: 2231

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

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.

Upvotes: 2

Related Questions