3 Replies Latest reply on Feb 22, 2018 9:07 AM by Gaz in Oz

    SQL parameter for cx_Oracle



      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

        • 1. Re: SQL parameter for cx_Oracle

          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?

          • 2. Re: SQL parameter for cx_Oracle

            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.

            • 3. Re: SQL parameter for cx_Oracle
              Gaz in Oz

              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.