Re: cx_Oracle CREATE TABLE AS returns ORA-01036: illegal variable name/number

Actually, this is an Oracle limitation: You cannot use bound variables in DDL 
statements (such as CREATE TABLE), even though it does make sense 
for CREATE TABLE AS SELECT.


You need to construct the statement as a string in SQL and execute it
using DBMS_UTILITY.EXEC_DDL_STATEMENT or Execute Immediate.
See for example http://www.dbforums.com/oracle/783494-include-variables-witihin-ddl-pl-sql-block.html
Just make sure your "datep" variable is properly sanitized (or does
not come from user-supplied data) because otherwise you open
yourself to SQL injections.

HTH,
	Shai.

On Friday 16 May 2014 06:46:54 Anthony Tuininga wrote:
> No special syntax is required for create table. Illegal variable
> name/number means that (a) the name you are using doesn't match the name
> you are passing or (b) the name you are using is illegal. These can
> sometimes be tough to find! When this sort of thing happens to me I simply
> replace bits and pieces until I figure out what the trouble is. Good luck!
> 
> Anthony
> 
> On Fri, May 2, 2014 at 11:44 AM, Marcus Diniz <[email protected]> wrote:
> > Hello there
> > 
> > 
> > I'm trying to CREATE TABLE AS using cx_Oracle on Python 2.4.6
> > 
> > The following code:
> >     query = '''CREATE TABLE TMP_STATS_1 AS
> >     select NUM from INF_CARD where IMPORT_DATE between
> >     to_date(:datepass || ' 00:00:00','dd/mm/yyyy hh24:mi:ss') and
> >     to_date(:datepass || ' 23:59:59','dd/mm/yyyy hh24:mi:ss')'''
> >     curs.execute(query, datepass=datep)
> > 
> > Returns: cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
> > 
> > Taking out the CREATE TABLE TMP_STATS_1 AS and leaving only the SELECT
> > statement, works perfectly. Also, when using CREATE TABLE AS statement on
> > SQLPLUS it runs correctly.
> > 
> > Is there any specific syntax for CREATE TABLE AS to be used in cx_Oracle?
> > Tried to find examples for this, but none found so far.
> > 
> > Regards,
> > Marcus
> > 
> > 
> > 
> > 
> > -------------------------------------------------------------------------
> > ----- "Accelerate Dev Cycles with Automated Cross-Browser Testing - For
> > FREE Instantly run your Selenium tests across 300+ browser/OS combos. 
> > Get unparalleled scalability from the best Selenium testing platform
> > available. Simple to use. Nothing to install. Get started now for free."
> > http://p.sf.net/sfu/SauceLabs
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.
Get unparalleled scalability from the best Selenium testing platform available
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs