Oracle Support

[angkanas]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

engine = create_engine("oracle+cx_oracle://<user>:<password>@<dbserver>")


SQLModel.metadata.create_all(engine)

Description

sqlalchemy.exc.DatabaseError: (cx_Oracle:Database:Error) ORA-00906: missing left parenthesis
[SQL:
CREATE TABLE hero(
id INTERGER NOT NULL,
name VARCHAR2 NOT NULL,
secret_name VARCHAR2 NOT NULL,
age INTERGER,
PRIMARY KEY(id)
)
]

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.11.4

Additional Context

No response

[spartyhacker]

I had exactly the same issue. It is due to the fact that VARCHAR2 needs a length spec. However, I was not able to figure out how to use sqlmodel to do that. The workaround I found is: create the table in Oracle before using sqlmodel. That way the "create table" sql is not implemented hence avoided the error

[SonOfLilit]

Are you sure this is an SQLModel issue? It really looks like an SQLAlchemy issue. Can you show working SQLAlchemy code similar to this?

[sandstromviktor]

# WORKS (SQLAlchemy)
class MyTable(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    name = Column(String(10))  # Specify length here

# DOES NOT WORK (generates VARCHAR2 and not VARCHAR2(10 CHAR))
class MyTable(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(sa_column=String(10))

[sandstromviktor]

I found a solution. Described in #1133