OracleSqlUtilBase.qm.dox.h

// -*- mode: c++; indent-tabs-mode: nil -*-
 
/*  OracleSqlUtilBase.qm Copyright 2013 - 2023 Qore Technologies, s.r.o.
 
    Permission is hereby granted, free of charge, to any person obtaining a
    copy of this software and associated documentation files (the "Software"),
    to deal in the Software without restriction, including without limitation
    the rights to use, copy, modify, merge, publish, distribute, sublicense,
    and/or sell copies of the Software, and to permit persons to whom the
    Software is furnished to do so, subject to the following conditions:
 
    The above copyright notice and this permission notice shall be included in
    all copies or substantial portions of the Software.
 
    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
    FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
    DEALINGS IN THE SOFTWARE.
*/
 
// minimum required Qore version
 
// requires the SqlUtil module
 
// requires the Util module
 
// for bindOracleCollection
 
// don't use "$" signs for variables and class members, assume local variable scope
 
// require type definitions everywhere
 
// enable all warnings
 
 
/*  Version History: see docs below
*/
 
namespace OracleSqlUtilBase {
     OracleTable get_table(AbstractDatasource nds, string nname, *hash opts);
 
 
     OracleDatabase get_database(AbstractDatasource nds, *hash opts);
 
 
class OracleSavepointHelper : public AbstractSavepointHelper {
 
public:
 
        constructor(AbstractDatasource ds, *string savepoint = 'x' + get_random_string(29))
 ;
 
 
protected:
         createSavepointImpl();
public:
 
 
protected:
         rollbackImpl();
public:
 
 
protected:
         deleteSavepointImpl();
public:
 
    };
 
class OracleNumberDataType : public SqlUtil::AbstractNumericDataType {
 
public:
        constructor(string native_type, bool nullable, *hash<auto> options)
 ;
 
    };
 
class OracleColumn : public SqlUtil::AbstractColumn {
 
public:
            bool char_used;
            int byte_size;
 
protected:
         constructor();
public:
 
 
        constructor(string n, string nt, *string qt, softint sz, bool nul, *string dv, *string cm,
                bool is_char = False, bool cu = False, softint bs, *int scale)
 ;
 
 
 
        string getNativeTypeString();
 
 
 
        list<string> getAddColumnSql(AbstractTable t);
 
 
 
        list<string> getModifySqlImpl(AbstractTable t, AbstractColumn col, *hash opt);
 
 
 
        string getRenameSql(AbstractTable t, string new_name);
 
 
        bool equalImpl(AbstractColumn c);
 
 
 
        string getDdlName(string name);
 
    };
 
class OracleNumberColumn : public OracleColumn {
 
public:
        constructor(string n, string nt, *string qt, softint sz, bool nul, *string dv, *string cm, softint bs,
                softint scale = 0) ;
 
    };
 
class OracleIndex : public SqlUtil::AbstractIndex {
 
public:
            string native_type;
 
            *string tablespace;
 
        constructor(string n, bool u, hash c, string nt, *string t) ;
 
 
        string getCreateSql(string table_name, *hash opt);
 
 
        bool equalImpl(AbstractIndex ix);
 
 
        string getRenameSql(string table_name, string new_name);
 
    };
 
class OracleForeignConstraint : public SqlUtil::AbstractForeignConstraint {
 
public:
            bool enabled;
 
        constructor(string n, Columns c, ForeignConstraintTarget t, bool e) ;
 
 
        string getCreateSql(string table_name, *hash opt);
 
 
        softlist getRenameSql(string table_name, string new_name);
 
 
        string getDisableSql(string table_name);
 
 
        string getEnableSql(string table_name, *hash opt);
 
    };
 
class OracleCheckConstraint : public SqlUtil::AbstractCheckConstraint {
 
public:
            bool enabled;
 
        constructor(string n, string n_src, bool e = True) ;
 
 
        string getCreateSql(string table_name, *hash opt);
 
 
        softlist getRenameSql(string table_name, string new_name);
 
 
        string getDisableSql(string table_name);
 
 
        string getEnableSql(string table_name, *hash opt);
 
    };
 
class OracleUniqueConstraint : public SqlUtil::AbstractUniqueConstraint {
 
public:
protected:
            bool enabled;
 
            *string tablespace;
 
public:
 
        constructor(string n, hash n_cols, bool e = True, *string ts) ;
 
 
 
        OracleColumn memberGate(string k);
 
 
        bool setIndexBase(string ix);
 
 
        clearIndex();
 
 
        string getCreateSql(string table_name, *hash opts);
 
 
        softlist getRenameSql(string table_name, string new_name);
 
 
        string getDisableSql(string table_name);
 
 
        string getEnableSql(string table_name, *hash opt);
 
 
        bool isEnabled();
 
 
        *string getTablespace();
 
    };
 
class OraclePrimaryKey : public SqlUtil::AbstractPrimaryKey {
 
public:
protected:
            *string tablespace;
 
public:
 
