Monday, 25 August 2008

How to do a "desc emp" from JDBC

If you ever try to do a "desc emp" from a JDBC program , that won't work as that's a SQL*Plus command only, but here is how to write an SQL statement to do that which can easily be converted into a JDBC program and used.

Firstly here is what we get when we describe the EMP table in SQL*Plus.


SCOTT@linux10g> desc emp;
Name Null? Type
----------------------------------- -------- ------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Now here is the SQL we can use to get the same output from an SQL query.


select
column_name as "Name",
decode(NULLABLE, 'Y', '', 'N', 'Not Null') as "Null",
data_type||decode(data_type,
'NUMBER', decode(DATA_PRECISION, NULL, '',
'('||DATA_PRECISION||','||data_scale||')'),
'VARCHAR2', '('||data_length||')',
'CHAR', '('||data_length||')') as "Type"
from user_tab_columns
where table_name = 'EMP'


The output of that is as follows from the query above , identical to a "desc emp", or close enough, couple more decodes will sort out a few minors issues , but seems to work for most tables I tested it against.

Name Null Type
------------------------------ -------- ------------------------------
EMPNO Not Null NUMBER(4,0)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4,0)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2,0)

So with a bind variable defined it would simply be as follows


select
column_name as "Name",
decode(NULLABLE, 'Y', '', 'N', 'Not Null') as "Null",
data_type||decode(data_type,
'NUMBER', decode(DATA_PRECISION, NULL, '',
'('||DATA_PRECISION||','||data_scale||')'),
'VARCHAR2', '('||data_length||')',
'CHAR', '('||data_length||')') as "Type"
from user_tab_columns
where table_name = ?


And then easily be integrated into a JDBC program, I did this over the web using a JSP page, which lists all user tables and adds a DESCRIBE button next to the table name to get the output above when clicked.

Monday, 11 August 2008

Determining if any active HTTP Sessions exist for deployed applications in OAS 10.1.3.x

Recently I was asked how to determine if any current HTTP sessions exist for an application. The reason for needing this information was so that the application could be redeployed only when no HTTP sessions existed and the application was currently not being used. It turns out there is an MBean which can give you that information.

The Mbean is as follows:

oc4j:j2eeType=ClassLoading,name=singleton,J2EEServer=standalone

His how to access it from the MBean browser in ASC and get the information on HTTP sessions for your deployed applications.

1. Log into ASC
2. Click on the container you wish to use.
3. Click on the link "Administration".
4. Click on the "Go To Task" icon as follows.

JMX -> System MBean Browser

5. Expand the ClassLoading node in the navigation pane, then select the singleton MBean instance.
6. Click the Operations tab in the right-hand pane, then click the executeQuery operation.

Three versions of the executeQuery operation are exposed. Click the version that takes one parameter.

7. For the value parameter enter in the following and press the "Invoke Operation" button

HttpSessions(details)

You will end up with output as follows which will give you the amount of HTTP sessions which currently exist for each deployed application.

** Summary at Aug 11, 2008 9:57:43 AM **

Total Sessions: 0
Total Attributes: 0
Total Session Size: 0 bytes

Largest Session: N/A
Youngest Session: N/A
Idlest Session: N/A
Oldest Session: N/A

** Details **

[ADFBC-HA:adfhaprofile]

No active sessions

[SimpleWS:WebServices]

No active sessions

[WebServiceDemo-SimpleWS-WS:WebServices]

No active sessions

...