Skip to content

Instantly share code, notes, and snippets.

@omansak

omansak/oracle-generate-class-from-table.sql

Last active April 23, 2024 09:03
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save omansak/f19eefffd2d639ac72a1f4b506d8471a to your computer and use it in GitHub Desktop.
Save omansak/f19eefffd2d639ac72a1f4b506d8471a to your computer and use it in GitHub Desktop.
Download ZIP
Oracle DB Generate Class From Table
Raw
oracle-generate-class-from-table.sql
/*
______________________________________________________________________
| ORACLE DATABASE DATA TYPE | NET TYPE ALIAS | NETDATATYPE |
|----------------------------|----------------|----------------------|
| NUMBER1 | BOOL | SYSTEM.BOOLEAN |
| NUMBER2TONUMBER4 | BYTE | SYSTEM.BYTE |
| NUMBER5 | SHORTINT16 | SYSTEM.INT16 |
| NUMBER6TONUMBER10 | INTINT32 | SYSTEM.INT32 |
| NUMBER11TONUMBER19 | LONGINT64 | SYSTEM.INT64 |
| NUMBERGT19 | DECIMAL | SYSTEM.DECIMAL |
| NUMBERPS | DECIMAL | SYSTEM.DECIMAL |
| NUMBER | DECIMAL | SYSTEM.DECIMAL |
| BINARY_FLOAT | FLOAT | SYSTEM.FLOAT |
| BINARY_DOUBLE | DOUBLE | SYSTEM.DOUBLE |
| TIMESTAMP | DATETIME | SYSTEM.DATETIME |
| TIMESTAMPWITHTIMEZONE | DATETIMEOFFSET | SYSTEM.DATETIMEOFFSET |
| TIMESTAMPWITHLOCALTIMEZONE | DATETIMEOFFSET | SYSTEM.DATETIMEOFFSET |
| DATE | DATE | SYSTEM.DATE |
| INTERVALDAYTOSECOND | TIMESPAN | SYSTEM.TIMESPAN |
| INTERVALYEARTOMONTH | STRING | SYSTEM.STRING |
| VARCHAR2 | STRING | SYSTEM.STRING |
| JSON | STRING | SYSTEM.STRING |
| NVARCHAR2 | STRING | SYSTEM.STRING |
| CHAR | STRING | SYSTEM.STRING |
| NCHAR | STRING | SYSTEM.STRING |
| CLOB | STRING | SYSTEM.STRING |
| NCLOB | STRING | SYSTEM.STRING |
| RAW | BYTE | SYSTEM.BYTE |
| BLOB | BYTE | SYSTEM.BYTE |
| XMLTYPE | STRING | SYSTEM.STRING |
| ROWID | STRING | SYSTEM.STRING |
| UROWID | STRING | SYSTEM.STRING |
| LONG | STRING | SYSTEM.STRING |
| BFILE | BYTE | SYSTEM.BYTE |
| LONGRAW | BYTE | SYSTEM.BYTE |
______________________________________________________________________
*/
--https://docs.oracle.com/en/database/oracle/oracle-database/21/odpnt/EFCoreREDataTypeMapping.html#GUID-4FF3A9A3-AE49-431B-A4FB-31F8C53FCCF5
WITH PROPERTIES AS
(
SELECT
CASE
WHEN C.COLUMN_NAME IN
(
SELECT COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME =( SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE Upper(TABLE_NAME) = Upper(C.TABLE_NAME) AND CONSTRAINT_TYPE = 'P')
)
THEN '[Key]' || CHR(10)
ELSE ''
END
||
CASE
WHEN C.IDENTITY_COLUMN = 'YES' THEN '[DatabaseGenerated(DatabaseGeneratedOption.Identity)]' || CHR(10)
ELSE ''
END
||
CASE
WHEN C.NULLABLE = 'N' THEN '[Required]' || CHR(10)
ELSE ''
END
||
CASE
WHEN C.DATA_TYPE = 'VARCHAR2' THEN '[StringLength(' || C.DATA_LENGTH || ')]' || CHR(10)
ELSE ''
END
|| '[Column("' || C.COLUMN_NAME || '", TypeName = "' || C.DATA_TYPE
||
CASE
WHEN C.DATA_TYPE IN ('VARCHAR','VARCHAR2') THEN (
CASE
WHEN C.DATA_LENGTH IS NOT NULL
AND C.DATA_LENGTH > 0 THEN '(' || C.DATA_LENGTH ||')'
ELSE ''
END)
WHEN C.DATA_TYPE IN ('NUMBER') THEN (
CASE
WHEN C.DATA_PRECISION IS NOT NULL
AND C.DATA_LENGTH > 0
THEN '('|| C.DATA_PRECISION || ( CASE WHEN C.DATA_SCALE IS NOT NULL THEN ',' || C.DATA_SCALE ELSE '' END ) || ')'
ELSE ''
END)
ELSE ''
END
|| '",Order = ' || C.COLUMN_ID || ')]' || CHR(10)
|| 'public ' || CASE WHEN C.NULLABLE = 'N' AND C.IDENTITY_COLUMN <> 'YES' THEN 'required ' ELSE '' END
||
CASE
WHEN C.DATA_TYPE = 'CHAR' THEN 'string'
WHEN C.DATA_TYPE = 'BOOLEAN' THEN 'bool'
WHEN C.DATA_TYPE = 'DECIMAL' THEN 'decimal'
WHEN C.DATA_TYPE = 'INTEGER' THEN 'int'
WHEN C.DATA_TYPE = 'INT' THEN 'int'
WHEN C.DATA_TYPE = 'LONG' THEN 'string'
WHEN C.DATA_TYPE = 'BINARY_FLOAT' THEN 'float'
WHEN C.DATA_TYPE = 'BINARY_DOUBLE' THEN 'double'
WHEN C.DATA_TYPE = 'JSON' THEN 'string'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE IS NULL THEN 'decimal'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE > 0 THEN 'decimal'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION >= 2 AND C.DATA_PRECISION <= 4 THEN 'byte'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION = 1 THEN 'bool' /*CARE*/
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION <= 5 THEN 'short'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION <= 10 THEN 'int'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION <= 19 THEN 'long'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION > 19 THEN 'decimal'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION IS NULL THEN 'decimal'
WHEN C.DATA_TYPE = 'VARCHAR' THEN 'string'
WHEN C.DATA_TYPE = 'VARCHAR2' THEN 'string'
WHEN C.DATA_TYPE = 'CLOB' THEN 'string'
WHEN C.DATA_TYPE = 'ROWID' THEN 'string'
WHEN C.DATA_TYPE = 'FLOAT' THEN 'decimal'
WHEN C.DATA_TYPE = 'BLOB' THEN 'byte[]'
WHEN C.DATA_TYPE LIKE '%TIMESTAMP%' THEN 'DateTime'
WHEN C.DATA_TYPE = 'DATE' THEN 'DateTime'
ELSE 'object'
END
||
CASE
WHEN C.NULLABLE = 'Y' THEN '? ' ELSE ' '
END
|| REPLACE(INITCAP(REGEXP_REPLACE(C.COLUMN_NAME,'[_^\-\.\#\$]',' ')),' ','')
|| ' {get;set;}' || CHR(10) AS CSHARP_PROPERTY,
C.TABLE_NAME,
C.COLUMN_NAME,
C.COLUMN_ID
FROM ALL_TAB_COLUMNS c
WHERE C.TABLE_NAME IN ('TABLE_NAME') /*Table Names*/
ORDER BY COLUMN_ID)
SELECT TABLE_NAME,
'[Table("'
|| TABLE_NAME
|| '")]'
|| CHR(10)
|| 'public class '
|| REPLACE(INITCAP(REGEXP_REPLACE(TABLE_NAME,'[_^\-\.\#\$]',' ')),' ','')
|| '{'
|| CHR(10)
|| REPLACE(RTRIM(XMLAGG(XMLELEMENT(E,CSHARP_PROPERTY,CHR(10)).EXTRACT('//text()') ORDER BY COLUMN_ID).GETCLOBVAL(),','),CHR(38)
||'quot;','"')
|| '}'
|| CHR(10) AS "CODE"
FROM PROPERTIES
GROUP BY TABLE_NAME
@omansak
Copy link
Author

