How to remote debug PL/SQL with Oracle SQL Developer

There are quite a few posts (see the References section at the bottom) on how to debug PL/SQL remotely. That is when you want to step through another session that is running some PL/SQL procedure. Still I didn't find it obvious to get it working. Mainly because none of the blog posts have a diagram on how all components are connected and which component tries to connect with the other component(s). Therefore this post, including..... a diagram! :)

Setup
- on a Windows 7 desktop PC running SQL developer with IP xxx.yyy.19.84
- remote database running in VM with IP xxx.yyy.19.104. This one actually is running on the above desktop machine, but that should not change much if it would be on a separate machine, except there might be some firewall(s) in between.

In short this is what we are trying to achieve:

1. Start up the remote debug listener on port 4000 on the desktop machine
2. Start up SQl*Plus connecting to the remote database
3. Connect to the remote debug listener from the database. This might be the tricky part for some: SQL*Plus is connected to the remote database, so the DBMS_DEBUG_JDWP.CONNECT_TCP call is executed from that database! So that machine (.104) needs to be able to get to the machine *.84) with the remote debug listener listening on port 4000
4. Execute the stored procedure from the package you want to debug and SQL Developer will stop at the set breakpoints

Detailed steps

1. As mentioned in all the referenced blog posts, right-click on the connection and select Remote Debug....
2. In that popup enter:
   
   
   If done correctly, you should see something like:
   
   
   If you get a message saying "The debugger is not able to listen for JPDA using the specified parameters." then there is something wrong with the parameters you entered. Yes that's all I could figure out too... Potentially you entered the wrong Local address or maybe there's already something listening on the specified port.
   Note that the Local address is the IP of the machine SQL Developer is running on. Unless your database is running on that same machine, you can't use 127.0.0.1 (localhost). You have to use the full IP address.
3. Since the database (which is on xxx.yyy.19.104) needs to connect to the SQL Developer remote debugger listener, it must be able to get to that machine (xxx.yyy.19.84). You can test this by trying to telnet to it and the port the listener is listening on from the .104 machine:

   $ telnet xxx.yyy.19.84 4000 Trying xxx.yyy.19.84... Connected to MyPC (xxx.yyy.19.84). Escape character is '^]'. JDWP-Handshake ^]quit

   Look for the JDWP-Handshake string. If you get that, you know it can get to the remote debug listener on the .84 machine. Also note that when you quit the telnet session, the listener is stopped, so make sure to start it again!
4. Start up an SQL*Plus session from the .84 machine to the remote database. For example:

   sqlplus user/passwd@xxx.yyy.19.104/oracle

5. Connect to the remote lister on the .84 machine:

   SQL> set serveroutput on; SQL> execute DBMS_DEBUG_JDWP.CONNECT_TCP('xxx.yyy.19.84',4000);

   Note it is the .84 machine and of course port 4000 where the remote debugger is listening on.
6. In SQL Developer, set a breakpoint in a procedure you want to debug, e.g test_me. Tip: as a first try, set it at the first line of code in the method test_me, then you know for sure that if it gets called, it should stop there definitely.
7. Now call that procedure from the SQL*Plus commandline, e.g:

   DECLARE P_ANR VARCHAR2(25); P_RESULT VARCHAR2(4000); BEGIN P_ANR := 666; SOME_PCK.test_me( P_ANR => P_ANR, P_RESULT => P_RESULT ); DBMS_OUTPUT.PUT_LINE('P_RESULT = ' || P_RESULT); END; /

   
   Note the test_me method has an IN and an OUT parameter.
8. Your SQL Developer should now stop in the breakpoint you've set, see below SQL Developer screenshot of the Remote Debug session log window:
   

Remarks
I did not seem to need to run:

GRANT DEBUG CONNECT SESSION TO some_user; GRANT DEBUG ANY PROCEDURE TO some_user; /

You might still need to execute these commands if you get the permission errors you see in Step 5 here.

If you have a firewall on the .84 machine or between the two machines, you might need it to allow port 4000 to be accessed from the (other) DB machine...

References
Using SQL Developer to debug your anonymous PL/SQL blocks
Remote debugging with SQL Developer revisited
Application Express Community - Remote debugging
Sue's Blog... again... - Remote debugging with SQL Developer
Barry McGillin - Remote debugging with SQL Developer
Debug ApEx App with SQL Developer
Use Oracle SQL Developer to aid Oracle Application Express development
OTN Discussion Forums - Remote Debugging