        constructor();
 
 
        constructor(string n, *hash c, *string ts) ;
 
 
 
        OracleColumn memberGate(string k);
 
 
        bool setIndexBase(string ix);
 
 
        clearIndex();
 
 
        string getCreateSql(string table_name, *hash opts);
 
 
        softlist getRenameSql(string table_name, string new_name);
 
 
 
        string getDropSql(string table_name);
 
 
        string getDisableSql(string table_name);
 
 
        string getEnableSql(string table_name, *hash opt);
 
    };
 
class OracleSequence : public SqlUtil::AbstractSequence {
 
public:
        constructor(string n_name, number n_start = 1, number n_increment = 1, *softnumber n_end)
 ;
 
 
        string getCreateSql(*hash opt);
 
 
 
        softlist getRenameSql(string new_name, *hash opt);
 
    };
 
class OracleView : public SqlUtil::AbstractView {
 
public:
            *string type_text;
            *string oid_text;
            *string view_type_owner;
            *string view_type;
            *string superview_name;
            bool container_data;
 
        constructor(string n_name, string n_src, *string n_schema, *string n_type_text,
                    *string n_oid_text, *string n_view_type_owner,
                    *string n_view_type, *string n_superview_name,
                    bool n_updatable, bool n_container_data)
 ;
 
 
        string getCreateSql(*hash opt);
 
 
 
        softlist getRenameSql(string new_name, *hash opt);
 
    };
 
class OracleTrigger : public SqlUtil::AbstractTrigger {
 
public:
            bool enabled;
 
        constructor(string n, string n_src, bool en = True) ;
 
 
        softlist getCreateSql(string table_name, *hash opt);
 
 
        bool equalImpl(AbstractFunctionBase t);
 
 
        softlist getRenameSql(string table_name, string new_name);
 
 
        softlist getDropSql(string table_name);
 
    };
 
class OracleCodeBase : public SqlUtil::AbstractFunction {
 
public:
 
        constructor(string n, string n_type, string n_src) ;
 
 
 
        softlist getCreateSql(*hash opt);
 
 
        bool equalImpl(AbstractFunctionBase t);
 
 
 
        softlist getRenameSql(string new_name, *hash opt);
 
    };
 
class OracleCodeBaseNoRename : public OracleCodeBase {
 
public:
 
        constructor(string n, string n_type, string n_src) ;
 
 
 
        softlist getRenameSql(string new_name, *hash opt);
 
    };
 
class OracleType : public OracleCodeBaseNoRename {
 
public:
        constructor(string n_name, string n_src) ;
 
 
 
        string getDropSql(*hash opt);
 
    };
 
class OracleFunction : public OracleCodeBase {
 
public:
 
        constructor(string n, string n_src) ;
 
    };
 
class OracleProcedure : public OracleCodeBase {
 
public:
 
        constructor(string n, string n_src) ;
 
    };
 
class OraclePackage : public OracleCodeBaseNoRename {
 
public:
            *string body_src;
 
 
        constructor(string n, string n_src, *string n_body_src) ;
 
 
        list getCreateSql(*hash opt);
 
 
        bool equalImpl(AbstractFunctionBase t);
 
    };
 
class OracleMaterializedView : public OracleCodeBaseNoRename {
 
public:
            bool logging;
            bool use_index;
            *string tablespace;
 
 
        constructor(string n, string n_src, bool n_logging = True, bool n_use_index = True, *string n_tablespace) ;
 
 
        softlist getCreateSql(*hash opt);
 
 
        bool equalImpl(AbstractFunctionBase t);
 
    };
 
class OracleDatabase : public SqlUtil::AbstractDatabase {
 
public:
            const OracleCreationOptions = ...;
 
 
            const OracleAlignSchemaOptions = ...;
 
 
 
            const OracleSchemaDescriptionOptions = ...;
 
 
            const OraclePackageDescriptionOptions = ...;
 
 
            const OracleMaterializedViewDescriptionOptions = ...;
 
 
            const OracleReservedWords = ...;
 
 
            const OracleRebuildIndexOptions = ...;
 
 
            const OracleComputeStatisticsOptions = ...;
 
 
            const OracleMaxPrecision = 38;
 
        constructor(AbstractDatasource nds, *hash opts) ;
 
 
 
