Forum Stats

  • 3,768,917 Users
  • 2,252,874 Discussions
  • 7,874,797 Comments

Discussions

SQL parameter for cx_Oracle

Twenty4billy
Twenty4billy Member Posts: 80 Blue Ribbon
edited Feb 22, 2018 4:07AM in Python

Hi,

I attempt to call a SQL script file with a parameter from cx_Oracle.  How can I do it?  The following is the code and the last is the error message I got.   Thanks for help.

>>>>>

import cx_Oracle

import os

db=cx_Oracle.makedsn("server name","1521","SID")

connection = cx_Oracle.connect(user="user", password="password", dsn=db)

cursor = connection.cursor()

file_name = "c:\1.sql"

file = open(file_name,"r")

sql_file = file.read()

cursor.execute(sql_file, '123')

<<<<<

Traceback (most recent call last):

  File "<input>", line 1, in <module>

cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,643 Employee
    edited Feb 21, 2018 9:59PM

    All the database drivers (cx_Oracle, PHP OCI8, Node.js node-oracledb) execute single SQL or PL/SQL statements.   They don't reimplement SQL*Plus.

    You need to find a way to extract the statements from the file and execute them individually (and without trailing semi-colons or slashes).  Because SQL*Plus scripts can handle 3 kinds of statements (SQL*Plus, SQL and PL/SQL) with three different line terminators (end-of-line, semi-colon and slash respectively) you may want to revisit your processes to make it easier to work in cx_Oralce.

    What are the constraints around what you are trying to do - where does the file come from?

  • Twenty4billy
    Twenty4billy Member Posts: 80 Blue Ribbon
    edited Feb 22, 2018 1:33AM

    Hi cj,

    Thanks for your reply.   I just like to run a SQL file which has a single statement.   And I need to pass a parameter.  How I can do it?  Thanks.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,784 Bronze Crown
    edited Feb 22, 2018 4:07AM

    How have you defined 1.sql?

    How is the substitution variable in 1.sql defined?

    You should print out the sql that is being generated and make sure it is syntactically correct.

This discussion has been closed.