Lessons learned Seam 2.2 project

Quick post with some bullet points of lessons learned during my last (and first) Seam project:

• Seam 2.2
• Hibernate 3.3.1
• JEE 5
• EJB 3.0
• JSF 1.2
• Richfaces 3.3
• JBoss 5.1
• Drools 5
• SQLServer 2008
• MySql 5.x
• TestNG
• Hudson

Seam
- To have a navigation for a method with a parameter like exportSelected(String value): see Seam navigation based on a function with parameters
- If your Seam app keeps re-deploying (restarting) when using JBoss within Eclipse: delete all files that end with 'dia' in your WEB-INF dir. See: Seam keeps redeploying For example, I modified pages.xml, which apparently created a pagesdia (or similar) file... after removing that one, it worked fine again.

Hibernate
- To see which validator fails on a persist():

try { entityManager.persist(verbinding); } catch (InvalidStateException e) { for (InvalidValue invalidValue : e.getInvalidValues()) { System.err.println("--------------> create(): exception Instance " + "of bean class: " + invalidValue.getBeanClass().getSimpleName() + " has an invalid property: " + invalidValue.getPropertyName() + " with message: " + invalidValue.getMessage()); } throw new RuntimeException(e); }

MySql
- To see data in better format in MySQL: show engine innodb status\G (so add the \G to a query)

Hibernate/JPA
- NamedQueries are loaded and parsed at startup time, so that's an advantage above em.createQuery() which are only parsed and evaluated at runtime. So with NamedQueries you get the errors sooner.

Drools
To solve this error in Drools decision table .XLS spreadsheet:

[testng] DEBUG [test.service.drools.DroolsHandling] getKnowledgeBase: getting path info from settings service [testng] DEBUG [test.service.drools.DroolsHandling] getting test.service.drools.DroolsHandling ruletable file: HandlingModelTest.xls [testng] DEBUG [test.service.drools.DroolsHandling] getKnowledgeBase: Trying to open a File [testng] WARN [jxl.read.biff.NameRecord] Cannot read name ranges for Excel_BuiltIn__FilterDatabase_1 - setting to empty [testng] WARN [test.service.drools.DroolsHandling] There are errors in the decision table file [testng] WARN [test.service.drools.DroolsHandling] Decision table error: message=[ERR 102] Line 9:16 mismatched input '"DroolsHandling6"' expecting ']' in rule "DroolsData_12" in pattern stringDataset [testng] WARN [test.service.drools.DroolsHandling] line: 9 [testng] WARN [test.service.drools.DroolsHandling] Decision table error: message=[ERR 102] Line 9:35 mismatched input '"Prefab"' expecting ']' in rule "DroolsData_12" in pattern stringDataset [testng] WARN [test.service.drools.DroolsHandling] line: 9 [testng] WARN [test.service.drools.DroolsHandling] Decision table error: message=[ERR 102] Line 22:16 mismatched input '"DroolsHandling6"' expecting ']' in rule "DroolsData_12" in rule "DroolsData_13" in pattern stringDataset [testng] WARN [test.service.drools.DroolsHandling] line: 22

Make sure your definition has all the cells merged that contain a condition! For example, check the red arrow pointing at the border of the cells between G9 and H9.

The last cell for the condition Prefab is not merged (i.e one cell) with all the other conditions. Here's now how it should be: see again the red arrow, there's no cell separation anymore, all condition columns are now merged into 1 cell.

- A space in a condition in a decision spreadsheet can cause a non-match! E.g GATE VALVE is not matched. Fixed it by always replacing a ' ' with an '_'.

BlackBerry Playbook Tablet for Android porting

Houston, we got it in the mail: a free RIM Blackberry Playbook Tablet because I submitted on time an Android app to the BlackBerry App World. The Android app runs within the Playbook Android emulator (yes so the app is native Android! Pretty cool I'd say!

Booting
Below a few bootup screenshots:

The bootup time for the device is quite long, definitely longer than the Samsung Galaxy Tab 10.1 for example.

Home-screen and taskmanager
This is what the home-screen looks like:

This is what the taskmanager looks like:

The way to go back to the home-screen or switch to another app is by swiping from the button of the screen up.

The simpel app running
And the app runs fine on the real device too :) Those Android figures are also checking it out as you can see...