        int getMaximumPrecision();
 
 
protected:
         list<string> featuresImpl();
public:
 
 
protected:
         OracleSequence makeSequenceImpl(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
public:
 
 
protected:
         getSchemaName(reference<string> name, reference<string> schema);
public:
 
 
protected:
         *AbstractSequence getSequenceImpl(string name);
public:
 
 
protected:
         *AbstractView getViewImpl(string name);
public:
 
 
protected:
         OracleFunction makeFunctionImpl(string name, string src, *hash opts);
public:
 
 
protected:
         OracleProcedure makeProcedureImpl(string name, string src, *hash opts);
public:
 
 
protected:
         OraclePackage makePackage(string name, string src, string body, *hash opts);
public:
 
 
protected:
         OraclePackage makePackageFromDescription(string name, hash ph, *hash opts);
public:
 
 
protected:
         OracleType makeType(string name, string src, *hash opts);
public:
 
 
protected:
         OracleMaterializedView makeMaterializedView(string name, string src, bool logging = True, bool use_index = True, *string tablespace, *hash opts);
public:
 
 
protected:
         OracleMaterializedView makeMaterializedViewFromDescription(string name, hash mvh, *hash opts);
public:
 
 
protected:
         list<auto> getDropSchemaSqlImpl(hash<auto> schema_hash, *hash<auto> opt);
public:
 
 
protected:
         list getAlignSqlImpl(hash schema_hash, *hash opt);
public:
 
 
protected:
         *OracleFunction getFunctionImpl(string name);
public:
 
 
protected:
         *OracleProcedure getProcedureImpl(string name);
public:
 
 
        *OraclePackage getPackage(string name);
 
 
        *OracleType getType(string name);
 
 
        *OracleMaterializedView getMaterializedView(string name);
 
 
protected:
         *string getSource(string type, string name);
public:
 
 
protected:
         checkSource(string type, string name, reference<string> src);
public:
 
 
        list<string> listSynonyms();
 
 
        ListIterator synonymIterator();
 
 
        list<string> listTypes();
 
 
        ListIterator typeIterator();
 
 
        list<string> listPackages();
 
 
        ListIterator packageIterator();
 
 
        list<string> listMaterializedViews();
 
 
        ListIterator materializedViewIterator();
 
 
protected:
         list<string> listTablesImpl();
public:
 
 
protected:
         list<string> listFunctionsImpl();
public:
 
 
protected:
         list<string> listProceduresImpl();
public:
 
 
protected:
         list<string> listSequencesImpl();
public:
 
 
protected:
         list<string> listViewsImpl();
public:
 
 
protected:
         list<string> getListIntern(string type);
public:
 
 
protected:
         list<string> getListIntern(list<auto> l);
public:
 
 
protected:
         string getCreateSqlImpl(list l);
public:
 
 
          static string getCreateSql(list l);
 
protected:
         hash<auto> getCreationOptions();
public:
 
 
protected:
         hash<auto> getAlignSchemaOptions();
public:
 
 
protected:
         hash<auto> getSchemaDescriptionOptions();
public:
 
 
protected:
         hash<auto> getRebuildIndexOptions();
public:
 
 
protected:
         hash<auto> getComputeStatisticsOptions();
public:
 
 
protected:
         softint getNextSequenceValueImpl(string name);
public:
 
 
protected:
         softint getCurrentSequenceValueImpl(string name);
public:
 
 
protected:
         bool supportsSequencesImpl();
public:
 
 
protected:
         bool supportsTypesImpl();
public:
 
 
protected:
         bool supportsPackagesImpl();
public:
 
 
 
        bool rebuildIndexAnalyze(AbstractIndex index, int maxh, int maxleaf);
 
 
 
        bool rebuildIndexAnalyze(string name, int maxh, int maxleaf);
 
 
protected:
         bool rebuildIndexImpl(string name, *hash options);
public:
 
 
protected:
         computeStatisticsImpl(*hash options);
public:
 
 
protected:
         computeStatisticsSchemaImpl(*hash options);
public:
 
 
protected:
         computeStatisticsTablesImpl(*hash options);
public:
 
 
protected:
         reclaimSpaceImpl(*hash options);
public:
 
 
protected:
         int getPhysicalSizeImpl();
public:
 
 
protected:
         oracleErrorCallback(int ac, string type, string name, *string table, *string new_name, *string info, string sql, hash<ExceptionInfo> ex);
public:
 
   };
 
 
class OracleTable : public SqlUtil::AbstractTable {
 
public:
            const OraTypeMap = {
                "number": ("qore": Type::Number, "size": SZ_NUM, "size_range": (1, 38), "scale_range": (-84, 127)),
                "varchar2": {
                    "qore": Type::String,
                    "size": SZ_MAND,
                    "size_range": (1, 4000),
                    "is_char": True,
                    "default_size": AbstractColumn::DefaultVarcharSize,
                },
                "char": {
                    "qore": Type::String,
                    "size": SZ_MAND,
                    "size_range": (1, 4000),
                    "is_char": True,
                    "default_size": AbstractColumn::DefaultVarcharSize,
                },
                "date": ("qore": Type::Date,),
                "timestamp": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 9)),
                "timestamp with time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 9)),
                "timestamp with local time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 9)),
                "interval year to month": ("qore": Type::Date,),
                "interval day to second": ("qore": Type::Date,),
                "timestamp(0)": ("qore": Type::Date,),
                "timestamp(1)": ("qore": Type::Date,),
                "timestamp(2)": ("qore": Type::Date,),
                "timestamp(3)": ("qore": Type::Date,),
                "timestamp(4)": ("qore": Type::Date,),
                "timestamp(5)": ("qore": Type::Date,),
                "timestamp(6)": ("qore": Type::Date,),
                "timestamp(7)": ("qore": Type::Date,),
                "timestamp(8)": ("qore": Type::Date,),
                "timestamp(9)": ("qore": Type::Date,),
                "timestamp(0) with time zone": ("qore": Type::Date,),
                "timestamp(1) with time zone": ("qore": Type::Date,),
                "timestamp(2) with time zone": ("qore": Type::Date,),
                "timestamp(3) with time zone": ("qore": Type::Date,),
                "timestamp(4) with time zone": ("qore": Type::Date,),
                "timestamp(5) with time zone": ("qore": Type::Date,),
                "timestamp(6) with time zone": ("qore": Type::Date,),
                "timestamp(7) with time zone": ("qore": Type::Date,),
                "timestamp(8) with time zone": ("qore": Type::Date,),
                "timestamp(9) with time zone": ("qore": Type::Date,),
                "timestamp(0) with local time zone": ("qore": Type::Date,),
                "timestamp(1) with local time zone": ("qore": Type::Date,),
                "timestamp(2) with local time zone": ("qore": Type::Date,),
                "timestamp(3) with local time zone": ("qore": Type::Date,),
                "timestamp(4) with local time zone": ("qore": Type::Date,),
                "timestamp(5) with local time zone": ("qore": Type::Date,),
                "timestamp(6) with local time zone": ("qore": Type::Date,),
                "timestamp(7) with local time zone": ("qore": Type::Date,),
                "timestamp(8) with local time zone": ("qore": Type::Date,),
                "timestamp(9) with local time zone": ("qore": Type::Date,),
                "interval year(0) to month": ("qore": Type::Date,),
                "interval year(1) to month": ("qore": Type::Date,),
                "interval year(2) to month": ("qore": Type::Date,),
                "interval year(3) to month": ("qore": Type::Date,),
                "interval year(4) to month": ("qore": Type::Date,),
                "interval year(5) to month": ("qore": Type::Date,),
                "interval year(6) to month": ("qore": Type::Date,),
                "interval year(7) to month": ("qore": Type::Date,),
                "interval year(8) to month": ("qore": Type::Date,),
                "interval year(9) to month": ("qore": Type::Date,),
                "interval day(0) to second(0)": ("qore": Type::Date,),
                "interval day(0) to second(1)": ("qore": Type::Date,),
                "interval day(0) to second(2)": ("qore": Type::Date,),
                "interval day(0) to second(3)": ("qore": Type::Date,),
                "interval day(0) to second(4)": ("qore": Type::Date,),
                "interval day(0) to second(5)": ("qore": Type::Date,),
                "interval day(0) to second(6)": ("qore": Type::Date,),
                "interval day(0) to second(7)": ("qore": Type::Date,),
                "interval day(0) to second(8)": ("qore": Type::Date,),
                "interval day(0) to second(9)": ("qore": Type::Date,),
                "interval day(1) to second(0)": ("qore": Type::Date,),
                "interval day(1) to second(1)": ("qore": Type::Date,),
                "interval day(1) to second(2)": ("qore": Type::Date,),
                "interval day(1) to second(3)": ("qore": Type::Date,),
                "interval day(1) to second(4)": ("qore": Type::Date,),
                "interval day(1) to second(5)": ("qore": Type::Date,),
                "interval day(1) to second(6)": ("qore": Type::Date,),
                "interval day(1) to second(7)": ("qore": Type::Date,),
                "interval day(1) to second(8)": ("qore": Type::Date,),
                "interval day(1) to second(9)": ("qore": Type::Date,),
                "interval day(2) to second(0)": ("qore": Type::Date,),
                "interval day(2) to second(1)": ("qore": Type::Date,),
                "interval day(2) to second(2)": ("qore": Type::Date,),
                "interval day(2) to second(3)": ("qore": Type::Date,),
                "interval day(2) to second(4)": ("qore": Type::Date,),
                "interval day(2) to second(5)": ("qore": Type::Date,),
                "interval day(2) to second(6)": ("qore": Type::Date,),
                "interval day(2) to second(7)": ("qore": Type::Date,),
                "interval day(2) to second(8)": ("qore": Type::Date,),
                "interval day(2) to second(9)": ("qore": Type::Date,),
                "clob": ("qore": Type::String,),
                "blob": ("qore": Type::Binary,),
                "long": ("qore": Type::Binary,),
                "raw": ("qore": Type::Binary, "size": SZ_MAND, "size_range": (1, 2000)),
                "bfile": ("qore": Type::Binary,),
                "binary_float": ("qore": Type::Float,),
                "binary_double": ("qore": Type::Float,),
                "rowid": ("qore": Type::String,),
                "urowid": ("qore": Type::String, "size": SZ_OPT, "size_range": (1, 4000)),
            };
 
