ORA-00933 when binding var as syntax part of query

[jameschenjav]

cursor = conn.parse('SELECT 1 FROM DUAL &foo')
cursor.bind_param(:foo, 'WHERE 1 = 1')
cursor.exec # OCIError: ORA-00933: SQL command not properly ended

got error: OCIError: ORA-00933: SQL command not properly ended from stmt.c:243:in oci8lib_220.so

This works fine:

cursor = conn.parse('SELECT &foo FROM DUAL')
cursor.bind_param(:foo, 'abc')
cursor.exec
cursor.fetch

[kubo]

Ruby-oci8 doesn't support substitution variables but supports bind variables. See https://community.oracle.com/docs/DOC-915518 about difference between a substitution variable and a bind variable.

If you need to replace syntax part in a SQL statement, use "... #{variable} ..." instead:

foo = 'WHERE 1 = 1'
cursor = conn.parse("SELECT 1 FROM DUAL #{foo}")
cursor.exec

By the way, I really surprised that &foo acted as a bind variable. I thought that the second example in the previous post didn't work.

[jameschenjav]

We have about 200 reports built with some oracle tools. I believe it's the only way to build where clauses. They build where clauses first :where_clauses := ...; and pass it to query SELECT ... &where_clauses. Now we are trying to generate the same XML data file in Rails. So we don't know if any substitution exists until parsed. If that's ruby code, it wouldn't be any problem.

[kubo]

ActiveRecord used in Rails has methods to build where clauses dynamically. See http://guides.rubyonrails.org/active_record_querying.html#conditions

If you need to use substitution variables, try the following:

class SubstStatement
  @@regex = /&([a-zA-Z]\w*)/ # This may be different from syntax used by SQL*Plus to search substitution variables.
  def initialize(stmt)
    @stmt = stmt
    @params = {}
  end
  def set_param(key, val)
    @params[key] = val
  end
  def to_s
    @stmt.gsub(@@regex) do
      @params[$1] || (raise "Unspecified substitution variable: #{$1}")
    end
  end
end

stmt = SubstStatement.new('SELECT 1 FROM DUAL &foo')
stmt.set_param('foo', 'WHERE 1 = 1')
cursor = conn.exec(stmt.to_s)

Be careful about SQL injection when you use it.