Those four buddies are called: Don Pablo Calaveroid, Longevity, Fortune and Blessing. The last three were released because of the chinese Year of the Dragon. Too bad they sent one with still the USA power plug attached to it. Luckily I had a converter (as the globetrotter I am ;)...

This is how the tablet 7" screen compares to the Samsung 10.1 (note both screens are very clear, the picture is only trying to show the difference in size of 10" vs 7"):

Tetris just runs fine on it (but that's a native app), the screen is very clear:

Some generic feedback:

• The tablet smells rubbery because it has rubber on the back. Probably done as anti-slip, but even your fingers start to smell like it.
• A bit annoying was that you have to watch the Basics tutorials when doing the setup.
• Like the virtual keyboard, feels like lot less typos than similar sized touch screens

Porting
During converting the app to the PlayBook Android 2.3.3 or higher runtime, I ran into a few issues:

• For emailIntent.putExtra(android.content.Intent.EXTRA_TEXT, emailBody) statements needed to add .toString() to emailBody because otherwise you get an exception telling that EXTRA_TEXT is not a String.
• After each re-install of the app on the emulator, nothing seems to be remembered of the preferences. E.g had to accept the EULA each time after a new deploy, while this does not happen on the "regular" Android emulator. Also works fine on the real Playbook device.
• In my app the user can swipe left to right and vice versa to browse through tips. On the Playbook emulator the text on bottom of the screen was not always correctly updated. It was lagging behind.
• When trying to open the browser with an Intent and URL in the simulator, it says 'No network connection'. Strange, the pre-installed browser on the home screen works just fine... Seems similar to this reported issue.

JBoss exception Transaction is not active: javax.transaction.RollbackException: The transaction is not active!

Sometimes you get this exception when using JBoss (5.1.0 and Seam 2.2 in my case):

14:08:17,312 WARN [arjLoggerI18N] [com.arjuna.ats.arjuna.coordinator.TransactionReaper_18] - TransactionReaper::check timeout for TX -3f57fd63:6ae:4f5df31e:b7in state RUN 14:08:17,312 WARN [arjLoggerI18N] [com.arjuna.ats.arjuna.coordinator.BasicAction_58] - Abort of action id -3f57fd63:6ae:4f5df31e:b7 invoked while multiple threads active within it. 14:08:17,312 WARN [arjLoggerI18N] [com.arjuna.ats.arjuna.coordinator.CheckedAction_2] - CheckedAction::check - atomic action -3f57fd63:6ae:4f5df31e:b7 aborting with 1 threads active! 14:08:17,343 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null 14:08:17,343 ERROR [JDBCExceptionReporter] Transaction is not active: tx=TransactionImple < ac, BasicAction: -3f57fd63:6ae:4f5df31e:b7 status: ActionStatus.ABORTING >; - nested throwable: (javax.resource.ResourceException: Transaction is not active: tx=TransactionImple < ac, BasicAction: -3f57fd63:6ae:4f5df31e:b7 status: ActionStatus.ABORTING >) 14:08:17,359 ERROR [TxPolicy] javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.GenericJDBCException: Cannot open connection 14:08:17,375 ERROR [TxPolicy] javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.GenericJDBCException: Cannot open connection 14:08:17,453 WARN [arjLoggerI18N] [com.arjuna.ats.arjuna.coordinator.TransactionReaper_7] - TransactionReaper::doCancellations worker Thread[Thread-10,5,jboss] successfully canceled TX -3f57fd63:6ae:4f5df31e:b7 14:08:17,500 WARN [arjLoggerI18N] [com.arjuna.ats.arjuna.coordinator.BasicAction_40] - Abort called on already aborted atomic action -3f57fd63:6ae:4f5df31e:b7 14:08:17,515 ERROR [AsynchronousExceptionHandler] Exception thrown whilst executing asynchronous call javax.ejb.EJBTransactionRolledbackException: Transaction rolled back at org.jboss.ejb3.tx.Ejb3TxPolicy.handleEndTransactionException(Ejb3TxPolicy.java:54) at org.jboss.aspects.tx.TxPolicy.endTransaction(TxPolicy.java:175) at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:87) at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:190) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.ejb3.security.RoleBasedAuthorizationInterceptorv2.invoke(RoleBasedAuthorizationInterceptorv2.java:201) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.ejb3.security.Ejb3AuthenticationInterceptorv2.invoke(Ejb3AuthenticationInterceptorv2.java:186) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:41) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.ejb3.BlockContainerShutdownInterceptor.invoke(BlockContainerShutdownInterceptor.java:67) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.aspects.currentinvocation.CurrentInvocationInterceptor.invoke(CurrentInvocationInterceptor.java:67) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102) at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:176) at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:216) at org.jboss.ejb3.proxy.impl.handler.session.SessionProxyInvocationHandlerBase.invoke(SessionProxyInvocationHandlerBase.java:207) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.jboss.seam.util.Reflections.invoke(Reflections.java:22) at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:32) at org.jboss.seam.intercept.ClientSideInterceptor$1.proceed(ClientSideInterceptor.java:76) at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56) at org.jboss.seam.async.AsynchronousInterceptor.aroundInvoke(AsynchronousInterceptor.java:52) at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.jboss.seam.util.Reflections.invoke(Reflections.java:22) at org.jboss.seam.util.Reflections.invokeAndWrap(Reflections.java:144) at org.jboss.seam.async.AsynchronousInvocation$1.process(AsynchronousInvocation.java:62) at org.jboss.seam.async.Asynchronous$ContextualAsynchronousRequest.run(Asynchronous.java:80) at org.jboss.seam.async.AsynchronousInvocation.execute(AsynchronousInvocation.java:44) at org.jboss.seam.async.QuartzDispatcher$QuartzJob.execute(QuartzDispatcher.java:243) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529) Caused by: javax.transaction.RollbackException: [com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] [com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] The transaction is not active! at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1414) at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:137) at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:75) at org.jboss.aspects.tx.TxPolicy.endTransaction(TxPolicy.java:170) ... 47 more

