How to call DB procedure with input and output parameters. Here I provide an example of ADF method which can be used with any number of in/pout parameters used in any order: public void runStatement(String stt, boolean commit, ArrayList<Map<String, Object>> pars) { CallableStatement st = this.getDBTransaction().createCallableStatement((stt), 0); …
Category: Database
How to create XMLIndex? When its worth to use it?
How to create XML index? Very general way with all default applied as follows: create index index_name on table_name (xmltype_column) indextype is xdb.xmlindex; Check the name of PATH table select path_table_name from user_xml_indexes where table_name = ‘table_name’ and index_name = ‘index_name’; Check what secondary indexes are used select c.index_name, c.column_name, c.column_position, e.column_expression from user_ind_columns c left outer join user_ind_expressions…
How to enable tracing in Oracle DB? How to read trace files?
There are many ways to enable tracing in Oracle DB, some are legacy and deprecated. One of latest and recommended method for 11gR2 – use of DBMS_MONITOR (find out more about it in Oracle reference) package. To enable whole instance tracing: execute dbms_monitor.database_trace_enable(waits => true, binds => true, instance_name => ‘instance_name’); To disable whole instance tracing: execute dbms_monitor.database_trace_disable(instance_name => ‘instance_name’);…
How to escape special symbols in Oracle SQL, PL/SQL (i.e. “&”)?
For escaping “&” or other special symbols in SQL or PL/SQL use function utl_i18n.escape_reference(string_needed_to_escape): select UTL_I18N.ESCAPE_REFERENCE(‘Here is my special symbol: ‘||chr(38)) from dual Example returns “Here is my special symbol: &“