Oracle provider does not work correctly when quoted identifiers are used

Open
#778 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
45/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
fsharp, sql
Domain
databases

Research direction

Start by reproducing the issue with the quoted Oracle schema, table, and column identifiers shown in the report, then trace the generated SQL for the provider. Compare the failing unquoted statement with the expected quoted form and verify that queries against quoted identifiers succeed without breaking unquoted identifiers.

Written by the indexing model from the issue text.

Description

Oracle

Describe the bug
Like Firebug (#431) Oracle also has a problem with quoted table names. Actually the same goes for schema names and column names as well. (ref). When a table is created with quoted identifiers:

CREATE TABLE "SchemaName"."table_name" 
   (	"timestamp" TIMESTAMP (6) NOT NULL ENABLE, 
	"text" NVARCHAR2(50) NOT NULL ENABLE  )

Identifiers with quotes are case sensitive. Identifiers without quotes are treated as uppercase. To maintain the casing of the identifier names, they should be referenced everywhere with quotes:

SELECT t."timestamp", t."text" FROM "SchemaName"."table_name" t

To Reproduce
Steps to reproduce the behavior:

  1. create a table like mentioned above
  2. create a query to retrieve records from that table
let records = 
    query {
        for t in context.SchemName.TableName do
        select ( t.Timestamp, t.Text )
    }

It wil fail with Oracle.ManagedDataAccess.Client.OracleException: 'ORA-00942: table or view does not exist' because it creates the query:

SELECT table_name.timestamp as "timestamp", table_name.text as "text" FROM SchemaName.table_name table_name

Which is interpreted by Oracle as something that cannot be found:

SELECT table_name.TIMESTAMP as "timestamp", table_name.TEXT as "text" FROM SCHEMANAME.TABLE_NAME table_name

Expected behavior
The schema object identifiers schema name, table name and column name should be quoted in statements when they were created as quoted identifiers. Preferably automatically otherwise with a flag like the one used in the Firebird solution (#431). The resulting query in this case should look like:

SELECT table_name."timestamp" as "timestamp", table_name."text" as "text" FROM "SchemaName"."table_name" table_name

Desktop (please complete the following information):

  • OS: windows 11
  • Oracle 12
  • SqlProvider 1.3.5

Libraries used for resolution

  • Oracle.ManagedDataAccess.dl (version 2.0.19.1)
  • System.Diagnostics.PerformanceCounter.dll (targeting netstandard2.0, version 6.0.0)
  • System.DirectoryService.dll (targeting netstandard2.0, version 5.0.0)
  • System.DirectoryService.Protocols.dll (targeting netstandard2.0, version 5.0.1)
  • System.Text.Json.dll (targeting netstandard2.0, version 6.0.0)
Dominant language
F#
Stars
627
Forks
147
Avg merge
2h 2m
Merged PRs (30d)
1

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from fsprojects/SQLProvider

All issues in fsprojects/SQLProvider

Similar issues

More Databases issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.