For 99% sure this is caused by a timeout. Unfortunately you can't immediately see that from the exception.

The fix
Increase the timeout in transaction-jboss-beans.xml in your JBoss deploy dir. For example if you have JBoss installed in:

C:\jboss-5.1.0.GA\server\default\deploy

then open transaction-jboss-beans.xml and set the field transactionTimeout much higher. E.g at 3000 (default is 300).
In older JBoss version this setting used to be in default/conf/jboss-server.xml, see here.
The official documentation can be found here.

Why cancel-button now also first on Android?

Just wondering about this big time...

Why is Android switching the standard buttons order for Android 4.0 and higher? See for example this screenshot, the cancel button is now the first button!
I was always taught that what the user most likely wants to do should be first, and so the dismissive button last.
And Cancel is usually not what to user wants to do!

Apple is doing it also in their iOS, is Android now just plainly copying it?
Definitely prefer Cancel at the end, (western) reading occurs from left to right, so now one always has to read/skip the dismissive button!

From the expert the conclusion is just to be consistent. That means at least within the Android platform it is not consistent between pre 4.0 and 4.0+... I think an unneeded and unnecessary change.

Summary of presentation with tips on creating visually appealing Android applications that scale to various screen sizes

Interesting points from a presentation in which Eric Burke shares tips on creating visually appealing Android applications that scale to various screen sizes.
The session focuses on custom views, rounded corners, scalable drawables, gradients, shine, holograms etc.
Eric is the St. Louis Engineering Manager and Android Tech Lead with Square.

06:00 Use alpha compositing with anti-aliasing, not canvas.clipPath() because you get jagged pixelated corners

17:25 triangular shine

29:00 Check the imeOptions for EditTexts/keyboard input. E.g imeOptions="actionNext" for example. Or "actionDone". NOTE: does not work on HTC Sense UI! So "actionNext" seems safest (might work, might not, but no harm done). For "actionDone" you still need a button for it on your screen anyway.
31:00 SafeViewFlipper code because the standard one crashes your app eventually (guaranteed) on pre-HoneyComb!
35:00 Holograms! Using the accelerator it acts as if it's shiny. Cool!
39:00 demo of cards sliding in/out and hologram on emulator

Android Tablet project lessons learned

A little while ago I had the opportunity to work on a tablet-only app, running Android 3.1 or higher.
The app consists of only one screen, on which everything can be done.Therefore the app basically consists of one Activity and several Fragments (about 4 to be precise).
The Activity controls the Fragments for potential re-use of the Fragments, as recommended. As a basis the official Honeycomb Gallery project was used. Quite unique to this app was though that it has to have 3 columns instead of the usual 2...

To get a feeling for the app, here are some screenshots:


The very first time the user has to log in, after which a sync with the server is done. Note the progress bars showing in the background.


