Oracle SQL – BLOB to XML Type ( ORA-06502 PL/SQL : numeric or value error : raw variable length too long )

I am working with a database schema where xml content is being stored in a BLOB and I’m working on querying that XML.
When you cast the BLOB to VARCHAR, you’ll run into the limit of 2000 characters with the following error:

ORA-06502 PL/SQL : numeric or value error : raw variable length too long

So this trick came in handy:

SELECT XMLTYPE( BLOB_FIELD, 871 ) AS AWESOMEXML FROM YOUR_TABLE;

Please be very aware of the character set that you are using, you can enter a world of hurt when you’re using the wrong one. The number 871 is the character set UTF8, which we are using.
For other character sets, check http://www.mydul.net/charsets.html

SSH through a proxy to a remote server

I wanted to SSH into my home server from my workplace but I couldn’t reach it directly because of the way the network was set up.
As it turns out it is quite easy to do by using the corkscrew program.
Edit ~/.ssh/config and add the following lines:

Host home joris.his.homeserver.com
    Hostname joris.his.homeserver.com
    User joris
    ProxyCommand corkscrew proxyserveraddress proxyserverport %h %p

The most important part is the ProxyCommand, this lets your ssh client know that it should use corkscrew as a proxy to your host. %h means the host of your remote server, %p means the port of your remote server.

Authentication – Subversion on command line will not remember credentials

I ran into this issue today on my command line SVN client.
Every time I ran the SVN command against my repository, it asks for my password. It does remember the username but doesn’t store the password.
There are a couple of settings to check in two different files:

  • .subversion/config
  • .subversion/servers

.subversion/config

The config file contains a setting which sets the password store you will be using. We need to disable all password stores and use an empty list, which is done by uncommenting (or adding) the next line

password-stores =

.subversion/servers

The servers file contains settings which allow you to save your passwords in general and to save the passwords in plaintext (please be careful when choosing this option!)
This file is divided in sections which are set with the [] brackets. In the [global] group you should uncomment (or add) the following lines:

store-passwords = yes
store-plaintext-passwords = yes

Run UltraVNC as user, prevent "No Password"

I wanted to run UltraVNC on my laptop to share the screen towards my desktop computer. I have no administrator rights on this pc but I can run the standalone UltraVNC server fine.
To run it without admin rights, download the zip file from the latest version from here, choose the “bin zip” downloads for your OS at the bottom of the page.
When you run winvnc.exe, the following message presented itself in a pop-up:

No password has been set & this machine has been preconfigured to prevent users from setting their own.
You must contact a System Administrator to configure WinVNC properly.

WinVNC No Password popup
Screenshot – WinVNC No Password popup

This means you need to set the password first, but you need to run WinVNC to be able to set the password (kind of a Catch 22 there)
Add the contents below in a new file called ultravnc.ini in the same folder as winvnc.exe and your password is set to “nopassword” (without the quotes). You can now startup WinVNC by doubleclicking winvnc.exe.

[Permissions]
[admin]
FileTransferEnabled=1
FTUserImpersonation=1
BlankMonitorEnabled=1
BlankInputsOnly=0
CaptureAlphaBlending=0
BlackAlphaBlending=0
DefaultScale=1
UseDSMPlugin=0
DSMPlugin=
DSMPluginConfig=
primary=1
secondary=0
SocketConnect=1
HTTPConnect=1
XDMCPConnect=0
AutoPortSelect=0
InputsEnabled=1
LocalInputsDisabled=0
IdleTimeout=0
EnableJapInput=0
QuerySetting=2
QueryTimeout=10
QueryAccept=0
LockSetting=0
RemoveWallpaper=1
RemoveEffects=0
RemoveFontSmoothing=0
RemoveAero=1
DebugMode=0
Avilog=0
path=C:\development\programs\UltraVNC
DebugLevel=0
AllowLoopback=0
LoopbackOnly=0
AllowShutdown=1
AllowProperties=1
AllowEditClients=1
FileTransferTimeout=30
KeepAliveInterval=5
SocketKeepAliveTimeout=10000
DisableTrayIcon=0
MSLogonRequired=0
NewMSLogon=0
ConnectPriority=0
PortNumber=5900
HTTPPortNumber=5800
[ultravnc]
; both passwords are "nopassword"
passwd=33117E54AA0D4D3B55
passwd2=33117E54AA0D4D3B55

Important: Do change your password in the settings when you can run WinVNC!

Screenshot - WinVNC Administrator Settings
Screenshot – WinVNC Administrator Settings

Format any XML (windows)

With this blog post, it’s possible to format any xml you’ve selected.
It will take about 5 minutes to get this working, a couple of manual steps are necessary.
These are the tools being used:

Steps to get it working: (All steps are necessary!)

  1. First install AutoHotKey_L and start it, you’ll see the logo if it’s running: AutoHotKey logo
  2. Create a folder on your Hard Drive called “C:\autohotkey”
  3. Create a file “tidycfg.ini” in the location “C:\autohotkey\tidycfg.ini” with the following contents:
    1. [Clean Indent XML]
      input-xml: yes
      bare:no
      clean:no
      fix-bad-comments:no
      fix-backslash:no
      indent:yes
      indent-attributes:no
      indent-spaces:4
      char-encoding:raw
      replace-color:no
      wrap:0
      wrap-asp:no
      wrap-jste:no
      wrap-php:no
      write-back:yes
  4. Place tidy.exe in the location C:\autohotkey
  5. Create a file called “autohotkey.ahk” in C:\autohotkey with the following contents:
    1. #x::
      sleep 50
      Send ^c
      ClipWait
      clipboard = %clipboard%
      FileDelete, C:\autohotkey\format_with_tidy.xml
      FileAppend, %clipboard%, C:\autohotkey\format_with_tidy.xml
      sleep 50
      RunWait, %comspec% /c C:\autohotkey\tidy.exe -config C:\autohotkey\tidycfg.ini C:\autohotkey\format_with_tidy.xml
      clipboard =
      FileRead, clipboard, C:\autohotkey\format_with_tidy.xml
      Return
  6. Doubleclick the file “autohotkey.ahk” in C:\autohotkey
  7. Select unformatted xml and press [ WINKEY+X ]
  8. The formatted xml is now in your clipboard and can be pasted anywhere.

Extra information:

These are the steps what is happening in the autohotkey file:

  1. Configure the keyboard shortcut (# = winkey, x = x-key)
  2. Wait 50 milliseconds
  3. Send the ctrl-c with keyboard combination ^c ( ^ = ctrl, c = c-key)
  4. Wait until the clipboard is filled
  5. remove all formatting from the clipboard
  6. Delete the temporary file (if present)
  7. Paste the clipboard contents in the temporary file
  8. Wait 50 milliseconds
  9. Run tidy.exe on the temporary file with the configuration settings
  10. empty the clipboard
  11. Paste the contents of the temporary file in the clipboard
  12. End script

See which SVN user editted which line

This is one quite cool trick where you can see which edit’s were done to a file by which user:
Issue the following command on a file which is checked in to SVN:

svn blame filename

Which will output the following syntax:

revision <tab> username <tab> line in filecontents

Please look at this example :

21672 jvisscher   declare namespace urn = "namespace:customer:v01";
21672 jvisscher   declare namespace urn1 ="namespace:customer:v01";
21673 mycolleague declare namespace urn2 = "namespace:v01";
21673 mycolleague declare namespace urn3 = "namespace:v01";
21674 mycolleague declare namespace urn4 = "namespace:v01";

Awesome! Now I can quickly see that my colleague was the cause of my failing namespace!
As a sidenote: I really like the function’s name, because most of the time, you’re looking to blame someone 😉

Weblogic does not start, java.lang.NumberFormatException, EmbeddedLDAP

I encountered the following exception when trying to start our AdminServer today:

<May 30, 2013 1:04:27 PM CEST> <Critical> <WebLogicServer> <BEA-000386> <Server subsystem failed. Reason: java.lang.NumberFormatException: null
java.lang.NumberFormatException: null
 at java.lang.Integer.parseInt(Integer.java:454)
 at java.lang.Integer.parseInt(Integer.java:527)
 at weblogic.ldap.EmbeddedLDAP.validateVDEDirectories(EmbeddedLDAP.java:1104)
 at weblogic.ldap.EmbeddedLDAP.start(EmbeddedLDAP.java:242)
 at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
 Truncated. see log file for complete stacktrace
>
<May 30, 2013 1:04:27 PM CEST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FAILED>
<May 30, 2013 1:04:27 PM CEST> <Error> <WebLogicServer> <BEA-000383> <A critical service failed. The server will shut itself down>
<May 30, 2013 1:04:27 PM CEST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SHUTTING_DOWN>

This does not really explain what went wrong, but the solution is to remove the replicas.prop file and restart the AdminServer again.
The replicas.prop file is located at :
$DOMAIN/servers/AdminServer/data/ldap/conf/replicas.prop
Rename (move) replicas.prop to replicas.prop.bak and retry the startWeblogic. In my case this solved the not starting server.

How to try to drop a table without raising exception ORA-00942: table or view does not exist

With the piece of code below it’s quite easy to first make sure your table exists before dropping it, so you’re not running into SQL Error: ORA-00942: table or view does not exist
Script explanation: It will check if the table is present before trying to drop it.

-- Drop table
DECLARE
 l_count NUMBER;
BEGIN
 SELECT COUNT(1)
 INTO l_count
 FROM ALL_TABLES
 WHERE table_name = 'TABLE_NAME'
 AND owner = 'USERNAME';
 IF l_count > 0 THEN
 EXECUTE IMMEDIATE 'Drop table USERNAME.TABLE_NAME CASCADE CONSTRAINTS';
END IF;
END;
/

Supercharge your CLI bash history search

This is a repost from https://coderwall.com/p/oqtj8w but it’s so handy, I want to share anyway 🙂
 
Create ~/.inputrc and fill it with this:

"\e[A": history-search-backward
"\e[B": history-search-forward
set show-all-if-ambiguous on
set completion-ignore-case on

 

This allows you to search through your history using the up and down arrows … i.e. type “cd /” and press the up arrow and you’ll search through everything in your history that starts with “cd /”.

Pretty print XML on Linux Command Line BASH

It’s quite handy to indent XML when you need to read it with the human eye, but on a terminal it’s often not as easily readible.
Fortunatly there’s a command which’ll indent it so you can actually read it;
xmllint –format file.xml
This will show you how ugly it could be:
 

Screenshot Unformatted XML on terminal
Screenshot Unformatted XML on terminal

 
And behold the indented beauty of this little gem:
Screenshot indented XML on terminal
Screenshot indented XML on terminal