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');

  • To enable current session tracing:
execute dbms_monitor.session_trace_enable(null,null, true, true);

  • To disable current session tracing:
execute dbms_monitor.session_trace_disable(null,null);

Note: procedure parameters can be adjusted to enable particular session or do other tuning.

  • In order to find desired tracing file easier, its recommended to set trace file identifier for current session:
alter session set tracefile_identifier ="any_string";

  • To check what are enabled traces at the moment:
select * from dba_enabled_traces

  • To check/set where trace files are persisted in the server:
show parameter user_dump_dest;
alter system set user_dump_dest='/u01/app/oracle/diag/......./trace';

  • Trace files for need to be processed by TKPROF utility to be more understandable and readable. Just execute a command line in the server:
TKPROF source_file.trc destination_file.txt

TKPROF can be tuned a bit. Find out more in Oracle guide.

ADF "Select many shuttle" (af:selectManyShuttle) component does not show all items in the leading list

   ADF "Select many shuttle" does not populate all items available in binded iterator. I don't know whether this is a bug or some other reason, but af:selectManyShuttle in the leading list populates just no more items then defined in the "RangeSize" property of iterator.
   To my mind it supposed to fetch all items in iterator in bunches of %RangeSize% (like other components do). But it is like it is for now and the workaround solution is simply to increase "RangeSize" of iterator to reasonable size. It applies to ADF 11.1.1.7.

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: &"

Problem with navigation using task flow actions - action is not executed after first button click

I experienced such strange ADF behavior regarding navigation using task-flow actions. Navigation doesn't work after single button press. After second press - works fine.

Preconditions:
  • Application "MyApp" with two pages - main.jspx and popup.jspx (You can download sample app)
  • popup.jspx belongs to bounded task-flow
  • main.jspx belongs to unbounded task-flow
  • Action "open" is defined in unbouded task-flow (see picture below)

  • CommandButton which executes action "open" is placed in main.jspx - it should open popup.jspx as inline dialog
  • In web.xml as  main.jspx is configured to be welcome file (see picture below)

 Strange behavior:
  1. Open application URL=http://localhost:7101/MyApp. Application is automatically redirected to URL:
    http://localhost:7101/MyApp/faces/main.jspx?_afrLoop=73412101452107&_afrWindowMode=0&_adf.ctrl-state=12hvlnrp73_4


    Pay attention to red-colored file name.
  2. Press button "open popup". After first press nothing happen except browser URL changes to:
    http://localhost:7101/MyApp/faces/main.jspx?_adf.ctrl-state=12hvlnrp73_4 
  3. Press button "open popup" once again. Now popup is shown and browser URL changes to:
    http://localhost:7101/MyApp/faces/main?_adf.ctrl-state=12hvlnrp73_4
What did happen here? Right after request application is forwarded to welcome file - its main.jspx. After button press action was not executed because its real source name (displayed in browser URL - "main.jspx")  was not the same as defined in task-flow ("main"). A bit strange, but ADF behaves like this - if there is such mismach it does't execute an action. But... Refreshes URL to one which conforms requirements. Need to remember specific ADF navigation behavior that in browser address bar shows URL of previous page, not the current one. Thats why in step2 we see the same main.jspx and just in step3 - see refreshed to "main".

To fix this (make button working right after the first click) available two solutions:
  • Rename view name in unbouded task flow "main" -> "main.jspx"
  • Change welcome file in web.xml "/faces/main/main.jspx" -> "/faces/main" (this case is implemented in my sample application available for download)
This case was tested in ADF 11.1.1.7.



How to implement contextual events between parent page and region?

   Contextual events are necessary when need to communicate between separate regions or between parent page and region in ADF application. Here I'll describe one approach to implement this. It is a little improvement of classic approach, because of event handling java interface is created and event consumer is implementing it. It is convenient, when need to separate data control implementation from specific event logic processing. In this sample description I'll focus on event creation steps having all other necessary objects already created. Sample application can be downloaded here. ADF version 11.1.1.7.

   So lets say we have a parent page (parent.jspx with parentPageDef.xml) and region (region.jspx with regionPageDef.xml and regionBean.java) in it, which needs to consume an event published by parent.

1. In parent.jspx create command button which will initiate an event. Then in property pallet find "Contextual Events" and add one with name "MyEvent".


2. Create java interface "MyEventHandler" for classes which will handle events:


3. Create java class "MyEventReceiver" to receive events:



4. Generate data control for "MyEventReceiver":


 5. Create RegionBean.java with custom event handler:


 6. Add previously created data control binding to regionPageDef.xml:


7. Finally create event mapping in parentPadeDef.xml:
 Open event mapper:

Provide event details:

8. Done! Test it! :) Implemented sample application can be downloaded here.