Synchronisation in progress... Notice that the title is indented for some reason. Needs some investigation why it shows here, and not in the Login DialogFragment...


After the data has been loaded, the user can filter all data by selecting from the dropdowns, and selecting items in the lists. The rightmost column then shows a list of questions, dependent on the selected filters. Many types of questions and actions had to be supported; beside the ones you see, also built were dropdowns, multiple radiobuttons, view images and PDFs, etc.

Lessons learned

• Prevent EditText in a ListView from losing focus/text disappearing:
  

  android:windowSoftInputMode="adjustPan"

  
  


• Sometimes Eclipse/DDMS loses the connection with the emulator, it just can't "see it" anymore. For example when you want to run an app in the emulator, the start dialog does not show your running emulator. To fix that run 'adb kill-server', that restarts the server. After this the emulator should be detected again.
  


• You can't disable a RadioGroup such that it disables it contained radiobuttons. You have to disable each radiobutton within that radiogroup seperately.
  


• Note that all Activities in your app might have had onDestroy() called, but the Application instance might still be around! So watch this when you have static variables, like a reference to the database... it might still be open when your Activity's onCreate() is called again!
  


• The 4.0 emulator was taking ages to start up (gave up after about 30mins). Then I compared the settings of my 3.1 AVD with the new 4.0 AVD. It turns out that the hardware property 'Abstract LCD density' setting of the 4.0 AVD is set by default to 240, which means high density. That implies a lot of pixels to draw. Read somewhere that that's one of the issues of a slow starting AVD. So I changed that property to be the same as of the 3.1 AVD, so made it 160. After that, the 4.0 AVD started about as fast as the 3.1 AVD (several minutes).
  I also reduced the 'Device ram size' setting from 512 to 256, but don't think that was the one that fixed it. See also here.
  


• The monkeyrunner recorder script records really slow on a 160 density Android 3.1 AVD. Playback is a lot faster. Here's a bunch of instructions, including 2 scripts for recording/playback which you can also find here and here.
  


• Had a problem trying to show the progress indicator in a ListFragment with the correct top margin. Tried adding addHeaderView() on the fragment to show some text above a fragment list. That works fine, except: when showing the progress indicator, it does not take into account any (top margin) LayoutParams being set! The app needs to set that to stay below the Action Bar which is in "overlay" mode. As soon as the adapter is set, the margin is taken into account correctly. I found the following here: in onCreateView() there is also a similar problem, they set the layoutparams explicitly again:
  
  

  // For some reason, if we omit this, NoSaveStateFrameLayout thinks we are // FILL_PARENT / WRAP_CONTENT, making the progress bar stick to the top of the activity. ... code where LayoutParams are set ...

  Tried several combinations of setting the LayoutParams, but those didn't work either. As a workaround now I have added a FrameLayout above the <fragment> in the main Activity xml. It is a <fragment> with a FrameLayout with a LinearLayout with some TextViews in it. For that one the top margin is set, and since the ListFragment is below it, it is pushed down correctly, even when the progress indicator is shown. Maybe the FrameLayout is causing the problems? Maybe a custom list fragment element would fix it.
  


• How to open a PDF from an app's local private storage:
  
  

  // Assuming you have stored a file like this (note the mode): FileOutputStream f = context.openFileOutput("theFilenameYouStoredThePdfAs", Context.MODE_WORLD_READABLE); ... write bytes to f, do other stuff... // Then you can get a reference for it for an external app via: File file = activity.getFileStreamPath("theFilenameYouStoredThePdfAs"); if (file.exists()) { Toast.makeText(activity, activity.getString(R.string.openingFile), Toast.LENGTH_SHORT).show(); Uri path = Uri.fromFile(file); Intent intent = new Intent(Intent.ACTION_VIEW); intent.setDataAndType(path, "application/pdf"); intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP); activity.startActivity(intent); }

  
  
• Definitely take a look at the latest Google I/O app, that's always a good reference for figuring out how to do specific things; I consider it a best-practices app (I've also seen it being referred to in blog posts/presentations from the Android team, can't find them now :).
  

Android invasion of the Mini Collectible - Series 02!

They are 16 in total, but sort of left out the doubles: Bernard, Hexcode, Cupcake, GD-927, Iceberg, Greeneon, ???, Blackbeard, Racer, Bluebot, Hexcode, Rupture, Noogler.