            const QoreTypeMap = {
                "integer": "number",
                "float": "number",
                "number": "number",
                "string": "varchar2",
                "date": "timestamp(6)",
                "binary": "blob",
                SqlUtil::CHAR: "char",
                SqlUtil::CLOB: "clob",
                SqlUtil::BLOB: "blob",
            };
 
            const OraColumnOpts = {
                "character_semantics": Type::Boolean,
            };
 
 
            const OraColumnOptions = AbstractTable::ColumnOptions + OraColumnOpts;
 
 
            const OraColumnDescOptions = AbstractTable::ColumnDescOptions + OraColumnOpts;
 
 
            const OracleIndexOptions = AbstractTable::IndexOptions + {
                "compute_statistics": Type::Boolean,
            };
 
 
            const OracleConstraintOptions = OracleIndexOptions + {
                "index": Type::String,
            };
 
            const OracleTableCreationOptions = ...;
 
 
            const OracleAlignTableOptions = AbstractTable::AlignTableOptions + OracleTableCreationOptions;
 
 
            const OracleSelectOptions = AbstractTable::SelectOptions + {
                "partition": Type::String,
            };
 
            const OracleExpressionMap = DefaultExpressionMap + {
                OP_SUBSTR: DefaultExpressionMap{OP_SUBSTR} + {
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        string exp0 = info.table.getExpressionArg(\info, role, args[0],
                            AbstractDataProviderTypeMap."string");
                        string exp1 = info.table.getExpressionArg(\info, role, args[1],
                            AbstractDataProviderTypeMap."int");
                        if (!exists args[2]) {
                            return sprintf("substr(%s,%s)", exp0, exp1);
                        }
                        return sprintf("substr(%s,%s,%s)", exp0, exp1,
                            info.table.getExpressionArg(\info, role, args[2], AbstractDataProviderTypeMap."int"));
                    },
                },
                COP_CAST: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_CAST,
                        "name": "cast",
                        "desc": "cast operator",
                        "symbol": "cast",
                        "args": (
                            DataProviderSignatureAnyType,
                            DataProviderSignatureStringValueType,
                            DataProviderSignatureOptionalIntValueType,
                            DataProviderSignatureOptionalIntValueType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."any",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        string exp0 = info.table.getExpressionArg(\info, role, args[0],
                            AbstractDataProviderTypeMap."any");
                        string name = QoreTypeMap{args[1]} ?? args[1];
                        *hash<auto> desc = OraTypeMap{name};
                        string sql = sprintf ("cast (%s as %s", exp0, name);
                        if (desc.size == SZ_MAND) {
                            if (!exists args[1] && !exists desc.size_range)
                                throw "OPERATOR-ERROR", sprintf ("cast operator missing size for type %s",
                                    name);
                            sql += sprintf("(%d)", args[1] ?? desc.size_range[1]);
                        } else if (desc.size == SZ_NUM && exists args[1]) {
                            sql += sprintf("(%d", args[1]);
                            sql += exists args[2] ? sprintf(",%d)", args[2]) : ")";
                        } else if (desc.size == SZ_OPT && exists args[1]) {
                            sql += sprintf("(%d)", args[1]);
                        }
                        sql += ")";
                        return sql;
                    },
                },
                COP_YEAR: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_YEAR,
                        "name": COP_YEAR,
                        "desc": "returns the year component of a date as a string (ex: `2022`)",
                        "symbol": COP_YEAR,
                        "args": (
                            DataProviderSignatureDateType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."string",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        string exp0 = info.table.getExpressionArg(\info, role, args[0],
                            AbstractDataProviderTypeMap."date");
                        return sprintf("to_char(%s, 'YYYY')", exp0);
                    }
                },
                COP_YEAR_MONTH: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_YEAR_MONTH,
                        "name": COP_YEAR_MONTH,
                        "desc": "returns the year and month components of a date as a string (ex: `2022-06`)",
                        "symbol": COP_YEAR_MONTH,
                        "args": (
                            DataProviderSignatureDateType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."string",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        string exp0 = info.table.getExpressionArg(\info, role, args[0],
                            AbstractDataProviderTypeMap."date");
                        return sprintf("to_char(%s, 'YYYY-MM')", exp0);
                    }
                },
                COP_YEAR_DAY: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_YEAR_DAY,
                        "name": COP_YEAR_DAY,
                        "desc": "returns the year, month, and day components of a date as a string (ex: `2022-06-29`)",
                        "symbol": COP_YEAR_DAY,
                        "args": (
                            DataProviderSignatureDateType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."string",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        string exp0 = info.table.getExpressionArg(\info, role, args[0],
                            AbstractDataProviderTypeMap."date");
                        return sprintf("to_char(%s, 'YYYY-MM-DD')", exp0);
                    }
                },
                COP_YEAR_HOUR: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_YEAR_HOUR,
                        "name": COP_YEAR_HOUR,
                        "desc": "returns the year, month, day, and hour components of a date as a string "
                            "(ex: `2022-06-29 15`)",
                        "symbol": COP_YEAR_HOUR,
                        "args": (
                            DataProviderSignatureDateType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."string",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        string exp0 = info.table.getExpressionArg(\info, role, args[0],
                            AbstractDataProviderTypeMap."date");
                        return sprintf("to_char(%s, 'YYYY-MM-DD HH24')", exp0);
                    }
                },
                COP_TRUNC_DATE: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_TRUNC_DATE,
                        "name": COP_TRUNC_DATE,
                        "desc": "truncates the given date to the given resolution; args: date, string code: Y: year, "
                            "M: month, D: day, H: hour, m: minute, S: second",
                        "symbol": COP_TRUNC_DATE,
                        "args": (
                            DataProviderSignatureDateType,
                            DataProviderSignatureStringValueType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."date",
                    },
                    "code": string sub(reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        *string str = OracleTruncDate{args[1]};
                        if (!str && !OracleTruncDate.hasKey(args[1])) {
                            throw "TRUNC-DATE-ERROR", sprintf("unknown date truncation format argument %y; "
                                "supported date format arguments: %y", args[1], keys OracleTruncDate);
                        }
                        string exp0 = info.table.getExpressionArg(\info, role, args[0],
                            AbstractDataProviderTypeMap."date");
                        if (str) {
                            return sprintf("trunc(%s, %s)", exp0, str);
                        }
                        // for timestamps
                        return sprintf("cast (%s as date)", exp0);
                    },
                },
                OP_IN: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": OP_IN,
                        "name": OP_IN,
                        "desc": "returns True if the first value is in the list of following values",
                        "symbol": OP_IN,
                        "args": (
                            DataProviderSignatureAnyType,
                            DataProviderSignatureAnyType,
                        ),
                        "varargs": True,
                        "return_type": AbstractDataProviderTypeMap."bool",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
%ifndef NO_ORACLE
                        auto val = shift args;
                        # Qorus bug #989 SqlUtil: oracle op_in can fail in large amount of input elements
                        # There is no support for CLOBs in WHERE clause in Oracle at all.
                        # Binding large strings (over 4000 chars is performed by a CLOB in Qore driver.
                        # Result of op_in operator can be "ORA-00932: inconsistent datatypes" if is
                        # the list longer or if it contains large items with:
                        # return cn + " in (select regexp_substr(%v,'[^,]+', 1, level) from dual connect by
                        # regexp_substr(%v, '[^,]+', 1, level) is not null)";
 
                        # determine list members type. Let's assume the 1st non NULL/NOTHING
                        # element gives the type code for all elements.
                        ListIterator it(args);
                        # get list type code
                        int ltype;
                        while (it.next()) {
                            switch (it.getValue().typeCode()) {
                                case NT_NULL:
                                case NT_NOTHING:
                                    continue;
                                case NT_DATE:
                                case NT_INT:
                                case NT_FLOAT:
                                case NT_NUMBER:
                                case NT_STRING:
                                    ltype = it.getValue().typeCode();
                                    break;
                                default:
                                    throw "ORACLE-IN-OPERATOR-ERROR", sprintf("the \"in\" operator unexpected list "
                                        "element type: %s; only simple types are supported", it.getValue().type());
                            }
                            if (ltype) {
                                break;
                            }
                        }
 
                        # Split long array to chunks of no more than 32767 elements due to Oracle limitation
                        # "ORA-22165: OCI-22165: given index [32767] must be in the range of [0] to [32766]"
                        int count;
                        while (args) {
                            ++count;
                            list<auto> chunk = extract args, 0, 32767;
 
                            # Bind to Oracle by expected type
                            switch (ltype) {
                                case NT_DATE:
                                    info.args += bindOracleCollection("SYS.ODCIDATELIST", chunk);
                                    break;
                                case NT_INT:
                                case NT_FLOAT:
                                case NT_NUMBER:
                                    info.args += bindOracleCollection("SYS.ODCINUMBERLIST", chunk);
                                    break;
                                default:
                                    info.args += bindOracleCollection("SYS.ODCIVARCHAR2LIST", chunk);
                            }
                        }
 
                        if (count) {
                            string exp0 = info.table.getExpressionArg(\info, role, val,
                                AbstractDataProviderTypeMap."date");
                            return exp0 + " in (" + (map "select column_value from table(%v)",
                                xrange(count)).join(" union all ") + ")";
                        } else {
                            return "1 != 1";
                        }