ADFdi Excel worksheet exception on upload operation: ArgumentException: invalid area string: N5:Q5,S5

Full exception stack:

ArgumentException: invalid area string: N5:Q5,S5
Source: adfdi-excel-om-api
Stack:
   at oracle.adf.client.windows.excel.om.api.ExcelRangeAreas.ParseRangeAddress()
   at oracle.adf.client.windows.excel.om.api.cache.ExcelRangeCache.get_RangeAreas()
   at oracle.adf.client.windows.excel.om.api.ExcelRangeReader..ctor(ExcelRange range, Int32 maxRows)
   at oracle.adf.client.windows.excel.runtime.uicomponent.table.DEGUploadHelper.InitDataRangeIterator()
   at oracle.adf.client.windows.excel.runtime.uicomponent.table.TableOperationHelper.ProcessRows(Boolean abortOnFail)
   at oracle.adf.client.windows.excel.runtime.uicomponent.table.TableOperationHelper.Run(RTColumn rtColumnFlaggedOrChanged, Int32 cRowsFlaggedOrChanged, Boolean abortOnFail)
   at oracle.adf.client.windows.excel.runtime.uicomponent.table.DEGUploadHelper.Upload(Boolean haveOptions, Boolean abortOnFail, Boolean downloadAfterUpload)
   at oracle.adf.client.windows.excel.runtime.uicomponent.RTDataEntryGrid.Invoke(RTComponentAction rtAction)
   at oracle.adf.client.windows.excel.runtime.uicomponent.RTActionCollection.DoInvoke(RTColumnCollection parent)


This exception is thrown when data upload to server is requested. Range N5:Q5,S5 specifies cells that are being updated. In my case it happend only under following conditions:
  • In table component there were columns with decimal data
  • There were some read only columns and some editable. Their order was mixed (i.e. [editable] [editable] [readonly] [editable])
  • Client computer OS locale was different from server locale (client - Lithuanian, server - US)
After some time spent on investigating, I came up with following solutions/workarounds which forced things work:
  • Making client locale same as server's. In my case I switched to US. Locale became important after decimal numbers appeared on the table. Number format in Lithuanian and US locale is different (decimal part is separated with comma or dot). When there is not available decimal numbers everything works fine. But locale unification solved problem.

  • Making monolithic editable range in the table. This means that there can not be any read only columns interfered between editable columns. In the picture wrong example:
Here is correct example:

After changing editable/readonly column order like in preceding picture, everything works fine.

So this is two solutions I came up, believe that there should be smarter one, but for now I have only those. If  You experienced some other aspects of this problem, please, post a comment!
This issue is relevant to ADF version 11.1.1.7

Latest update!
I experienced same error when trying to update records using different than English locale. Try to change  more then one row, but with at least one unchanged row between them (i.e. change 4'th and 6'th row, 5th stays unchanged). After trying to upload changed data, same error is displayed. We've registered SR at Oracle support and got bug confirmed. And they made backport fix for 11.1.1.7 version (in 12c it is fixed). Related document ID in My Oracle Support - 1590786.1.

ADF dekstop integration (ADFdi) plugin with macro enabled file doesn't work when opened from MS Internet Explorer. File is opened but nothing is loaded. Why?

Actually this is not an issue or bug, it is not even directly related to ADFdi. Everything work as supposed to :)

ADFdi plugin can't function properly, because Excel opens file in a protected view. This happens because AES (Attachment Execution Services) marks file as downloaded from internet (only MS IE and Outlook can do this). Before showing file contents Excel checks AES records and automatically switch to protected view.

Possible solutions:
  • Disable protected view for files downloaded from internet ( [Open Excel] -> File -> Options -> Trust Center -> Trust Center Settings -> Protected View -> [Uncheck "Enable Protected View for files originatin from the Internet"])
  • Change Windows OS localhost real location (sounds like a hack). To accomplish this - open windows hosts file (sample location: C:\Windows\System32\drivers\etc), add record "[application servers, where resides Excel file, IP] localhost"
  • Some other

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.

Be careful not to choose too many loggers with quite fine logging level. Otherwise log output can become too massive, flooded and be difficult to find useful info in there.

In standalone WebLogic can accomplish this task using Fusion Middleware Control:

Open Oracle Enterprise Manager, right click on ADF server (i.e. adf_server1), choose Logs -> Log Configuration:


Here You find similar structure like in JDeveloper:


ADFDI-00502: The client was unable to establish an unauthenticated session with the web application. What can be the reason?

I have experienced such error. The reason was, that web application didn't know, that servlet "adfdiRemoteServlet", responsible for Excel workbook connectivity, should be secured. Problem happens only when ADF security is set to "ADF Authentication and Authorization". This means, that ADF takes care not just for login, but for access to various resources (in our case it is adfdiRemoteServlet) as well. When security is set to "ADF Authentication", everything works fine without any additional configuration.
So what is the solution if You want to use ADF authentication and authorization as well? There is need to edit application web.xml file. You need to say, that adfdiRemoteServlet is also secured. To complete this, just append "security-constraint" XML element like this:

<security-constraint>
    <web-resource-collection>
      <web-resource-name>adfAuthentication</web-resource-name>
      <url-pattern>/adfAuthentication</url-pattern>
      <url-pattern>/adfdiRemoteServlet</url-pattern>
    </web-resource-collection>
    <auth-constraint>
      <role-name>valid-users</role-name>
    </auth-constraint>
  </security-constraint>

The line in bold is newly added. It is just additional URL pattern for adfdiRemoteServlet.
Thats it! It should work now.
This solution tested for ADF version 11.1.1.7

How to configure local integrated WebLogic non-SOA domain to access BPM worklist on separate SOA-enabled domain?

ADF and BPM task flow on separate domains? No doubt this is frequent issue for developers working with ADF/BPM.There are several advantages to have it working:
  • No need to install SOA stuff locally if You're developing just ADF
  • Faster deployments to integrated WebLogic
  • Possibility of debugging
  • Isolated ADF enviroment for developing
In my case the last one was most important. An application I was working on had interaction with BPM worklist through BPM API. Steps which I completed to access BPM worklist on remote SOA enabled domain from my integrated non-SOA domain:

  • Create foreign JNDI provider in local integrated non-SOA WebLogic:

 Provider URL - link to SOA infrastructure on SOA enabled domain.

  • Create foreign JNDI provider links:


  • Deploy these 4 libraries into integrated WebLogic:

 Those libraries can be found in "%MIDDLEWARE_HOME%\jdeveloper\soa\modules". Of course SOA and BPM jdeveloper extensions should be installed to find these libraries here.

  • Inject following grant for bpm-services.jar into system-jazn-data.xml (locatedin: ORACLE_WEBLOGIC_INSTALL/user_projects/domains/your_domain_name/config/fmwconfig )
<grant>
  <grantee>
   <codesource>
    <url>file: ORACLE_JDEV_HOME/jdeveloper/soa/modules/oracle.soa.workflow_
11.1.1/bpm-services.jar</url>
   </codesource>
  </grantee>
  <permissions>
   <permission>
    <class>oracle.security.jps.JpsPermission</class>
    <name>VerificationService.createInternalWorkflowContext</name>
   </permission>
  <permission>
  <class>oracle.security.jps.service.credstore.CredentialAccessPermission
  </class>
  <name>credstoressp.credstore.BPM-CRYPTO.BPM-CRYPTO</name>
  <actions>read,write</actions>
  </permission>
  <permission>
   <class>oracle.security.jps.JpsPermission</class>
   <name>IdentityAssertion</name>
   <actions>*</actions>
  </permission>
 </permissions>
</grant>

  • Establish trust between non-SOA (integrated WebLogic) domain and SOA-enabled domain:
To do this follow steps:
  • Select domain name (i.e. DefaultDomain) in left-hand domain structure menu.
  • Choose "Security" tab, "General" sub-tab
  • Expand "Advanced" pane
  • Enter 



  • Make sure your application (which runs on integrated WebLogic) has proper URL set for SOA-enabled server in wf_client_config.xml (by default it is located in "Your app"\model\src) file:




When a domain is created, a unique credential is generated for the domain. If you want to establish trust between two or more domains, decide on a credential that will be shared by the domains, then specify it here and in the other domains. In our case it is non-SOA and SOA-enabled domains.

