sql connect by level

Sometimes it's useful to generate in SQL a series of data responding to a certain criteria.

Like "the integers between 0 and N", or "the dates between today and 5 days ago"

You can do it with "connect by level":

SELECT level from DUAL connect by level <= 10 ;

SELECT TRUNC((sysdate-5 + (LEVEL))) AS DATES FROM DUAL connect by level <= ( sysdate-(sysdate-5) ) order by level;

How to return a default value if lookup fails on a table

given this table:

  CREATE TABLE "ACME_ERRORCODES" 
   ( "ERRORCODE" VARCHAR2(100 BYTE), 
 "ISRETRIABLE" NUMBER, 
 "ISCUSTOM" NUMBER
   ) ;

if you want to return a default value 0 for ISRETRIABLE in case the ERRORCODE is missing in the table, you can do:

select NVL ( 
   (select ISRETRIABLE from ACME_ERRORCODES where errorcode = 'SOME_MISSING_ERROR_CODE' ), 
    '0') 
from dual;

SQL to display SQL history

This to display all SQL statements processed in Oracle DB withing a certain range of time and coming from certain machines:

select a.sql_id,a.sql_text,b.cnt from dba_hist_sqltext a, 
(
select sql_id,count(1) cnt from 
(
select session_id,sql_id,machine,sample_time,count(1) from 
(
select * from dba_hist_active_sess_history
where 
sample_time between to_date('19/06/2013 07:00','dd/mm/yyyy hh24:mi') and to_date('19/06/2013 10:00','dd/mm/yyyy hh24:mi')
and program = 'JDBC Thin Client' 
and machine like 'myhost1%'
--and rownum < 10
)
where sql_id is not null
group by session_id,sql_id,machine,sample_time
order by sample_time asc
) group by sql_id) b
where a.sql_id(+)=b.sql_id

if you simply want the active sessions:

select * from dba_hist_active_sess_history
where 
sample_time between to_date('19/06/2013 07:00','dd/mm/yyyy hh24:mi') and to_date('19/06/2013 10:00','dd/mm/yyyy hh24:mi')
and program = 'JDBC Thin Client' 
and machine like 'myhost1%'

"Javadoc" for ORACLE DB

Documentation should be embedded in code. Everything else is way, way suboptimal.

You can easily document your DB like this:
This will display all tables
SELECT table_name FROM user_tables;
This will display all Comments on all Tables, Views, Columns:
SELECT TABLE_NAME, table_type, comments from user_tab_comments order by table_name;

To make it only for TABLEs:
SELECT TABLE_NAME, table_type, comments from user_tab_comments where table_name = 'TABLE' order by table_name;

Oracle DB: alter table add column after

Terrible news, in Oracle DB you cannot add a column in a specific position, it will always be added at the end.

Why would that matter? Because if you use this statement to copy data from one table to the other:

insert into ONE select * from TWO

you rely on ONE and TWO having the same position for each corresponding column.

Here some tips on how to work around (they all entail creating a new table, which sucks):
http://www.orafaq.com/faq/how_does_one_add_a_column_to_the_middle_of_a_table


The safest way is to add at the bottom and in order to copy use:

insert into ONE (COL1, COL2) select COL2, COL1 from TWO; 

and avoid the more generic statement

insert into ONE select * from TWO 

Extracting a single resultset row from sql:query resultset

I know, JSTL SQL breaks the MVC pattern. Who cares. It's quick and dirty and it works.
I simply hate complicated setups of MVC products, gimme the result now with minimal effort and I will be grateful to you.

So, all the examples are about retrieving a ResultSet with a sql:query:

<sql:query var="rsInterfaceNames" dataSource="jdbc/soainfra_dev2">
select unique InterfaceID from WLI_QS_REPORT_VIEW order by InterfaceID DESC
</sql:query>


and then do a

<c:forEach var="rowIF" items="${rsInterfaceNames.rows}">
do something with ${rowIF.someAttribute}
</c:forEach>


This simply doesn't apply when you retrieve a ResultSet with a single row, suck as in SELECT COUNT(*) FROM SOMETABLE;

Here is the javadoc of the object returned into the resultset var:

http://tomcat.apache.org/taglibs/standard/apidocs/org/apache/taglibs/standard/tag/common/sql/ResultImpl.html

and here is how you can get the value of count(*):

${rsInterfaceCount.getRowsByIndex()[0][0]}


in case you have trouble with conversions, just use select TO_CHAR(count(*)) ...

WebLogic, how to run SQL commands without SQLPlus

few people know this :o) :

cd $DOMAIN_HOME
. ./bin/setDomainEnv.sh

java utils.Schema
Usage: java utils.Schema <url> <driver> [options] <SQL file>

where:
<url> JDBC driver URL.
<driver> JDBC driver class pathname.
<SQL file> Text file with SQL statements.

where options include:
-u <user> User name to be passed to database.
-p <password> User password to be passed to database.
-verbose Print SQL statements and database messages.


utils.Schema is a class in weblogic.jar

REGEX and string manipulation with SQL

REGEXP_SUBSTR : search a string for a regular expression pattern


REGEXP_REPLACE the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string

REPLACE REPLACE returns char with every occurrence of search_string replaced with replacement_string

select count from multiple table

Select
(Select Count(*) From table1),
(Select Count(*) From table2)
from dual;


it works!