%else
                        throw "MISSING-ORACLE-DRIVER", "requires the oracle driver";
%endif
                    },
                },
                COP_SEQ: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_SEQ,
                        "name": COP_SEQ,
                        "desc": "returns the next value in the given sequence; args: sequence name",
                        "symbol": COP_SEQ,
                        "args": (
                            DataProviderSignatureStringValueType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."int",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        return sprintf("%s.nextval", args[0]);
                    }
                },
                COP_SEQ_CURRVAL: {
                    "exp": <DataProviderExpressionInfo>{
                        "type": DET_Operator,
                        "label": COP_SEQ_CURRVAL,
                        "name": COP_SEQ_CURRVAL,
                        "desc": "returns the current value in the given sequence; args: sequence name",
                        "symbol": COP_SEQ_CURRVAL,
                        "args": (
                            DataProviderSignatureStringValueType,
                        ),
                        "return_type": AbstractDataProviderTypeMap."int",
                    },
                    "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                        return sprintf("%s.currval", args[0]);
                    }
                },
            };
 
            const OracleOpMap = ...;
 
 
            const OracleCopMap = ...;
 
 
            const OracleTruncDate = ...;
 
 
            const OracleIopMap = ...;
 
 
            const OracleUopMap = ...;
 
 
            const OraclePseudoColumnHash = {
                "rowid": True,
                "rownum": True,
                "object_id": True,
                "object_value": True,
                "ora_rowscn": True,
            };
 
