SQL Developer Dbms Output Pane gone…

I already managed to remove the Dbms Output pane a couple of times from my SQL Developer, probably by doing a keyboard combination by accident.
I am not sure how I did this and I can’t really reproduce this but sometimes I was bugged with this problem.

Problem is that I can’t get this pane back by following the ‘normal’ procedure (going to Menu-> View->Dbms-Output).
Previously I did not found a proper solution for this and I just had to reinstall the sqldeveloper all over again.
Untill now, I found a really easy way to get this pane back.

First of all exit all your sqldevelopers sessions.
Go to the directory where the sqldeveloper is installed (the numbering of the directories could be different from one version to another, but it will look similar to this)
cd /home/vallafr/.sqldeveloper/system3.1.07.42/o.ide.11.1.1.4.37.59.48

there remove the windowinglayout.xml file
rm windowinglayout.xml

This file contains your ‘custom’ sqldeveloper layout. By removing this the sqldeveloper layout will be reset to the original settings.

Then restart your SqlDeveloper
in sqldeveloper go to Menu -> View -> Dbms_Output
And tadaaa  there it is again

Read from anydata column

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more

Search database objects with a variable in oracle

Something I really often use is this select:

SELECT distinct type,line,name,text
FROM user_source
WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%')
UNION
SELECT distinct ‘column’ type,null line,table_name||’.'|| COLUMN_NAME name,null
FROM user_tab_columns
WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%')
UNION
SELECT  DISTINCT ‘table’ type,null line,object_name name,null
from user_objects
WHERE object_type IN  (‘TABLE’,'VIEW’)
AND lower(object_name) LIKE(‘%’||lower(:search_source)||’%')
ORDER BY type,name,line

This select will show you most of the database objects in a schema(columns, table,view,code), where this ‘search_source’ string is used.

You can add this into you sqldeveloper as well, as a user defined report.
To accomplish this you will have to do the following steps:
1) Open sqldeveloper
2) go to Reports tab(If you don’t see this tab go to the Menu choose view->Reports
3) right-click ’User Defined Reports’ -> add Report -> give name and put the sql code as above into SQL input screen(you can leave the rest as default)
You can use this Search in sqldeveloper now.

XMLTYPE column larger THAN 4000 bytes => ORA-19011

When you try to convert an xml file, which has a tag that contains for example one or more pdf files, into an XMLTYPE object, you will get the following error:
ORA-19011: CHARACTER string BUFFER too small

This error will be thrown because the content of a tag of an XMLTYPE is limited to 4000 bytes(this problem should be solved in 11g).
I have developed a solution/workaround for this.

How to get a view on packages larger than 30K in Oracle Portal

For a project with Oracle Portal, I had to do some lookups in packages and procedures on the database to get some information about the project.   Since I wasn’t working full-time for this project I had no access to the DB, except via the portal itself.

Several times I had the problem that the client wanted a quick answer for his question, but therefor I needed to dig into the code that was developed in the past. The problem was that the size for editing/viewing a package or procedure was limited to 30K and since there where several packages bigger than 30K, I couldn’t give them a quick answer.

If you only want to read the packages(not edit), there is a very easy portal solution for this problem:

extract() function gives a ‘problem’ whith spaces and lines in XML

I was developing a dequeue function for an XML file and I wanted to check if a certain returned value from the xml file was correct.
To retrieve the value of a tag I used this=>
v_text := v_record.extract(‘/record/Fruit/text()’,'xmlns=”http://www.example.org”‘).getStringVal();

Ones I captured this record I wanted to check if the content of this tag was equal to, for example, some fruit, let’s say ‘Lemon’.
In most of the cases I received the correct result, but there were some xml files which didn’t gave me the correct result. Nevertheless I was pretty sure that the value in my xml file was ‘Lemon’.
So what happened? I did some checks and then I found out that the syntax of this specific xml file was something like this:
<Fruit>Lemon
</Fruit>

When extracting the content out of the Fruit tag he also took the spaces, tabs and lines with it, and that was the reason why he thought that this wasn’t a ‘Lemon’

Conclusion, always use trim for the spaces and put some replace clauses for lines and tabs round the value. So you should use something like this:
v_text := replace(replace(replace(trim(v_record.extract(‘/record/Fruit/text()’,'xmlns=”http://www.example.org”&#8216;).getStringVal()),chr(10),”),chr(13),”),chr(9),”);

How to find the Oracle Application Server version number

I remember that I was searching a few years ago for a way to find the exact version of the Oracle Application Server, but I didn’t found it.

Today I had to search for this again and now I found a file that contains the exact version and even will be updated when the iAS is patched.

The file to look for is ias.properties, in this file you have to look for the line that begins with Version, next to this you will find the correct number.

You can find this file in iAS-home\config

How to enlarge the disk size of a VM drive?

For a client of ours we had to create a test in VMWARE but at a certain moment we noticed that the VM was running out of space, so we had to try to enlarge the disk size of the VM.

It is rather simple if you know it. In fact it is just an .exe file that you will have to run.

The steps:
Look in your VM for the VM that you want to enlarge.
Select it and double click on the disk that you want to enlarge.

Now you get the properties of this disk, remember the disk file and go to your explorer window and go to the directory of this VM, just to make sure that this file really exists.

If you are working on windows open a dos cmd(Start -> Run)
For the solution read more…

Now go to the VMWARE program directory in windows this is something like C:\Program Files\VMware\VMware Workstation
In this directory you will have to start the script that will enlarge the disk space of your VM-drive.

The command will look like: vmware-vdiskmanager.exe -x 19GB “F:\VMWare\Webcenter\test.vmdk” where 19GB will be the new size of your VM-drive.
You will see an indicator with the percentage of growth.

With this exe file it is also possible to shrink, create, defragment, rename, … VM-disks