Qore OracleSqlUtil Module Reference  1.2.3
OracleSqlUtil.qm.dox.h
1 // -*- mode: c++; indent-tabs-mode: nil -*-
2 // @file OracleSqlUtil.qm Qore user module for working with Oracle SQL data
3 
4 /* OracleSqlUtil.qm Copyright 2013 - 2017 Qore Technologies, s.r.o.
5 
6  Permission is hereby granted, free of charge, to any person obtaining a
7  copy of this software and associated documentation files (the "Software"),
8  to deal in the Software without restriction, including without limitation
9  the rights to use, copy, modify, merge, publish, distribute, sublicense,
10  and/or sell copies of the Software, and to permit persons to whom the
11  Software is furnished to do so, subject to the following conditions:
12 
13  The above copyright notice and this permission notice shall be included in
14  all copies or substantial portions of the Software.
15 
16  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
17  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
18  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
19  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
20  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
21  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
22  DEALINGS IN THE SOFTWARE.
23 */
24 
25 // this module requires Qore 0.8.12 or better
26 
27 // requires the SqlUtil module
28 
29 // requires the Util module
30 
31 // for bindOracleCollection
32 
33 // don't use "$" signs for variables and class members, assume local variable scope
34 
35 // require type definitions everywhere
36 
37 // enable all warnings
38 
39 
40 /* Version History: see docs below
41 */
42  a.*, rownum rnum from (select * from schema.table where type = %v order by type) a where rownum <= %v) where rnum > %v", ("user", 300, 200));
72  @endcode
73  note that the following simpler SQL is generated for Oracle 12c+ servers:
74  @code{.py}
75 $ds.vselectRows("select * from schema.table where type = %v order by type offset %v rows fetch next %v rows only", ("user", 200, 100));
76  @endcode
77 
78  @subsection ora_in_operator IN Operator on Oracle
79 
80  In order to avoid dynamic SQL and better manage the server's shared pool (in particular, the number of parsed statements), the %OracleSqlUtil
81  module uses bind by value with the SQL \c IN operator.
82 
83  For example, the following query:
84  @code{.py}
85 my *hash $q = $table.select(("where": ("col1": op_in(1, 2, 3, 4))));
86  @endcode
87 
88  Results in the equivalent of the following SQL:
89  @code{.py}
90 my *hash $q = $ds.select("select * from schema.table where col1 in (select column_value from table(%v))", bindOracleCollection("SYS.ODCIVARCHAR2LIST", (1,2,3,4)));
91  @endcode
92 
93  @htmlonly <style><!-- td.qore { background-color: #5b9409; color: white; } --></style> @endhtmlonly
94  <table>
95  <tr>
96  <td class="qore"><b>Qore Type</b></td>
97  <td class="qore"><b>Oracle Collection</b></td>
98  </tr>
99  <tr>
100  <td>Date</td>
101  <td>\c SYS.ODCIDATELIST</td>
102  </tr>
103  <tr>
104  <td>Float</td>
105  <td>\c SYS.ODCINUMBERLIST</td>
106  </tr>
107  <tr>
108  <td>Integer</td>
109  <td>\c SYS.ODCINUMBERLIST</td>
110  </tr>
111  <tr>
112  <td>Number</td>
113  <td>\c SYS.ODCINUMBERLIST</td>
114  </tr>
115  <tr>
116  <td>String</td>
117  <td>\c SYS.ODCIVARCHAR2LIST</td>
118  </tr>
119  </table>
120 
121  There universal collections are limited to 32767 elements. And \c SYS.ODCIVARCHAR2LIST element size is \c VARCHAR2(4000).
122 
123  @subsection ora_partitioning_select Partition Support in Selects
124 
125  It's possible to select from a particular partition of a table with %OracleSqlUtil;
126  @ref OracleSqlUtil::OracleTable::OracleSelectOptions "OracleSelectOptions" defines the \c "partition" key which can be added to a
127  @ref select_option_hash "select option hash" to specify the partition to select from as in the following example:
128  @code{.py}
129 my *list $rows = $table.selectRows(("created": op_gt(2012-05-01), "partition": "p1"));
130  @endcode
131  Which generates an SQL command like the following:
132  @code{.py}
133 my *list $rows = $ds.vselectRows("select * from schema.table partition(p1) where created > %v", (2012-05-01));
134  @endcode
135 
136  @subsection ora_partitioning_join Partition Support in Joins
137 
138  It's possible to perform a join on a particular partition of a table with %OracleSqlUtil; the join option \c "partition" is
139  supported to specify the partition to join on as in the following example:
140  @code{.py}
141 my *list $rows = $table.selectRows(("join": join_inner($table2, "t2", ("id": "altid"), NOTHING, ("partition": "p2"))));
142  @endcode
143  Which generates an SQL command like the following:
144  @code{.py}
145 my *list $rows = $ds.vselectRows("select * from schema.table inner join schema.table2 partition(p2) t2 on (schema.table.id = t2.altid)");
146  @endcode
147 
148  @section ora_schema_management Schema Management on Oracle
149 
150  Note that when getting an object description from an Oracle database, if the object cannot be found in the connection schema, then
151  if a synonym of the same type exists and the target object is accessible, then the target object is read automatically and the owning
152  schema name is also set to the actual owner of the object.
153 
154  @subsection ora_type_mapping Type Mapping
155 
156  Column types are mapped from %Qore types as follows:
157 
158  <b>Oracle Column Type Mappings</b>
159  <table>
160  <tr>
161  <td class="qore"><b>Generic Type Name</b></td>
162  <td class="qore"><b>Oracle Type Used</b></td>
163  </tr>
164  <tr>
165  <td>\c float</td>
166  <td>\c number</td>
167  </tr>
168  <tr>
169  <td>\c integer</td>
170  <td>\c number</td>
171  </tr>
172  <tr>
173  <td>\c number</td>
174  <td>\c number</td>
175  </tr>
176  <tr>
177  <td>\c string</td>
178  <td>\c varchar2</td>
179  </tr>
180  <tr>
181  <td>\c date</td>
182  <td>\c timestamp(6)</td>
183  </tr>
184  <tr>
185  <td>\c binary</td>
186  <td>\c blob</td>
187  </tr>
188  <tr>
189  <td>@ref SqlUtil::BLOB</td>
190  <td>\c blob</td>
191  </tr>
192  <tr>
193  <td>@ref SqlUtil::CHAR</td>
194  <td>\c char</td>
195  </tr>
196  <tr>
197  <td>@ref SqlUtil::CLOB</td>
198  <td>\c clob</td>
199  </tr>
200  <tr>
201  <td>@ref SqlUtil::NUMERIC</td>
202  <td>\c number</td>
203  </tr>
204  <tr>
205  <td>@ref SqlUtil::VARCHAR</td>
206  <td>\c varchar2</td>
207  </tr>
208  </table>
209 
210  To use other types, use the \c "native_type" @ref SqlUtil::AbstractTable::ColumnDescOptions "column description option" with the
211  native Oracle type name instead (under the \c "driver" and \c "oracle" keys for schemas supporting multiple databases).
212 
213  @subsection ora_other_objects Additional Object Types Supported
214 
215  The following additional schema objects can be managed with %OracleSqlUtil:
216  - @ref ora_materialized_views "materialized views"
217  - @ref ora_packages "packages"
218  - @ref ora_types "types"
219 
220  @subsection ora_materialized_views Materialized Views
221 
222  The @ref schema_desc_hash takes an optional key, \c "materialized_views" that allows materialized views in Oracle schemas to be managed along with other objects.
223 
224  The \c "materialized_views" should be assigned to a hash, each key name is the name of the materialized view, and the values are hashes with the
225  following keys:
226  - \c "logging": (@ref bool_type "bool") if the materialized view should be logged
227  - \c "use_index": (@ref bool_type "bool") if the materialized view should be indexed
228  - \c "src": (@ref bool_type "bool") the source of the materialized view
229 
230  The \c "materialized_views" key can go in the top level of the @ref schema_desc_hash for Oracle-only schemas, or, for schemas targeting multiple database types, under the \c "driver" and \c "oracle" keys as in the following example:
231 
232  @code{.py}
233 my hash $schema = (
234  "driver": (
235  "oracle": (
236  "materialized_views": (
237  "example_mv": (
238  "logging": False,
239  "use_index": False,
240  "src": "select type, count(1) total_count from table group by type",
241  ),
242  ),
243  ),
244  ),
245 );
246  @endcode
247 
248  @subsection ora_packages Packages
249 
250  The @ref schema_desc_hash takes an optional key, \c "packages" that allows packages in Oracle schemas to be managed along with other objects.
251 
252  The \c "packages" should be assigned to a hash, each key name is the name of the package, and the values are hashes with the
253  following key:
254  - \c "src": (@ref bool_type "bool") the source of the package
255 
256  The \c "packages" key can go in the top level of the @ref schema_desc_hash for Oracle-only schemas, or, for schemas targeting multiple database types, under the \c "driver" and \c "oracle" keys as in the following example:
257 
258  @code{.py}
259 my hash $schema = (
260  "driver": (
261  "oracle": (
262  "packages": (
263  "example_pkg": (
264  "src": "types as
265 type cursorType is ref cursor;
266 MYSTATCOMPLETE constant order_status.orderstatus%type := 'C';
267 MYSTATERROR constant order_status.orderstatus%type := 'E';
268 ",
269  ),
270  ),
271  ),
272  ),
273 );
274  @endcode
275 
276  @subsection ora_types Types
277 
278  The @ref schema_desc_hash takes an optional key, \c "types" that allows types in Oracle schemas to be managed along with other objects.
279 
280  The \c "types" should be assigned to a hash, each key name is the name of the type, and the values are strings giving the type definition.
281 
282  The \c "types" key can go in the top level of the @ref schema_desc_hash for Oracle-only schemas, or, for schemas targeting multiple database types, under the \c "driver" and \c "oracle" keys as in the following example:
283 
284  @code{.py}
285 my hash $schema = (
286  "driver": (
287  "oracle": (
288  "types": (
289  "num_array": "table of number",
290  "str_array": "table of varchar2(240)",
291  ),
292  ),
293  ),
294 );
295  @endcode
296 
297  @see OracleSqlUtil::OracleDatabase::OracleSchemaDescriptionOptions for a list of Oracle-specific schema description hash keys.
298 
299  @section ora_relnotes Release Notes
300 
301  @subsection v123 OracleSqlUtil v1.2.3
302  - fixed a bug in \c character_semantics for standalone column (<a href="https://github.com/qorelanguage/qore/issues/1688">issue 1688</a>)
303  - implemented @ref cop_trunc_date() operator (<a href="https://github.com/qorelanguage/qore/issues/2032">issue 2032</a>)
304 
305  @subsection v122 OracleSqlUtil v1.2.2
306  - fixed a bug in the \a force option (i.e. cascade) for dropping types (<a href="https://github.com/qorelanguage/qore/issues/1683">issue 1683</a>)
307 
308  @subsection v121 OracleSqlUtil v1.2.1
309  - implemented the \a force option (i.e. cascade) for dropping code objects (<a href="https://github.com/qorelanguage/qore/issues/1314">issue 1314</a>)
310  - worked around ORA-22165 from op_in() caused by Oracle's limit on number of collection elements (<a href="https://github.com/qorelanguage/qore/issues/1660">issue 1660</a>)
311 
312  @subsection v12 OracleSqlUtil v1.2
313  - implemented support for the \c "returning" clause as an insert option
314  - implemented support for views for DML in the @ref OracleSqlUtil::OracleTable class
315  - implemented @ref OracleSqlUtil::OracleTable::getBaseType()
316  - implemented support for Oracle pseudocolumns in queries
317  - implemented support for @ref SqlUtil::cop_cast() operator
318  - fixed bugs in @ref SqlUtil::cop_seq() and @ref SqlUtil::cop_seq_currval() (<a href="https://github.com/qorelanguage/qore/issues/624">issue 624</a>)
319  - return lists from Oracle's data dictionary ordered
320  - implemented @ref OracleSqlUtil::OracleTable::bindEmptyStringsAsNull()
321  - implemented high-performance "upsert" (merge) support also supporting bulk DML
322  - implemented @ref SqlUtil::cop_substr() and @ref SqlUtil::uop_substr() operators (<a href="https://github.com/qorelanguage/qore/issues/801">issue 801</a>)
323  - implemented @ref SqlUtil::op_substr() where operator (<a href="https://github.com/qorelanguage/qore/issues/883">issue 883</a>)
324 
325  @subsection v11 OracleSqlUtil v1.1
326  - fixed selects with "limit" but no "offset"
327  - convert date/time values to timestamps with microseconds resolution instead of dates with second resolution when dynamically inserting values as strings in SQL (binding by value not affected)
328  - fixed schema information classes when the "string-numbers" driver option is enabled
329 
330  @subsection v10 OracleSqlUtil v1.0
331  - initial release
332 */
333 
335 namespace OracleSqlUtil {
337  OracleTable get_table(AbstractDatasource nds, string nname, *hash opts);
338 
339 
341  OracleDatabase get_database(AbstractDatasource nds, *hash opts);
342 
343 
346 
347 public:
348  public :
350  bool char_used;
353 
354 public:
355 
356  constructor(string n, string nt, *string qt, softint sz, bool nul, *string dv, *string cm, bool is_char = False, bool cu = False, softint bs) ;
357 
358 
360  string getNativeTypeString();
361 
362 
364 
371  list getAddColumnSql(AbstractTable t);
372 
373 
375 
385  list getModifySqlImpl(AbstractTable t, AbstractColumn col, *hash opt);
386 
387 
389 
399  string getRenameSql(AbstractTable t, string new_name);
400 
401 
403  bool equalImpl(AbstractColumn c);
404 
405  };
406 
409 
410 public:
411  constructor(string n, string nt, *string qt, softint sz, bool nul, *string dv, *string cm, softint bs, softint n_scale = 0) ;
412 
413 
414  string getNativeTypeString();
415 
416  };
417 
420 
421 public:
422  public :
424  string native_type;
425 
427  *string tablespace;
428 
429 public:
430 
432  constructor(string n, bool u, hash c, string nt, *string t) ;
433 
434 
436  string getCreateSql(string table_name, *hash opt);
437 
438 
440  bool equalImpl(AbstractIndex ix);
441 
442 
444  string getRenameSql(string table_name, string new_name);
445 
446  };
447 
450 
451 public:
452  public :
454  bool enabled;
455 
456 public:
457 
458  constructor(string n, Columns c, ForeignConstraintTarget t, bool e) ;
459 
460 
461  string getCreateSql(string table_name, *hash opt);
462 
463 
464  softlist getRenameSql(string table_name, string new_name);
465 
466 
468  string getDisableSql(string table_name);
469 
470 
472  string getEnableSql(string table_name, *hash opt);
473 
474  };
475 
478 
479 public:
480  public :
482  bool enabled;
483 
484 public:
485 
486  constructor(string n, string n_src, bool e = True) ;
487 
488 
489  string getCreateSql(string table_name, *hash opt);
490 
491 
492  softlist getRenameSql(string table_name, string new_name);
493 
494 
496  string getDisableSql(string table_name);
497 
498 
500  string getEnableSql(string table_name, *hash opt);
501 
502  };
503 
506 
507 public:
508  private :
510  bool enabled;
511 
513  *string tablespace;
514 
515 public:
516 
517  constructor(string n, hash n_cols, bool e = True, *string ts) ;
518 
519 
521 
536  OracleColumn memberGate(string k);
537 
538 
539  bool setIndexBase(string ix);
540 
541 
543  clearIndex();
544 
545 
546  string getCreateSql(string table_name, *hash opts);
547 
548 
549  softlist getRenameSql(string table_name, string new_name);
550 
551 
553  string getDisableSql(string table_name);
554 
555 
557  string getEnableSql(string table_name, *hash opt);
558 
559 
561  bool isEnabled();
562 
563 
565  *string getTablespace();
566 
567  };
568 
571 
572 public:
573  private :
575  *string tablespace;
576 
577 public:
578 
579  constructor();
580 
581 
582  constructor(string n, *hash c, *string ts) ;
583 
584 
586 
601  OracleColumn memberGate(string k);
602 
603 
604  bool setIndexBase(string ix);
605 
606 
608  clearIndex();
609 
610 
611  string getCreateSql(string table_name, *hash opts);
612 
613 
614  softlist getRenameSql(string table_name, string new_name);
615 
616 
618 
620  string getDropSql(string table_name);
621 
622 
624  string getDisableSql(string table_name);
625 
626 
628  string getEnableSql(string table_name, *hash opt);
629 
630  };
631 
634 
635 public:
637  constructor(string n_name, number n_start = 1, number n_increment = 1, *softnumber n_end) ;
638 
639 
641  string getCreateSql(*hash opt);
642 
643 
645 
649  softlist getRenameSql(string new_name, *hash opt);
650 
651  };
652 
655 
656 public:
657  public :
659  *string type_text;
661  *string oid_text;
665  *string view_type;
667  *string superview_name;
670 
671 public:
672 
674  constructor(string n_name, string n_src, *string n_schema, *string n_type_text,
675  *string n_oid_text, *string n_view_type_owner,
676  *string n_view_type, *string n_superview_name,
677  bool n_updatable, bool n_container_data)
678  ;
679 
680 
682  string getCreateSql(*hash opt);
683 
684 
686 
690  softlist getRenameSql(string new_name, *hash opt);
691 
692  };
693 
696 
697 public:
698  public :
700  bool enabled;
701 
702 public:
703 
704  constructor(string n, string n_src, bool en = True) ;
705 
706 
707  softlist getCreateSql(string table_name, *hash opt);
708 
709 
711  bool equalImpl(AbstractFunctionBase t);
712 
713 
715  softlist getRenameSql(string table_name, string new_name);
716 
717 
719  softlist getDropSql(string table_name);
720 
721  };
722 
725 
726 public:
728 
732  constructor(string n, string n_type, string n_src) ;
733 
734 
736 
738  softlist getCreateSql(*hash opt);
739 
740 
742  bool equalImpl(AbstractFunctionBase t);
743 
744 
746 
750  softlist getRenameSql(string new_name, *hash opt);
751 
752  };
753 
756 
757 public:
759 
763  constructor(string n, string n_type, string n_src) ;
764 
765 
767 
772  softlist getRenameSql(string new_name, *hash opt);
773 
774  };
775 
778 
779 public:
780  constructor(string n_name, string n_src) ;
781 
782 
784 
786  string getDropSql(*hash opt);
787 
788  };
789 
792 
793 public:
795 
798  constructor(string n, string n_src) ;
799 
800  };
801 
804 
805 public:
807 
810  constructor(string n, string n_src) ;
811 
812  };
813 
816 
817 public:
818  public :
820  *string body_src;
821 
822 public:
823 
825 
829  constructor(string n, string n_src, *string n_body_src) ;
830 
831 
833  list getCreateSql(*hash opt);
834 
835 
837  bool equalImpl(AbstractFunctionBase t);
838 
839  };
840 
843 
844 public:
845  public :
847  bool logging;
849  bool use_index;
851  *string tablespace;
852 
853 public:
854 
856 
862  constructor(string n, string n_src, bool n_logging = True, bool n_use_index = True, *string n_tablespace) ;
863 
864 
866  softlist getCreateSql(*hash opt);
867 
868 
869  bool equalImpl(AbstractFunctionBase t);
870 
871  };
872 
875 
876 public:
877  public :
879  const OracleCreationOptions = AbstractDatabase::CreationOptions + (
880  "compute_statistics": Type::Boolean,
881  );
882 
884  const OracleAlignSchemaOptions = AbstractDatabase::AlignSchemaOptions
885  + OracleCreationOptions
886  + OracleTable::OracleAlignTableOptions
887  ;
888 
890 
898  const OracleSchemaDescriptionOptions = AbstractDatabase::SchemaDescriptionOptions + (
899  "types": Type::Hash,
900  "type_map": Type::Hash,
901 
902  "packages": Type::Hash,
903  "package_map": Type::Hash,
904 
905  "materialized_views": Type::Hash,
906  "materialized_view_map": Type::Hash,
907 
908  //"synonyms": Type::Hash,
909  //"synonym_map": Type::Hash,
910  );
911 
913  const OraclePackageDescriptionOptions = (
914  "src": Type::String,
915  "body": Type::String,
916  );
917 
919  const OracleMaterializedViewDescriptionOptions = (
920  "logging": Type::Boolean,
921  "use_index": Type::Boolean,
922  "tablespace": Type::String,
923  "src": Type::String,
924  );
925 
927  const OracleReservedWords = (
928  "access": True,
929  "else": True,
930  "modify": True,
931  "start": True,
932  "add": True,
933  "exclusive": True,
934  "noaudit": True,
935  "select": True,
936  "all": True,
937  "exists": True,
938  "nocompress": True,
939  "session": True,
940  "alter": True,
941  "file": True,
942  "not": True,
943  "set": True,
944  "and": True,
945  "float": True,
946  "notfound": True,
947  "share": True,
948  "any": True,
949  "for": True,
950  "nowait": True,
951  "size": True,
952  "arraylen": True,
953  "from": True,
954  "null": True,
955  "smallint": True,
956  "as": True,
957  "grant": True,
958  "number": True,
959  "sqlbuf": True,
960  "asc": True,
961  "group": True,
962  "of": True,
963  "successful": True,
964  "audit": True,
965  "having": True,
966  "offline": True,
967  "synonym": True,
968  "between": True,
969  "identified": True,
970  "on": True,
971  "sysdate": True,
972  "by": True,
973  "immediate": True,
974  "online": True,
975  "table": True,
976  "char": True,
977  "in": True,
978  "option": True,
979  "then": True,
980  "check": True,
981  "increment": True,
982  "or": True,
983  "to": True,
984  "cluster": True,
985  "index": True,
986  "order": True,
987  "trigger": True,
988  "column": True,
989  "initial": True,
990  "pctfree": True,
991  "uid": True,
992  "comment": True,
993  "insert": True,
994  "prior": True,
995  "union": True,
996  "compress": True,
997  "integer": True,
998  "privileges": True,
999  "unique": True,
1000  "connect": True,
1001  "intersect": True,
1002  "public": True,
1003  "update": True,
1004  "create": True,
1005  "into": True,
1006  "raw": True,
1007  "user": True,
1008  "current": True,
1009  "is": True,
1010  "rename": True,
1011  "validate": True,
1012  "date": True,
1013  "level": True,
1014  "resource": True,
1015  "values": True,
1016  "decimal": True,
1017  "like": True,
1018  "revoke": True,
1019  "varchar": True,
1020  "default": True,
1021  "lock": True,
1022  "row": True,
1023  "varchar2": True,
1024  "delete": True,
1025  "long": True,
1026  "view": True,
1027  "desc": True,
1028  "maxextents": True,
1029  "rowlabel": True,
1030  "whenever": True,
1031  "distinct": True,
1032  "minus": True,
1033  "rownum": True,
1034  "where": True,
1035  "drop": True,
1036  "mode": True,
1037  "rows": True,
1038  "with": True,
1039  );
1040 
1042  const OracleRebuildIndexOptions = (
1043  "parallel" : Type::Boolean,
1044  "logging" : Type::Boolean,
1045  "statictics" : Type::Boolean,
1046  "tablespace" : Type::String,
1047  "cond_rebuild" : Type::Boolean,
1048  "cond_maxheight" : Type::Int,
1049  "cond_maxleafpct" : Type::Int,
1050  );
1051 
1053  const OracleComputeStatisticsOptions = ComputeStatisticsOptions + (
1054  "estimate_percent" : Type::Int,
1055  "block_sample" : Type::Boolean,
1056  "method_opt" : Type::String,
1057  "degree" : Type::Int,
1058  "granularity" : Type::String,
1059  "cascade" : Type::Boolean,
1060  "stattab" : Type::String,
1061  "statid" : Type::String,
1062  "options" : Type::String,
1063  "statown" : Type::String,
1064  );
1065 
1066 public:
1067 
1069  constructor(AbstractDatasource nds, *hash opts) ;
1070 
1071 
1072 
1073 private:
1074  list featuresImpl();
1075 public:
1076 
1077 
1078 
1079 private:
1080  OracleSequence makeSequenceImpl(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
1081 public:
1082 
1083 
1084 
1085 private:
1086  getSchemaName(reference<string> name, reference<string> schema);
1087 public:
1088 
1089 
1090 
1091 private:
1092  *AbstractSequence getSequenceImpl(string name);
1093 public:
1094 
1095 
1096 
1097 private:
1098  *AbstractView getViewImpl(string name);
1099 public:
1100 
1101 
1102 
1103 private:
1104  OracleFunction makeFunctionImpl(string name, string src, *hash opts);
1105 public:
1106 
1107 
1108 
1109 private:
1110  OracleProcedure makeProcedureImpl(string name, string src, *hash opts);
1111 public:
1112 
1113 
1114 
1115 private:
1116  OraclePackage makePackage(string name, string src, string body, *hash opts);
1117 public:
1118 
1119 
1120 
1121 private:
1122  OraclePackage makePackageFromDescription(string name, hash ph, *hash opts);
1123 public:
1124 
1125 
1126 
1127 private:
1128  OracleType makeType(string name, string src, *hash opts);
1129 public:
1130 
1131 
1132 
1133 private:
1134  OracleMaterializedView makeMaterializedView(string name, string src, bool logging = True, bool use_index = True, *string tablespace, *hash opts);
1135 public:
1136 
1137 
1138 
1139 private:
1140  OracleMaterializedView makeMaterializedViewFromDescription(string name, hash mvh, *hash opts);
1141 public:
1142 
1143 
1144 
1145 private:
1146  list getDropSchemaSqlImpl(hash schema_hash, *hash opt);
1147 public:
1148 
1149 
1150 
1151 private:
1152  list getAlignSqlImpl(hash schema_hash, *hash opt);
1153 public:
1154 
1155 
1156 
1157 private:
1158  *OracleFunction getFunctionImpl(string name);
1159 public:
1160 
1161 
1162 
1163 private:
1164  *OracleProcedure getProcedureImpl(string name);
1165 public:
1166 
1167 
1169  *OraclePackage getPackage(string name);
1170 
1171 
1173  *OracleType getType(string name);
1174 
1175 
1177  *OracleMaterializedView getMaterializedView(string name);
1178 
1179 
1180 
1181 private:
1182  *string getSource(string type, string name);
1183 public:
1184 
1185 
1186 
1187 private:
1188  checkSource(string type, string name, reference<string> src);
1189 public:
1190 
1191 
1193  list listSynonyms();
1194 
1195 
1197  ListIterator synonymIterator();
1198 
1199 
1201  list listTypes();
1202 
1203 
1205  ListIterator typeIterator();
1206 
1207 
1209  list listPackages();
1210 
1211 
1213  ListIterator packageIterator();
1214 
1215 
1217  list listMaterializedViews();
1218 
1219 
1221  ListIterator materializedViewIterator();
1222 
1223 
1224 
1225 private:
1226  list listTablesImpl();
1227 public:
1228 
1229 
1230 
1231 private:
1232  list listFunctionsImpl();
1233 public:
1234 
1235 
1236 
1237 private:
1238  list listProceduresImpl();
1239 public:
1240 
1241 
1242 
1243 private:
1244  list listSequencesImpl();
1245 public:
1246 
1247 
1248 
1249 private:
1250  list listViewsImpl();
1251 public:
1252 
1253 
1254 
1255 private:
1256  list getListIntern(string type);
1257 public:
1258 
1259 
1260 
1261 private:
1262  list getListIntern(list l);
1263 public:
1264 
1265 
1266 
1267 private:
1268  string getCreateSqlImpl(list l);
1269 public:
1270 
1271 
1272  static string getCreateSql(list l);
1273 
1275 
1276 private:
1277  hash getCreationOptions();
1278 public:
1279 
1280 
1282 
1283 private:
1284  hash getAlignSchemaOptions();
1285 public:
1286 
1287 
1289 
1290 private:
1291  hash getSchemaDescriptionOptions();
1292 public:
1293 
1294 
1296 
1297 private:
1298  hash getRebuildIndexOptions();
1299 public:
1300 
1301 
1303 
1304 private:
1305  hash getComputeStatisticsOptions();
1306 public:
1307 
1308 
1310 
1311 private:
1312  softint getNextSequenceValueImpl(string name);
1313 public:
1314 
1315 
1317 
1318 private:
1319  softint getCurrentSequenceValueImpl(string name);
1320 public:
1321 
1322 
1324 
1325 private:
1326  bool supportsSequencesImpl();
1327 public:
1328 
1329 
1331 
1332 private:
1333  bool supportsTypesImpl();
1334 public:
1335 
1336 
1338 
1339 private:
1340  bool supportsPackagesImpl();
1341 public:
1342 
1343 
1345 
1357  bool rebuildIndexAnalyze(AbstractIndex index, int maxh, int maxleaf);
1358 
1359 
1361 
1373  bool rebuildIndexAnalyze(string name, int maxh, int maxleaf);
1374 
1375 
1377 
1378 private:
1379  bool rebuildIndexImpl(string name, *hash options);
1380 public:
1381 
1382 
1384 
1385 private:
1386  computeStatisticsImpl(*hash options);
1387 public:
1388 
1389 
1391 
1392 private:
1393  computeStatisticsSchemaImpl(*hash options);
1394 public:
1395 
1396 
1398 
1399 private:
1400  computeStatisticsTablesImpl(*hash options);
1401 public:
1402 
1403 
1405 
1406 private:
1407  reclaimSpaceImpl(*hash options);
1408 public:
1409 
1410 
1411  };
1412 
1414 
1417 
1418 public:
1419  public :
1421  const OraTypeMap = (
1422  "number": ("qore": Type::Number, "size": SZ_NUM, "size_range": (1, 38), "scale_range": (-84, 127)),
1423  "varchar2": ("qore": Type::String, "size": SZ_MAND, "size_range": (1, 4000), "is_char": True),
1424  "char": ("qore": Type::String, "size": SZ_MAND, "size_range": (1, 4000), "is_char": True),
1425  "date": ("qore": Type::Date,),
1426  "timestamp": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 9)),
1427  "timestamp with time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 9)),
1428  "timestamp with local time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 9)),
1429  "interval year to month": ("qore": Type::Date,),
1430  "interval day to second": ("qore": Type::Date,),
1431  "timestamp(0)": ("qore": Type::Date,),
1432  "timestamp(1)": ("qore": Type::Date,),
1433  "timestamp(2)": ("qore": Type::Date,),
1434  "timestamp(3)": ("qore": Type::Date,),
1435  "timestamp(4)": ("qore": Type::Date,),
1436  "timestamp(5)": ("qore": Type::Date,),
1437  "timestamp(6)": ("qore": Type::Date,),
1438  "timestamp(7)": ("qore": Type::Date,),
1439  "timestamp(8)": ("qore": Type::Date,),
1440  "timestamp(9)": ("qore": Type::Date,),
1441  "timestamp(0) with time zone": ("qore": Type::Date,),
1442  "timestamp(1) with time zone": ("qore": Type::Date,),
1443  "timestamp(2) with time zone": ("qore": Type::Date,),
1444  "timestamp(3) with time zone": ("qore": Type::Date,),
1445  "timestamp(4) with time zone": ("qore": Type::Date,),
1446  "timestamp(5) with time zone": ("qore": Type::Date,),
1447  "timestamp(6) with time zone": ("qore": Type::Date,),
1448  "timestamp(7) with time zone": ("qore": Type::Date,),
1449  "timestamp(8) with time zone": ("qore": Type::Date,),
1450  "timestamp(9) with time zone": ("qore": Type::Date,),
1451  "timestamp(0) with local time zone": ("qore": Type::Date,),
1452  "timestamp(1) with local time zone": ("qore": Type::Date,),
1453  "timestamp(2) with local time zone": ("qore": Type::Date,),
1454  "timestamp(3) with local time zone": ("qore": Type::Date,),
1455  "timestamp(4) with local time zone": ("qore": Type::Date,),
1456  "timestamp(5) with local time zone": ("qore": Type::Date,),
1457  "timestamp(6) with local time zone": ("qore": Type::Date,),
1458  "timestamp(7) with local time zone": ("qore": Type::Date,),
1459  "timestamp(8) with local time zone": ("qore": Type::Date,),
1460  "timestamp(9) with local time zone": ("qore": Type::Date,),
1461  "interval year(0) to month": ("qore": Type::Date,),
1462  "interval year(1) to month": ("qore": Type::Date,),
1463  "interval year(2) to month": ("qore": Type::Date,),
1464  "interval year(3) to month": ("qore": Type::Date,),
1465  "interval year(4) to month": ("qore": Type::Date,),
1466  "interval year(5) to month": ("qore": Type::Date,),
1467  "interval year(6) to month": ("qore": Type::Date,),
1468  "interval year(7) to month": ("qore": Type::Date,),
1469  "interval year(8) to month": ("qore": Type::Date,),
1470  "interval year(9) to month": ("qore": Type::Date,),
1471  "interval day(0) to second(0)": ("qore": Type::Date,),
1472  "interval day(0) to second(1)": ("qore": Type::Date,),
1473  "interval day(0) to second(2)": ("qore": Type::Date,),
1474  "interval day(0) to second(3)": ("qore": Type::Date,),
1475  "interval day(0) to second(4)": ("qore": Type::Date,),
1476  "interval day(0) to second(5)": ("qore": Type::Date,),
1477  "interval day(0) to second(6)": ("qore": Type::Date,),
1478  "interval day(0) to second(7)": ("qore": Type::Date,),
1479  "interval day(0) to second(8)": ("qore": Type::Date,),
1480  "interval day(0) to second(9)": ("qore": Type::Date,),
1481  "interval day(1) to second(0)": ("qore": Type::Date,),
1482  "interval day(1) to second(1)": ("qore": Type::Date,),
1483  "interval day(1) to second(2)": ("qore": Type::Date,),
1484  "interval day(1) to second(3)": ("qore": Type::Date,),
1485  "interval day(1) to second(4)": ("qore": Type::Date,),
1486  "interval day(1) to second(5)": ("qore": Type::Date,),
1487  "interval day(1) to second(6)": ("qore": Type::Date,),
1488  "interval day(1) to second(7)": ("qore": Type::Date,),
1489  "interval day(1) to second(8)": ("qore": Type::Date,),
1490  "interval day(1) to second(9)": ("qore": Type::Date,),
1491  "interval day(2) to second(0)": ("qore": Type::Date,),
1492  "interval day(2) to second(1)": ("qore": Type::Date,),
1493  "interval day(2) to second(2)": ("qore": Type::Date,),
1494  "interval day(2) to second(3)": ("qore": Type::Date,),
1495  "interval day(2) to second(4)": ("qore": Type::Date,),
1496  "interval day(2) to second(5)": ("qore": Type::Date,),
1497  "interval day(2) to second(6)": ("qore": Type::Date,),
1498  "interval day(2) to second(7)": ("qore": Type::Date,),
1499  "interval day(2) to second(8)": ("qore": Type::Date,),
1500  "interval day(2) to second(9)": ("qore": Type::Date,),
1501  "clob": ("qore": Type::String,),
1502  "blob": ("qore": Type::Binary,),
1503  "long": ("qore": Type::Binary,),
1504  "raw": ("qore": Type::Binary, "size": SZ_MAND, "size_range": (1, 2000)),
1505  "bfile": ("qore": Type::Binary,),
1506  "binary_float": ("qore": Type::Float,),
1507  "binary_double": ("qore": Type::Float,),
1508  "rowid": ("qore": Type::String,),
1509  "urowid": ("qore": Type::String, "size": SZ_OPT, "size_range": (1, 4000)),
1510  );
1511 
1513  const QoreTypeMap = (
1514  "integer": "number",
1515  "float": "number",
1516  "number": "number",
1517  "string": "varchar2",
1518  "date": "timestamp(6)",
1519  "binary": "blob",
1520  SqlUtil::CHAR: "char",
1521  SqlUtil::CLOB: "clob",
1522  SqlUtil::BLOB: "blob",
1523  );
1524 
1525  const OraColumnOpts = (
1526  "character_semantics": Type::Boolean,
1527  );
1528 
1530 
1533  const OraColumnOptions = AbstractTable::ColumnOptions + OraColumnOpts;
1534 
1536 
1539  const OraColumnDescOptions = AbstractTable::ColumnDescOptions + OraColumnOpts;
1540 
1542 
1545  const OracleIndexOptions = AbstractTable::IndexOptions + (
1546  "compute_statistics": Type::Boolean,
1547  );
1548 
1550 
1553  const OracleConstraintOptions = OracleIndexOptions + (
1554  "index": Type::String,
1555  );
1556 
1558  const OracleTableCreationOptions = AbstractTable::TableCreationOptions
1559  + OracleConstraintOptions
1560  + OraColumnOptions
1561  ;
1562 
1563  const OracleAlignTableOptions = AbstractTable::AlignTableOptions + OracleTableCreationOptions;
1564 
1566 
1570  const OracleSelectOptions = AbstractTable::SelectOptions + (
1571  "partition": Type::String,
1572  );
1573 
1575  const OracleOpMap = DefaultOpMap + (
1576  OP_IN: (
1577  "code": string (object t, string cn, softlist arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
1578  // Qorus bug #989 SqlUtil: oracle op_in can fail in large amount of input elements
1579  // There is no support for CLOBs in WHERE clause in Oracle at all.
1580  // Binding large strings (over 4000 chars is performed by a CLOB in Qore driver.
1581  // Result of op_in operator can be "ORA-00932: inconsistent datatypes" if is
1582  // the list longer or if it contains large items with:
1583  // return cn + " in (select regexp_substr(%v,'[^,]+', 1, level) from dual connect by regexp_substr(%v, '[^,]+', 1, level) is not null)";
1584 
1585  // determine list members type. Let's assume the 1st non NULL/NOTHING
1586  // element gives the type code for all elements.
1587  ListIterator it(arg);
1588  int ltype;
1589  while (it.next());
1590 
1591 
1592  // Split long array to chunks of no more than 32767 elements due to Oracle limitation
1593  // "ORA-22165: OCI-22165: given index [32767] must be in the range of [0] to [32766]"
1594  int count;
1595  while (arg.size());
1596 
1597 
1598  if (count)
1599  return cn + " in (" + (map "select column_value from table(%v)", xrange(1,count)).join(" union all ") + ")";
1600  else
1601  return "1 != 1";
1602  throw "MISSING-ORACLE-DRIVER", "op_in requires oracle driver";
1603  },
1604  ),
1605  OP_SUBSTR: (
1606  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
1607  args += arg[0]; // start
1608  if (!exists arg[1]);
1609 
1610  args += arg[1]; // count
1611  args += arg[2]; // text
1612  return sprintf("substr(%s,%v,%v) = %v", cn);
1613  },
1614  ),
1615  );
1616 
1618  const OracleCopMap = DefaultCopMap + (
1619  COP_CAST: (
1620  "code": string (string cve, list args) {
1621  string name = QoreTypeMap{args[0]} ?? args[0];
1622  hash desc = OraTypeMap{name};
1623  string sql = sprintf ("cast (%s as %s", cve, name);
1624  switch (name);
1625 
1626  sql += ")";
1627  return sql;
1628  },
1629  ),
1630  COP_SUBSTR: (
1631  "code": string (string cve, list args) {
1632  if (!exists args[1])
1633  return sprintf("substr(%s,%d)", cve, args[0]);
1634  return sprintf("substr(%s,%d,%d)", cve, args[0], args[1]);
1635  },
1636  ),
1637  COP_YEAR: (
1638  "code": string (string arg1, auto arg) {
1639  return sprintf("to_char(%s, 'YYYY')", arg1);
1640  }
1641  ),
1642  COP_YEAR_MONTH: (
1643  "code": string (string arg1, auto arg) {
1644  return sprintf("to_char(%s, 'YYYY-MM')", arg1);
1645  }
1646  ),
1647  COP_YEAR_DAY: (
1648  "code": string (string arg1, auto arg) {
1649  return sprintf("to_char(%s, 'YYYY-MM-DD')", arg1);
1650  }
1651  ),
1652  COP_YEAR_HOUR: (
1653  "code": string (string arg1, auto arg) {
1654  return sprintf("to_char(%s, 'YYYY-MM-DD HH24')", arg1);
1655  }
1656  ),
1657  COP_SEQ: (
1658  "nocolumn": True,
1659  "withalias": True,
1660  "code": string (*string cve, hash arg, reference<hash> psch) {
1661  string sql = sprintf("%s.nextval", arg.seq);
1662  if (arg.as);
1663 
1664  return sql;
1665  }
1666  ),
1667  COP_SEQ_CURRVAL: (
1668  "nocolumn": True,
1669  "withalias": True,
1670  "code": string (*string cve, hash arg, reference<hash> psch) {
1671  string sql = sprintf("%s.currval", arg.seq);
1672  if (arg.as);
1673 
1674  return sql;
1675  }
1676  ),
1677  COP_TRUNC_DATE: (
1678  "code": string sub(string arg1, auto arg) {
1679  if (!OracleTruncDate.hasKey(arg));
1680 
1681  // seconds are not part of TRUNC() in oracle
1682  if (exists OracleTruncDate{arg});
1683 
1684  // for timestamps
1685  return sprintf("cast (%s as date)", arg1);
1686  }
1687  )
1688  );
1689 
1691  const OracleTruncDate = (
1692  DT_YEAR : "'YYYY'",
1693  DT_MONTH : "'MM'",
1694  DT_DAY : "'DD'",
1695  DT_HOUR : "'HH24'",
1696  DT_MINUTE : "'MI'",
1697  DT_SECOND : NOTHING,
1698  );
1699 
1701  const OracleIopMap = DefaultIopMap + (
1702  IOP_SEQ: (
1703  "arg": Type::String,
1704  "immediate": True,
1705  "code": string (string cve, string arg) {
1706  return sprintf("%s.nextval", arg);
1707  },
1708  ),
1709  IOP_SEQ_CURRVAL: (
1710  "arg": Type::String,
1711  "immediate": True,
1712  "code": string (string cve, string arg) {
1713  return sprintf("%s.currval", arg);
1714  },
1715  ),
1716  );
1717 
1719  const OracleUopMap = DefaultUopMap + (
1720  COP_SEQ: (
1721  "nocolumn": True,
1722  "code": string (*string cve, string arg) {
1723  return sprintf("%s.nextval", arg);
1724  }
1725  ),
1726  COP_SEQ_CURRVAL: (
1727  "nocolumn": True,
1728  "code": string (*string cve, string arg) {
1729  return sprintf("%s.currval", arg);
1730  }
1731  ),
1732  );
1733 
1735  const OraclePseudoColumnHash = (
1736  "rowid": True,
1737  "rownum": True,
1738  "object_id": True,
1739  "object_value": True,
1740  "ora_rowscn": True,
1741  );
1742 
1743 public:
1744 
1745  private :
1746  // schema name
1747  string schema;
1748 
1749  // tablespace name
1750  *string tablespace;
1751 
1752  // is the table read only?
1753  bool readonly;
1754 
1755  // table comment
1756  *string comment;
1757 
1758  // dblink
1759  *string dblink;
1760 
1761  // Oracle server major version
1762  int ora_major;
1763 
1764  // helper flag to indicate if is the OracleTable real table or a view
1765  bool m_isView = False;
1766 
1767 public:
1768 
1769  constructor(AbstractDatasource nds, string nname, *hash opts) ;
1770 
1771 
1772 
1773 private:
1774  bool checkExistenceImpl();
1775 public:
1776 
1777 
1778 
1779 private:
1780  setTableInfoIntern();
1781 public:
1782 
1783 
1785  string getSqlName();
1786 
1787 
1789  bool isView();
1790 
1791 
1792 
1793 private:
1794  hash setSchemaTable();
1795 public:
1796 
1797 
1798 
1799 private:
1800  setDblinkSchema();
1801 public:
1802 
1803 
1804 
1805 private:
1806  hash setTable();
1807 public:
1808 
1809 
1810 
1811 private:
1812  string getUserSchema();
1813 public:
1814 
1815 
1816 
1817 private:
1818  string getDBString();
1819 public:
1820 
1821 
1823  string getSchemaName();
1824 
1825 
1827  *string getTablespaceName();
1828 
1829 
1831  *string getComment();
1832 
1833 
1834  bool readOnly();
1835 
1836 
1837 
1838 private:
1839  hash getColumnOptions();
1840 public:
1841 
1842 
1843 
1844 private:
1845  hash getColumnDescOptions();
1846 public:
1847 
1848 
1850 
1851 private:
1852  hash getSelectOptions();
1853 public:
1854 
1855 
1856 
1857 private:
1858  getSelectWhereSqlUnlocked(reference<string> sql, reference<list> args, *hash qh, *hash jch, bool join = False, *hash ch, *hash psch);
1859 public:
1860 
1861 
1862 
1863 private:
1864  doSelectOrderByWithOffsetSqlUnlockedImpl(reference<string> sql, reference<list> args, *hash qh, *hash jch, *hash ch, *hash psch, list coll);
1865 public:
1866 
1867 
1869 
1870 private:
1871  doSelectLimitOnlyUnlockedImpl(reference<string> sql, reference<list> args, *hash qh);
1872 public:
1873 
1874 
1875 
1876 private:
1877  Columns describeImpl();
1878 public:
1879 
1880 
1881 
1882 private:
1883  OraclePrimaryKey getPrimaryKeyImpl();
1884 public:
1885 
1886 
1887 
1888 private:
1889  Indexes getIndexesImpl();
1890 public:
1891 
1892 
1893 
1894 private:
1895  ForeignConstraints getForeignConstraintsImpl(*hash opts);
1896 public:
1897 
1898 
1899 
1900 private:
1901  Constraints getConstraintsImpl();
1902 public:
1903 
1904 
1905 
1906 private:
1907  string getSelectSqlName(*hash qh);
1908 public:
1909 
1910 
1911 
1912 private:
1913  Triggers getTriggersImpl();
1914 public:
1915 
1916 
1917  string getCreateTableSqlImpl(*hash opt);
1918 
1919 
1920 
1921 private:
1922  *list getCreateMiscSqlImpl(*hash opt, bool cache);
1923 public:
1924 
1925 
1926 
1927 private:
1928  string getCreateSqlImpl(list l);
1929 public:
1930 
1931 
1932 
1933 private:
1934  string getRenameSqlImpl(string new_name);
1935 public:
1936 
1937 
1938 
1939 private:
1940  AbstractColumn addColumnImpl(string cname, hash opt, bool nullable = True);
1941 public:
1942 
1943 
1944 
1945 private:
1946  AbstractPrimaryKey addPrimaryKeyImpl(string cname, hash ch, *hash opt);
1947 public:
1948 
1949 
1950 
1951 private:
1952  AbstractIndex addIndexImpl(string iname, bool enabled, hash ch, *hash opt);
1953 public:
1954 
1955 
1956 
1957 private:
1958  AbstractForeignConstraint addForeignConstraintImpl(string cname, hash ch, string table, hash tch, *hash opt);
1959 public:
1960 
1961 
1962 
1963 private:
1964  AbstractCheckConstraint addCheckConstraintImpl(string cname, string src, *hash opt);
1965 public:
1966 
1967 
1968 
1969 private:
1970  AbstractUniqueConstraint addUniqueConstraintImpl(string cname, hash ch, *hash opt);
1971 public:
1972 
1973 
1974 
1975 private:
1976  AbstractTrigger addTriggerImpl(string tname, string src, *hash opt);
1977 public:
1978 
1979 
1980 
1981 private:
1982  bool tryInsertImpl(string sql, hash row);
1983 public:
1984 
1985 
1986 
1987 private:
1988  *list getAlignSqlImpl(AbstractTable t, *hash opt);
1989 public:
1990 
1991 
1992 
1993 private:
1994  hash getQoreTypeMapImpl();
1995 public:
1996 
1997 
1998 
1999 private:
2000  hash getTypeMapImpl();
2001 public:
2002 
2003 
2004 
2005 private:
2006  hash getIndexOptions();
2007 public:
2008 
2009 
2010 
2011 private:
2012  hash getConstraintOptions();
2013 public:
2014 
2015 
2016 
2017 private:
2018  hash getTableCreationOptions();
2019 public:
2020 
2021 
2022 
2023 private:
2024  hash getAlignTableOptions();
2025 public:
2026 
2027 
2029 
2030 private:
2031  hash getWhereOperatorMap();
2032 public:
2033 
2034 
2036 
2037 private:
2038  hash getColumnOperatorMap();
2039 public:
2040 
2041 
2043 
2044 private:
2045  hash getInsertOperatorMap();
2046 public:
2047 
2048 
2050 
2051 private:
2052  hash getRawUpdateOperatorMap();
2053 public:
2054 
2055 
2057 
2058 private:
2059  *hash getPseudoColumnHash();
2060 public:
2061 
2062 
2064 
2065 private:
2066  *string getSqlValueImpl(auto v);
2067 public:
2068 
2069 
2071  string getColumnSqlName(string col);
2072 
2073 
2075  list getColumnSqlNames(softlist cols);
2076 
2077 
2079 
2082  string getBaseType();
2083 
2084 
2086  code getBulkUpsertClosure(hash example_row, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
2087 
2088 
2090  code getUpsertClosure(hash row, int upsert_strategy = UpsertAuto, *hash opt);
2091 
2092 
2094 
2095 private:
2096  code getUpsertInsertOnly(Columns cols, hash row, *hash opt);
2097 public:
2098 
2099 
2101 
2102 private:
2103  code getUpsertUpdateOnly(Columns cols, hash row, *hash opt);
2104 public:
2105 
2106 
2108  bool hasArrayBind();
2109 
2110 
2112 
2114  bool bindEmptyStringsAsNull();
2115 
2116 
2118 
2119 private:
2120  bool asteriskRequiresPrefix();
2121 public:
2122 
2123 
2124 
2125 private:
2126  *hash doReturningImpl(hash opt, reference<string> sql, list args);
2127 public:
2128 
2129 
2130 
2131 private:
2132  bool emptyImpl();
2133 public:
2134 
2135 
2136 
2137 private:
2138  setupTableImpl(hash desc, *hash opt);
2139 public:
2140 
2141 
2143 
2144 private:
2145  bool constraintsLinkedToIndexesImpl();
2146 public:
2147 
2148 
2150 
2151 private:
2152  bool uniqueIndexCreatesConstraintImpl();
2153 public:
2154 
2155 
2157 
2158 private:
2159  bool supportsTablespacesImpl();
2160 public:
2161 
2162 
2164 
2165 private:
2166  copyImpl(AbstractTable old);
2167 public:
2168 
2169  };
2170 };
represents an Oracle unique constraint
Definition: OracleSqlUtil.qm.dox.h:505
const Date
represents an Oracle materialized view
Definition: OracleSqlUtil.qm.dox.h:842
date date(date dt)
const COP_SEQ
const Hash
the OracleSqlUtil namespace contains all the objects in the OracleSqlUtil module
Definition: OracleSqlUtil.qm.dox.h:335
const UpsertAuto
const String
const DefaultIopMap
string sprintf(string fmt,...)
const OP_IN
const DefaultCopMap
hash< ColumnOperatorInfo > cop_cast(auto column, string arg, auto arg1, auto arg2)
*string tablespace
the tablespace name of the index (if supported)
Definition: OracleSqlUtil.qm.dox.h:427
bool logging
Flag if is loggign mode used.
Definition: OracleSqlUtil.qm.dox.h:847
*string view_type_owner
Owner of the type of the view if the view is a typed view.
Definition: OracleSqlUtil.qm.dox.h:663
represents an Oracle table
Definition: OracleSqlUtil.qm.dox.h:1416
*string tablespace
Name of the potential tablespace.
Definition: OracleSqlUtil.qm.dox.h:851
*string superview_name
Name of the superview.
Definition: OracleSqlUtil.qm.dox.h:667
bool enabled
True if the trigger is enabled, False if not
Definition: OracleSqlUtil.qm.dox.h:700
const COP_SEQ_CURRVAL
const True
const SZ_MAND
const CHAR
bool enabled
True if the constraint is enabled, False if not
Definition: OracleSqlUtil.qm.dox.h:454
bool enabled
True if the constraint is enabled, False if not
Definition: OracleSqlUtil.qm.dox.h:482
represents an Oracle procedure
Definition: OracleSqlUtil.qm.dox.h:803
number number(softnumber n)
const COP_YEAR_HOUR
hash< UpdateOperatorInfo > uop_substr(int start, *int count, *hash< UpdateOperatorInfo > nest)
const IOP_SEQ_CURRVAL
const False
*string tablespace
any tablespace for the unique key index
Definition: OracleSqlUtil.qm.dox.h:513
bool enabled
True if the constraint is enabled, False if not
Definition: OracleSqlUtil.qm.dox.h:510
represents an Oracle view
Definition: OracleSqlUtil.qm.dox.h:654
RangeIterator xrange(int start, int stop, int step=1, auto val)
list list(...)
const Float
int index(softstring str, softstring substr, softint pos=0)
const DT_DAY
const Boolean
const SZ_NUM
date microseconds(softint us)
const Binary
*string oid_text
WITH OID clause of the typed view.
Definition: OracleSqlUtil.qm.dox.h:661
bool exists(...)
hash< ColumnOperatorInfo > cop_trunc_date(auto column, string mask)
bool use_index
Flag if is index used.
Definition: OracleSqlUtil.qm.dox.h:849
const COP_YEAR_MONTH
const BLOB
the Oracle specialization for SqlUtil::AbstractDatabase
Definition: OracleSqlUtil.qm.dox.h:874
int byte_size
byte size of the column
Definition: OracleSqlUtil.qm.dox.h:352
const CLOB
const NOTHING
string type(auto arg)
const COP_TRUNC_DATE
the base class for Oracle code objects that cannot be renamed in place
Definition: OracleSqlUtil.qm.dox.h:755
represents an Oracle check constraint
Definition: OracleSqlUtil.qm.dox.h:477
represents an Oracle number column
Definition: OracleSqlUtil.qm.dox.h:408
represents an Oracle package
Definition: OracleSqlUtil.qm.dox.h:815
hash< ColumnOperatorInfo > cop_seq(string seq, *string as)
const Int
const COP_YEAR
string string(softstring str, *string enc)
represents an Oracle column
Definition: OracleSqlUtil.qm.dox.h:345
*string type_text
Type clause of the typed view.
Definition: OracleSqlUtil.qm.dox.h:659
OracleDatabase get_database(AbstractDatasource nds, *hash opts)
returns an OracleDatabase object corresponding to the arguments
const COP_CAST
hash< OperatorInfo > op_in()
const DT_MINUTE
hash< ColumnOperatorInfo > cop_seq_currval(string seq, *string as)
const DT_HOUR
*string body_src
package body source
Definition: OracleSqlUtil.qm.dox.h:820
const DefaultUopMap
const COP_YEAR_DAY
*string tablespace
any tablespace for the primary key index
Definition: OracleSqlUtil.qm.dox.h:575
*string view_type
Type of the view if the view is a typed view.
Definition: OracleSqlUtil.qm.dox.h:665
const DT_MONTH
represents an Oracle sequence
Definition: OracleSqlUtil.qm.dox.h:633
represents an Oracle trigger
Definition: OracleSqlUtil.qm.dox.h:695
hash< OperatorInfo > op_substr(int start, *int count, string text)
OracleTable get_table(AbstractDatasource nds, string nname, *hash opts)
returns an OracleTable object corresponding to the arguments
hash hash(object obj)
bool container_data
Indicates whether the view contains container-specific data.
Definition: OracleSqlUtil.qm.dox.h:669
const COP_SUBSTR
const OracleSchemaDescriptionOptions
oracle-specific schema description keys
Definition: OracleSqlUtil.qm.dox.h:898
const OP_SUBSTR
hash< ColumnOperatorInfo > cop_substr(auto column, int start, *int count)
const SZ_OPT
const IOP_SEQ
represents an Oracle foreign constraint
Definition: OracleSqlUtil.qm.dox.h:449
represents an Oracle type
Definition: OracleSqlUtil.qm.dox.h:777
const DT_YEAR
represents an Oracle primary key
Definition: OracleSqlUtil.qm.dox.h:570
string native_type
the native type of the index (if supported)
Definition: OracleSqlUtil.qm.dox.h:424
string join(string str,...)
const Number
represents an Oracle index
Definition: OracleSqlUtil.qm.dox.h:419
const DefaultOpMap
bool char_used
the column uses character semantics
Definition: OracleSqlUtil.qm.dox.h:350
const DT_SECOND
represents an Oracle function
Definition: OracleSqlUtil.qm.dox.h:791
the base class for Oracle code objects
Definition: OracleSqlUtil.qm.dox.h:724