omansak commented Mar 22, 2024

.NET 5 and before


/*

https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/EFCoreREDataTypeMapping.html

______________________________________________________________________
| ORACLE DATABASE DATA TYPE  | NET TYPE ALIAS | NETDATATYPE           |
|----------------------------|----------------|-----------------------|
| NUMBER1                    | BOOL           | SYSTEM.BOOLEAN        |
| NUMBER2TONUMBER4           | BYTE           | SYSTEM.BYTE           |
| NUMBER5                    | SHORTINT16     | SYSTEM.INT16          |
| NUMBER6TONUMBER10          | INTINT32       | SYSTEM.INT32          |
| NUMBER11TONUMBER19         | LONGINT64      | SYSTEM.INT64          |
| NUMBERGT19                 | DECIMAL        | SYSTEM.DECIMAL        |
| NUMBERPS                   | DECIMAL        | SYSTEM.DECIMAL        |
| NUMBER                     | DECIMAL        | SYSTEM.DECIMAL        |
| BINARY_FLOAT               | FLOAT          | SYSTEM.FLOAT          |
| BINARY_DOUBLE              | DOUBLE         | SYSTEM.DOUBLE         |
| BOOLEAN                    | BOOL           | System.Boolean        |
| TIMESTAMP                  | DATETIME       | SYSTEM.DATETIME       |
| TIMESTAMPWITHTIMEZONE      | DATETIMEOFFSET | SYSTEM.DATETIMEOFFSET |
| TIMESTAMPWITHLOCALTIMEZONE | DATETIMEOFFSET | SYSTEM.DATETIMEOFFSET |
| DATE                       | DATE           | SYSTEM.DATE           |
| INTERVALDAYTOSECOND        | TIMESPAN       | SYSTEM.TIMESPAN       |
| INTERVALYEARTOMONTH        | STRING         | SYSTEM.STRING         |
| VARCHAR2                   | STRING         | SYSTEM.STRING         |
| JSON                       | STRING         | SYSTEM.STRING         |
| NVARCHAR2                  | STRING         | SYSTEM.STRING         |
| CHAR                       | STRING         | SYSTEM.STRING         |
| NCHAR                      | STRING         | SYSTEM.STRING         |
| CLOB                       | STRING         | SYSTEM.STRING         |
| NCLOB                      | STRING         | SYSTEM.STRING         |
| RAW                        | byte[]         | SYSTEM.BYTE           |
| BLOB                       | byte[]         | SYSTEM.BYTE           |
| XMLTYPE                    | STRING         | SYSTEM.STRING         |
| ROWID                      | STRING         | SYSTEM.STRING         |
| UROWID                     | STRING         | SYSTEM.STRING         |
| LONG                       | STRING         | SYSTEM.STRING         |
| BFILE                      | BYTE[]         | SYSTEM.BYTE           |
| LONGRAW                    | BYTE[]         | SYSTEM.BYTE           |
______________________________________________________________________
*/
WITH PROPERTIES AS
(
         SELECT
                  CASE
                           WHEN C.COLUMN_NAME IN
                                    (
                                           SELECT COLUMN_NAME
                                           FROM   ALL_CONS_COLUMNS
                                           WHERE  CONSTRAINT_NAME =( SELECT CONSTRAINT_NAME FROM   ALL_CONSTRAINTS WHERE  Upper(TABLE_NAME) = Upper(C.TABLE_NAME) AND CONSTRAINT_TYPE = 'P')
                                    ) 
                           THEN '[Key]' || CHR(10)
                           ELSE ''
                  END
                           ||
                  CASE
                            WHEN C.IDENTITY_COLUMN = 'YES' THEN '[DatabaseGenerated(DatabaseGeneratedOption.Identity)]' || CHR(10)
                            ELSE ''
                  END  
                           ||
                  CASE
                           WHEN C.NULLABLE = 'N' THEN '[Required]' || CHR(10)
                           ELSE ''
                  END
                           ||
                  CASE
                           WHEN C.DATA_TYPE = 'VARCHAR2' THEN '[StringLength(' || C.DATA_LENGTH || ')]' || CHR(10)
                           ELSE ''
                  END
                           || '[Column("' || C.COLUMN_NAME || '", TypeName = "' || C.DATA_TYPE
                           ||
                  CASE
                           WHEN C.DATA_TYPE IN ('VARCHAR','VARCHAR2') THEN (
                                    CASE
                                             WHEN C.DATA_LENGTH IS NOT NULL
                                             AND  C.DATA_LENGTH > 0 THEN '(' || C.DATA_LENGTH ||')'
                                             ELSE ''
                                    END)
                           WHEN C.DATA_TYPE IN ('NUMBER') THEN (
                                    CASE
                                             WHEN C.DATA_PRECISION IS NOT NULL
                                             AND  C.DATA_LENGTH > 0 
                                             THEN '('|| C.DATA_PRECISION || ( CASE WHEN C.DATA_SCALE IS NOT NULL THEN ',' || C.DATA_SCALE ELSE '' END ) || ')'
                                             ELSE ''
                                    END)
                           ELSE ''
                  END
                           || '",Order = ' || C.COLUMN_ID || ')]' || CHR(10)
                           || 'public '
                           ||
                  CASE
                           WHEN C.DATA_TYPE = 'CHAR' THEN 'string'
                           WHEN C.DATA_TYPE = 'BOOLEAN' THEN 'bool'
                           WHEN C.DATA_TYPE = 'DECIMAL' THEN 'decimal'
                           WHEN C.DATA_TYPE = 'INTEGER' THEN 'int'
                           WHEN C.DATA_TYPE = 'INT' THEN 'int'
                           WHEN C.DATA_TYPE = 'LONG' THEN 'string'
                           WHEN C.DATA_TYPE = 'BINARY_FLOAT' THEN 'float'
                           WHEN C.DATA_TYPE = 'BINARY_DOUBLE' THEN 'double'
                           WHEN C.DATA_TYPE = 'JSON' THEN 'string'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE IS NULL THEN 'decimal'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE > 0 THEN 'decimal'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION >= 2 AND C.DATA_PRECISION <= 4 THEN 'byte'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION = 1 THEN 'bool' /*CARE*/
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION <= 5 THEN 'short'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION <= 10 THEN 'int'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION <= 19 THEN 'long'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION > 19 THEN 'decimal'
                           WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 AND C.DATA_PRECISION IS NULL THEN 'decimal'
                           WHEN C.DATA_TYPE = 'VARCHAR' THEN 'string'
                           WHEN C.DATA_TYPE = 'VARCHAR2' THEN 'string'
                           WHEN C.DATA_TYPE = 'CLOB' THEN 'string'
                           WHEN C.DATA_TYPE = 'ROWID' THEN 'string'
                           WHEN C.DATA_TYPE = 'FLOAT' THEN 'decimal'
                           WHEN C.DATA_TYPE = 'BLOB' THEN 'byte[]'                
                           WHEN C.DATA_TYPE = 'TIMESTAMP' THEN 'DateTime'
                           WHEN C.DATA_TYPE LIKE 'TIMESTAMPWITH%' THEN 'DATETIMEOFFSET'
                           WHEN C.DATA_TYPE = 'DATE' THEN 'DateTime'
                           ELSE 'object'
                  END
                           ||
                  CASE
                           WHEN C.NULLABLE = 'Y'
                           AND      NOT (C.DATA_TYPE = 'VARCHAR2' OR C.DATA_TYPE = 'VARCHAR' OR C.DATA_TYPE = 'CHAR') THEN '? '
                           ELSE ' '
                  END
                           || REPLACE(INITCAP(REGEXP_REPLACE(C.COLUMN_NAME,'[_^\-\.\#\$]',' ')),' ','')
                           || ' {get;set;}' AS CSHARP_PROPERTY,
                  C.TABLE_NAME,
                  C.COLUMN_NAME,
                  C.COLUMN_ID
         FROM     ALL_TAB_COLUMNS c
         WHERE    C.TABLE_NAME IN ('TABLE_NAME') /*Table Names*/
         ORDER BY COLUMN_ID)
SELECT   TABLE_NAME,
         '[Table("'
                  || TABLE_NAME
                  || '")]'
                  || CHR(10)
                  || 'public class '
                  || REPLACE(INITCAP(REGEXP_REPLACE(TABLE_NAME,'[_^\-\.\#\$]',' ')),' ','')
                  || '{'
                  || CHR(10)
                  || REPLACE(RTRIM(XMLAGG(XMLELEMENT(E,CSHARP_PROPERTY,CHR(10)).EXTRACT('//text()') ORDER BY COLUMN_ID).GETCLOBVAL(),','),CHR(38)
                  ||'quot;','"')
                  || '}'
                  || CHR(10) AS "CODE"
FROM     PROPERTIES
GROUP BY TABLE_NAME

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment