Not generating correct Oracle SQL

[sandstromviktor]

This is an issue related to SQLModel and the open discussion (see below)
When creating a very simple table, the generated SQL code is incorrect. It produces VARCHAR2 and Oracle expects VARCHAR2(length). However, using SQLAlchemy, it works fine if you use for example Column(String(10)). Doing similar in SQLModel does not work and is probably related to the typing, which cast this incorrectly.

FROM DISCUSSION SECTION

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 # This creates the incorrect VARCHAR2 type in Oracle. Setting e.g., String(20) does not help
    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

Originally posted by @angkanas in #641

[sandstromviktor]

After a lot of searching, I found a solution. This works:

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(max_length=255)
    secret_name: str = Field(max_length=255)
    age: int | None = None

which generates the correct SQL

CREATE TABLE hero (
        id INTEGER NOT NULL, 
        name VARCHAR2(255 CHAR) NOT NULL, 
        secret_name VARCHAR2(255 CHAR) NOT NULL, 
        age INTEGER, 
        PRIMARY KEY (id)
)