Thats it. It was enough for me to get things working. If it is not enough for You, check these links which helped me to set up this enviroment:


      Weblogic version 10.3.6

      Submit of ADF selectManyShuttle pre-selected values without pre-selection change

      Our requirement is to implement af:selectManyShuttle with preselected values. New records in database table must be created according to selected values. If user change something in the selection, everything works fine. But if user agrees with default selection and does not want to do any changes and just submits a form, we have a problem - nothing is submitted.



      af:selectManyShuttle is implemented with managed bean behind.

      Component:
       <af:selectManyShuttle value="#{pageFlowScope.MyBean.selectedValues}"
                                id="sms1"
                                leadingHeader="#{bundle.myHeader1}"
                                trailingHeader="#{bundle.myHeader2}">
         <f:selectItems value="#{bindings.Id.items}" id="si3"/>
      </af:selectManyShuttle>
      

      Bean:


      public class MyBean  {
          List selectedValues;
          int shuttleCounter = 0;
          public MyBean() {}
       
          public void setSelectedValues(List selectedValues) {
         // Do some processing for data persisting
          }
          public List getSelectedValues() {
              if (this.selectedValues == null) {
                  this.selectedValues = new ArrayList();
          //Some code to fill preselected values
              } else if (this.shuttleCounter == 0) {
                  this.shuttleCounter++;
                  this.selectedValues.add(-1);
              }
              return this.selectedValues;
          }

      So why is this problem happening?

      Select many shuttle on page load invokes getSeletedValues() to fill trailing list. Then when user edits form and press submit, getSelectedValues() is invoked again to check if something is changed. If there are changes in trailing list, component invokes setSelectedValues(List selectedValues) and everything is processed what is necessary. But if user doesn't shuttle enything and leaves default pre-selected trailing list, component doesn't see any changes ant it decides not to execute setSelectedValues(List selectedValues), because obviously there is no point to do that. But... here is no point only if we need to do update in some list. In our case we create pure new records and there is big point to execute setSelectedValues(List selectedValues) nevertheless the user has changed sometging or not.

      What workaround could be?

      Just to cheat component by returning fake changed selected list during the second getSelectedValues() invoke. In the sample I add "-1" index to force change. This index is not processed in setSelectedValues.  af:selectManyShuttle just throws warning "Could not find value:-1 of type:java.lang.Integer among list of SelectItems" and process the rest as required.

      How to filter OSB logging and redirect log records to any desirable destination?

      The best practice to accomplish this task is to use Weblogic server startup class. In this class we should take Weblogic server "Logger" object and use it to attach additional appender. This appender can be implemented to redirect log messages to any destination You prefer. Log4j supports dozen of standard destinations, providing built in appenders like JDBCAppender, JMSAppender, FileAppender and many others. Lets say we need to redirect log messages from Oracle Service Bus to separate file. We are going to implement this approach on WebLogic version 10.3.5.0.

      What is Weblogic server startup class?

      This is general kind of java class which main method is called during server startup. Here is an example:

      public class StartupClass {
          public static void main(String[] args) {
      // Do some startup processing here
                }
      }


      Steps to configure Weblogic startup class for log redirecting to separate file

      • Create Log4j custom filter class to filter messages from "ALSB Logging" logger:
        package logging;
        
        import org.apache.log4j.spi.Filter;
        import org.apache.log4j.spi.LoggingEvent;
        
        public class ALSBFilter extends Filter {
        
         @Override
         public int decide(LoggingEvent event) {
          if (event.getLoggerName() == "ALSB Logging") {
           return NEUTRAL;
          }
          return DENY;
         }
        }
        
      • Create general java class with log4j appender code inside. Class example:
        
        
        package logging;
        
        import org.apache.log4j.Logger;
        import org.apache.log4j.PatternLayout;
        import weblogic.logging.log4j.Log4jLoggingHelper;
        import weblogic.logging.log4j.WLLog4jLevel;
        import org.apache.log4j.RollingFileAppender;
        
        public class AppenderStartup {
        
         public static void main(String[] args) {
        
          try {
        
           Logger serverLogger = Log4jLoggingHelper.getLog4jServerLogger();
           RollingFileAppender fa = new RollingFileAppender();
           fa.setFile(args[0]);
           fa.setLayout(new PatternLayout("%d %-5p [%c{1}] %m%n"));
           fa.setThreshold(WLLog4jLevel.INFO);
           fa.setAppend(true);
           fa.setMaxFileSize("10MB");
           fa.setMaxBackupIndex(2);
                                fa.addFilter(new ALSBFilter());
           fa.activateOptions();
           serverLogger.addAppender(fa);
        
          } catch (Exception e) {
           e.printStackTrace();
          }
         }
        }
        
      Any appender option could be configurable passing an argument to main method. In the case of example just file name is received through arguments.
      • Export either ALSBFilter and AppenderStartup classes to JAR.
      • Login to Weblogic console and register newly created class as startup one. To do this follow the steps:
        • In the left-hand domain structure menu expand "Enviroment" and select "Startup and shutdown classes".
        • Check "Startup class", click next
        • In the fileld "Name" enter desired name for this class to be listed in weblogic (not actual class name, i.e. My-startup-class-1). In the field "Class name" enter actual class name(our example case - AppenderStartup). Click next.
        • Select target server where this class should be executed during startup (i.e. "osb_server1"). Click finish.
        • Click on newly registered class in the "Summary of Startup and Shutdown Classes" table. Now few more configuration options are available. In the field "Arguments" enter file name for log. Save.
      • Enable "Log4j" logging implementation in Weblogic:
        Environment --> Servers --> [Target server](i.e."osb_server1") -> Logging-> Advanced ->
        Logging implemention -> Select LOG4J
      • Ensure to have these libs on server classpath (recomended just to put under domain lib folder - %DOMAIN_HOME%/lib):
        • log4j-1.2.15.jar
        • wllog4j.jar
        • AppenderStartup.jar (our startup class inside)
      • Restart server. Check for logs appearing in new log file.