This is a question I’ll try to answer here. Quite frequently it happens to face SQL code, where extremum dates take place. From a first glance it look OK to use like to_date(‘3000.01.01′,’YYYY.MM.DD’)) for indicating infinity, something that lasts forever. Nobody could bet that no one of our developed nice IT system will even exist at such time point 🙂…
Category: SQL and PL/SQL
Get “ORA-56901: non-constant expression is not allowed for pivot|unpivot values” ? Not a big deal, check a workaround!
Problem Get “ORA-56901: non-constant expression is not allowed for pivot|unpivot values” while trying to execute SQL statement with pivot clause. Lets consider this tiny sample: Here is the table: SQL statement with PIVOT clause and constant expressions, which executes successfully: SQL statement with PIVOT clause and dynamic expressions, which fails: Solution Workaround – just don’t use PIVOT at this…
ADF method for calling DB procedures, executing other SQL statements with input and output parameters
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); Â Â Â …
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: &“
How to change logging level of standalone or integrated WebLogic in JDeveloper? For example how to capture SQL executed?
In Integrated WebLogic (JDeveloper) this can be done with just few clicks: Open “IntegratedWeblogicServer – Log” window. On the right-hand top corner click “Actions” and choose “Configure Oracle Diagnostic Logging” For getting SQL statements in the output, in the opened logging.xml choose “oracle.jbo” logger with desired logging level. Level “Finest” will generate maximum output. SQL staements shoud definitely be there.…