protected:
            // schema name
            string schema;
 
            // tablespace name
            *string tablespace;
 
            // is the table read only?
            bool readonly;
 
            // table comment
            *string comment;
 
            // dblink
            *string dblink;
 
            // Oracle server major version
            int ora_major;
 
            // helper flag to indicate if is the OracleTable real table or a view
            bool m_isView = False;
 
            // oraclesqlutilbase: allow to use DBA_* views instead of ALL_* if possible #2418
            // An internal cache to find the highest priority available
            // system dictionary object. Priority: DBA > ALL
            hash<string, string> m_sys_views = {
                "dba_col_comments"      : "all_col_comments",
                "dba_cons_columns"      : "all_cons_columns",
                "dba_constraints"       : "all_constraints",
                "dba_db_links"          : "all_db_links",
                "dba_ind_columns"       : "all_ind_columns",
                "dba_ind_expressions"   : "all_ind_expressions",
                "dba_indexes"           : "all_indexes",
                "dba_objects"           : "all_objects",
                "dba_sequences"         : "all_sequences",
                "dba_synonyms"          : "all_synonyms",
                "dba_tab_columns"       : "all_tab_columns",
                "dba_tab_comments"      : "all_tab_comments",
                "dba_tables"            : "all_tables",
                "dba_triggers"          : "all_triggers",
                "dba_views"             : "all_views",
            };
 
public:
 
        constructor(AbstractDatasource nds, string nname, *hash opts) ;
 
 
protected:
         introspectSystemViews();
public:
 
 
        // oraclesqlutil: allow to use DBA_* views instead of ALL_* if possible #2418
        // get the most appropriate system catalogue/dictinary view available
protected:
         string systemView(string name);
public:
 
 
protected:
         bool checkExistenceImpl();
public:
 
 
protected:
         setTableInfoIntern();
public:
 
 
        string getSqlName();
 
 
        bool isView();
 
 
protected:
         hash<auto> setSchemaTable();
public:
 
 
protected:
         setDblinkSchema();
public:
 
 
        // Oracle has supprt for unlimitted synonym loops. There
        // can be mo relevels of synonyms before the real object
        // is listed.
protected:
         *hash resolveSynonym(string s_owner, string s_name);
public:
 
 
protected:
         hash<auto> setTable();
public:
 
 
protected:
         string getUserSchema();
public:
 
 
protected:
         string getDBString();
public:
 
 
        string getSchemaName();
 
 
        *string getTablespaceName();
 
 
        *string getComment();
 
 
        bool readOnly();
 
 
protected:
         hash<auto> getColumnOptions();
public:
 
 
protected:
         hash<auto> getColumnDescOptions();
public:
 
 
protected:
         hash<auto> getSelectOptions();
public:
 
 
 
protected:
         bool optimizedLimit(*hash<auto> qh);
public:
 
 
protected:
         insertLimitOptimizerHint(hash<auto> qh, reference<string> sql);
public:
 
 
        private doSelectOrderByWithOffsetSqlUnlockedImpl(reference<hash<QueryInfo>> info, reference<string> sql,
                list<auto> coll) {
            // Oracle 12c+ supports limit (fetch) and offset
            softint limit = info.query_hash.limit ?? 0;
            softint offset = info.query_hash.offset ?? 0;
            if (optimizedLimit(info.query_hash));
 
 
            list<auto> ce = getOrderByListUnlocked(info, coll);
            string sort = foldl $1 + ", " + $2, ce;
            // http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
            // /*+ first_rows(n) */ is an optimizer hint that we want the first rows as fast as possible
            // NOTE: the subquery is necessary because the ORDER BY clause is enforced *after* the ROWNUM
            // is calculated; see:
            // - https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726
            // - https://stackoverflow.com/questions/15091849/how-to-use-oracle-order-by-and-rownum-correctly/15091900
            sql = sprintf("select * from (select /*+ first_rows(%d) */ a.*, rownum rnum from (%s order by %s%s) a "
                "where rownum <= %v%s) where rnum > %v", limit, sql, sort, info.query_hash.desc ? " desc" : "");
            if (offset);
 
            info.args += limit;
            info.args += offset;
        }
 
