OracleSqlParser

SQL Parser for Oracle

Ruby 3.2 or later (CI runs on 3.2, 3.3, 3.4 and 4.0). The gem itself does not need an Oracle client; only the connection tests described under Development do.

Add this line to your application's Gemfile:

gem 'oracle-sql-parser'

And then execute:

$ bundle

Or install it yourself as:

$ gem install oracle-sql-parser

query = "select 1 from dual"
parser = OracleSqlParser::Grammar::GrammarParser.new
syntax_tree = parser.parse query
if syntax_tree.nil?
  message = "\n#{query}\n" + " " * (parser.failure_column.to_i-1) + "*\n"
  raise parser.failure_reason + message
end
ast = syntax_tree.ast

=> #<OracleSqlParser::Ast::SelectStatement {:subquery=>#<OracleSqlParser::Ast::Subquery
  :has_parenthesis => nil,
  :query_block => #<OracleSqlParser::Ast::QueryBlock
    :hint => nil,
    :modifier => nil,
    :select_list => #<OracleSqlParser::Ast::Array [
      #<OracleSqlParser::Ast::SelectColumn
        :expr => #<OracleSqlParser::Ast::NumberLiteral {:value=>"1"}>,
        :as => nil,
        :c_alias => nil}>

    ]>
    ,
    :select_sources => #<OracleSqlParser::Ast::Array [
      #<OracleSqlParser::Ast::TableReference {:schema_name=>nil, :table_name=>#<OracleSqlParser::Ast::Identifier {:name=>"dual"}>, :dblink=>nil, :subquery=>nil, :table_alias=>nil}>
    ]>
    ,
    :where_clause => nil,
    :group_by_clause => nil,
    :model_clause => nil}>
  ,
  :subqueries => nil,
  :subquery => nil,
  :order_by_clause => nil}>
, :for_update_clause=>nil}>=> nil

ast.to_sql

=> "select 1 from dual"

p = ast.to_parameterized
p.to_sql
p.params.inspect

=> "select :a0 from dual"
=> {"a0"=>#<OracleSqlParser::Ast::NumberLiteral {:value=>"1"}>}

$ bundle install
$ bundle exec rake test:unit  # parser tests, no Oracle required

The parsers under lib/oracle-sql-parser/grammar/ are generated from the .treetop grammars next to them and the generated .rb files are committed. After editing a grammar (or reserved_word_generator.rb), regenerate and commit the result; CI fails if the committed parsers are out of date:

$ bundle exec rake gen_force  # compile **/*.treetop

The tests under test/oracle_enhanced-adapter run real queries through activerecord-oracle_enhanced-adapter and need Oracle Instant Client plus a reachable Oracle database. The easiest way to get both is the dev container in .devcontainer/, which starts Oracle Database Free next to a Ruby container that has Instant Client installed. Open the repository in the container (VS Code "Reopen in Container", or the devcontainer CLI) and run the tests:

$ devcontainer up --workspace-folder .
$ devcontainer exec --workspace-folder . bundle exec rake test  # test:unit and test:adapter

The Oracle gems are managed with Appraisal so that a plain bundle install works without Oracle; rake test:adapter switches to gemfiles/adapter_6.gemfile by itself when the current bundle does not have them. There is one appraisal per ActiveRecord version the tests are run against (6.1, 7.2 and 8.1, see Appraisals); to run the tests with all of them:

$ bundle exec appraisal rake test:adapter:run

To run against another Oracle database instead, install the appraisal gems and point the tests at it:

$ bundle exec appraisal install
$ export ORACLE_USERNAME=... ORACLE_PASSWORD=... ORACLE_HOST=localhost ORACLE_PORT=1521 ORACLE_SID=FREEPDB1
$ bundle exec rake test:adapter

Connection settings can also be put in test/oracle_enhanced-adapter/connection_params.yml (ignored by git).

Release notes live on GitHub Releases and are generated from the merged pull requests, grouped by label (.github/release.yml). CHANGELOG.md is kept for history only.

1. Open a pull request that updates lib/oracle-sql-parser/version.rb, label it release (so it is left out of the notes) and merge it.
2. On master, run bundle exec rake release, which creates the v<version> tag, pushes it and publishes the gem to rubygems.org.
3. On GitHub, draft a release for the tag, click "Generate release notes" and publish it.

Bug reports and pull requests are welcome on GitHub at https://github.com/jksy/sql_parser.

The gem is available as open source under the terms of the MIT License.