cx_Oracle CREATE TABLE AS返回ORA-01036：非法变量名称/编号 [英] cx_Oracle CREATE TABLE AS returns ORA-01036: illegal variable name/number

问题描述

我正在尝试在Python 2.4.6上使用cx_Oracle创建表AS

I'm trying to CREATE TABLE AS using cx_Oracle on Python 2.4.6

以下代码：

    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)

返回： cx_Oracle.DatabaseError：ORA -01036：非法变量名称/编号

取出 CREATE TABLE TMP_STATS_1 AS 并仅保留 SELECT 语句非常有效。另外，在SQLPLUS上使用 CREATE TABLE AS 语句时，它可以正确运行。

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.

<$是否有任何特定的语法c $ c>创建表AS 可以在cx_Oracle中使用？试图查找示例，但到目前为止没有找到。

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.

推荐答案

最好在Oracle中创建可以创建的过程您的表，然后调用过程表格Python：

You are better off creating a procedure in Oracle that can create your table, and then call the procedure form Python:

create or replace procedure my_pro(p_table_name in varchar2, p_date_pass in date) as
 q1 varchar2(4000);
begin

 q1 := 'CREATE TABLE '|| p_table_name ||' AS
 select NUM from INF_CARD where IMPORT_DATE between
 trunc('||p_date_pass||') and trunc('||p_date_pass||') + 1 - (1/24/60/60)';

 EXECUTE IMMEDIATE q1;
end;

，然后从Python调用该过程：

and then call the procedure from Python:

import cx_Oracle
con = cx_Oracle.connect('###YourPath###')
cur = con.cursor()
cur.callproc('my_pro', ('TMP_STATS_1', datep))
cur.close()
con.close()

这篇关于cx_Oracle CREATE TABLE AS返回ORA-01036：非法变量名称/编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！