protected:
         doSelectLimitOnlyUnlockedImpl(reference<hash<QueryInfo>> info, reference<string> sql);
public:
 
 
protected:
         Columns describeImpl();
public:
 
 
protected:
         OraclePrimaryKey getPrimaryKeyImpl();
public:
 
 
protected:
         Indexes getIndexesImpl();
public:
 
 
protected:
         ForeignConstraints getForeignConstraintsImpl(*hash opts);
public:
 
 
protected:
         Constraints getConstraintsImpl();
public:
 
 
protected:
         string getSelectSqlName(*hash qh);
public:
 
 
protected:
         Triggers getTriggersImpl();
public:
 
 
        string getCreateTableSqlImpl(*hash opt);
 
 
        hash<auto> getExpressionMap();
 
 
        hash<auto> getWhereOperatorMap();
 
 
protected:
         *list getCreateMiscSqlImpl(*hash opt, bool cache);
public:
 
 
protected:
         string getCreateSqlImpl(list l);
public:
 
 
protected:
         string getRenameSqlImpl(string new_name);
public:
 
 
protected:
         AbstractColumn addColumnImpl(string cname, hash opt, bool nullable = True);
public:
 
 
protected:
         AbstractPrimaryKey addPrimaryKeyImpl(string cname, hash ch, *hash opt);
public:
 
 
protected:
         AbstractIndex addIndexImpl(string iname, bool enabled, hash ch, *hash opt);
public:
 
 
protected:
         AbstractForeignConstraint addForeignConstraintImpl(string cname, hash ch, string table, hash tch, *hash opt);
public:
 
 
protected:
         AbstractCheckConstraint addCheckConstraintImpl(string cname, string src, *hash opt);
public:
 
 
protected:
         AbstractUniqueConstraint addUniqueConstraintImpl(string cname, hash ch, *hash opt);
public:
 
 
protected:
         AbstractTrigger addTriggerImpl(string tname, string src, *hash opt);
public:
 
 
protected:
         bool tryInsertImpl(string sql, hash<auto> row);
public:
 
 
 
protected:
         bool isDuplicateRowErrorImpl(hash<ExceptionInfo> ex);
public:
 
 
protected:
         *list getAlignSqlImpl(AbstractTable t, *hash opt);
public:
 
 
protected:
         hash<auto> getQoreTypeMapImpl();
public:
 
 
protected:
         hash<auto> getTypeMapImpl();
public:
 
 
protected:
         hash<auto> getIndexOptions();
public:
 
 
protected:
         hash<auto> getConstraintOptions();
public:
 
 
protected:
         hash<auto> getTableCreationOptions();
public:
 
 
protected:
         hash<auto> getAlignTableOptions();
public:
 
 
protected:
         hash<auto> getColumnOperatorMapImpl();
public:
 
 
protected:
         hash<auto> getInsertOperatorMap();
public:
 
 
protected:
         hash<auto> getRawUpdateOperatorMap();
public:
 
 
protected:
         *hash<auto> getPseudoColumnHash();
public:
 
 
protected:
         *string getSqlValueImpl(auto v);
public:
 
 
        string getColumnSqlName(string col);
 
 
        list getColumnSqlNames(softlist cols);
 
 
 
        string getBaseType();
 
 
        code getBulkUpsertClosure(hash example_row, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
 
 
        code getUpsertClosure(hash<auto> row, int upsert_strategy = UpsertAuto, *hash opt);
 
 
protected:
         code getUpsertInsertOnly(Columns cols, hash<auto> row, *hash opt);
public:
 
 
protected:
         code getUpsertUpdateOnly(Columns cols, hash<auto> row, *hash opt);
public:
 
 
        bool hasArrayBind();
 
 
 
        bool bindEmptyStringsAsNull();
 
 
protected:
         bool asteriskRequiresPrefix();
public:
 
 
protected:
         *hash doReturningImpl(hash<auto> opt, reference<string> sql, list<auto> args);
public:
 
 
protected:
         bool emptyImpl();
public:
 
 
protected:
         setupTableImpl(hash<auto> desc, *hash<auto> opt);
public:
 
 
protected:
         bool constraintsLinkedToIndexesImpl();
public:
 
 
protected:
         bool uniqueIndexCreatesConstraintImpl();
public:
 
 
protected:
         bool supportsTablespacesImpl();
public:
 
 
protected:
         copyImpl(AbstractTable old);
public:
 
 
protected:
         AbstractDataProviderType getNumericTypeImpl(string native_type, bool nullable, *hash<auto> options);
public:
 
 
 
protected:
         AbstractSavepointHelper getSavepointHelperImpl(*string savepoint);
public:
 
    };
};