Oracle list based queries ORA-00907

This should be helpful in case you get an error missing right parenthesis in oracle when using a list based query.

Needless to say... first check the parenthesis.

Next check if you are using sorting in the subquery.

E.g:

The following will not work:

select thedate,bsc,cell,ccalls, cmsestab
from
oss_cell_avg
where cell in
(
select cell from
oss_cell_avg
where
oss_cell_avg.ccalls > 0
and oss_cell_avg.cmsestab = 0
and oss_cell_avg.thedate >= (sysdate -2)
and oss_cell_avg.cell not like 'EC%'
order by cell asc
)

order by thedate desc,cmsestab asc


But the following will :


select thedate,bsc,cell,ccalls, cmsestab
from
oss_cell_avg
where cell in
(
select cell from
oss_cell_avg
where
oss_cell_avg.ccalls > 0
and oss_cell_avg.cmsestab = 0
and oss_cell_avg.thedate >= (sysdate -2)
and oss_cell_avg.cell not like 'EC%'

)

order by thedate desc,cmsestab asc

Enjoy!

BIRT Report Scheduler

I am considering writing a BIRT report scheduler for a recent project.

I already have a working system in place using what I learnt from the following:
http://digiassn.blogspot.com/2005/10/birt-report-server-pt-2.html

It works pretty neat. I did need to make a couple of modifications to make it work on solaris (needless to say). First wget is not in system path therefore I needed to do the following :

WGET="/usr/sfw/bin/wget"

And later on:

$WGET "$RUNURL=RegionDailyPeak.rptdesign&__format=doc&Region=Central" -O "$EXPORTDIR/CentralDailyPeak.doc" -q

I also hid the server birt viewer url into a variable as :
RUNURL="http:///ReportXViewer/run?__report"

and EXPORTDIR as the path where I want the download.

Also I wrote some code to send emails as well and used that as a script. Additionally I needed to used the zip command to zip large reports before mailing them :

e.g.:
zip $EXPORTDIR/TCHGPRSTraffic.zip $EXPORTDIR/TCHGPRSTraffic.xls

and then mailed the zip file. Its working fine . Any parameters needing any form of dynamic nature were put into the report using a method I posted previously.


I am thinking of encapsulating all these cool things into a web server application using flex?

Anyone interested in such an application?

The following is a list of the features:

• A simple admin login screen

It is intended to be a single user application. One admin password

• Server Settings

Lists:

1. adminusername / adminpassword.
2. BIRT report viewer url (the run url... to which only report and further parameters will be passed as shown above)
   
3. The report folder url.
4. Notification email address
   
5. mailer settings:

servername, Username, password, from , port, ssl

• A simple report schedules list screen.:
  

This will have a list of schedules setup into a data grid with two columns. Crontab scheduling string, and a schedule name. Click on any one and select edit (other options: delete, new, duplicate)

• Options for scheduling:

1. crontab string,
2. Select report from a combo box
   
3. outputfolder
4. zip or not to zip
5. notify of running by email
   
6. format : valid formats : pdf, doc, ppt, xls..... but not html. A simple downloader that will download a single url will be used. (KISS)
7. mail to. (comma seperated destination addresses)
   
8. append a date string to ( low priority )
9. A parameters table. Which lists parameter vs. value. Nothing dynamic about it. You need to know the paramter names yourself to be able to use it. (low priority .... make parameters list available once you select the report)

• A reports management screen:

Lists the .rptdesign files in the reports directory. With options of upload, delete, download, rename, copy

Seems simple enough :) But requires dedicated time.... which I am short on right now.... I need motivation... anyone interested in giving me some?

Optimizing Birt Viewer for solaris in simple steps

I was working on BIRT in windows. The performance in windows was awesome but when I moved the code to our solaris server a report that would take round about 10 seconds on windows took over a minute!

Here is how I improved the performance: (Note that I prefer jetty to tomcat due to its lightweight nature ..... Actually I use hightide ... but that's another story)

• Make the default memory allocation greater:
  

open your tide.sh in the bin directory (hightide.sh in my case). Find and modify the following:

# JAVA_OPTIONS
# Extra options to pass to the JVM

TO:

JAVA_OPTIONS="-server -Xmx3800 -Xms3800"

this basically tells the JVM to run in server mode i.e. -server (slower startup time.... faster execution time :)) and to start of with a default of 4GB ram with a max of 4GB ram (Xmx and Xms) . Change this to how much RAM you want allocated to JVM by default

• Update your BIRT runtime to the latest and greatest version

I am telling you.... don't take this as a joke! :)

• Update your JDK to the latest version

Again ... no kidding. Solaris 10 comes with JDK 5 by default. JDK 6 is so much better. Just download the .sh file for jdk from the sun website and install it. It is a simple command line script. You do not even need root privledges to run it. All you need is access to a folder you can write to (your home dir ?) and thats it.

After installation it note the location of your installation dir
e.g. /home/bla/Desktop/1.6.13_10
or whatever.

again modify jetty.sh (or hightide.sh) changing:

# JAVA_HOME
# Home of Java installation.

to :

JAVA_HOME="/home/bla/Desktop/1.6.13_10"

Thats all. Now give BIRT a spin. My runtime went from 10 secs to 7 secs :)!!

BIRT dynamic parameters

BIRT is simply amazing. There is no denying that. The best thing about it is that it is open source and free to use. This makes it simply an amazing solution to work with. It is really great as long as they keep the normal report rendering api free and open source :)

This post is about a particular scenario in reporting. It relates to time reporting. Most commonly we need trend analysis of the past week to better analyze the data. But you dont want to put in the date / time of the last week manaually. Using oracle BI Publisher I am used to taking care of these using sysdate inside of SQL Queries. But BIRT provides a better way.

Heres a step by step howto.

Create two report parameters. One for start and one for the stop time:

Remeber to uncheck the Is Required property:

This is because we are going to generate the value dynamically in case the parameters are not set :)

Needless to say you need to link these to your query (data set) parameters:


Now click the main surface of your report in the layout tab:


Then click the script tab and add the following code to the initialze event handler as shown:


function getdateBehindByDays(days)
{
var cal = java.util.Calendar.getInstance();
cal.add(java.util.Calendar.DAY_OF_MONTH, -days);
return (cal.get(java.util.Calendar.MONTH) + 1 ) + "/" + cal.get(java.util.Calendar.DAY_OF_MONTH) + "/" + cal.get(java.util.Calendar.YEAR);

}

if (params["StartTime"].value == null)
{
params["StartTime"].value = getdateBehindByDays(7);
}

if (params["StopTime"].value==null)
{
params["StopTime"].value = getdateBehindByDays(0);
}

What this code is doing is that it is sending the start date back by one week and the stop time to today :). Just change the values '7' and '0' to what you require :)

Now just run the report and set the parameters you want to be made dynamic to null:

Viola! welcome to the wonderful world of automated reporting :)

Solaris Crontab exporting an environment variable

Ran into a problem when exporting an environment variable in solaris.

If your bash script has the following code:
export JETTY_HOME=/opt/os/home/efaitan/Desktop/hightide

You would get the following error:
JETTY_HOME=/opt/os/home/efaitan/Desktop/hightide: is not an identifier

This is because cron is run by bourne shell and for that this is not a valid syntax. Bascially you are telling it to export a variable identified as "JETTY_HOME=/opt/os/home/efaitan/Desktop/hightide:" where all you wanted was to export the variable JETTY_HOME.

Resolution: Use two lines instead of one. Modify your script to :
JETTY_HOME=/opt/os/home/efaitan/Desktop/hightide
export JETTY_HOME

Further detail:
The default shell opened for my solaris system was KSH. You can determine the default shell using the $SHELL environment variable. The above export and set in one line in valid for ksh but not valid for bourne shell (normal sh).

It is suggested that you test your scripts in sh before adding them in crontab entries.
Here is a screen shot explaining the root cause of the error:


Enjoy!

Oracle Create a table using a query

Here is a neat little SQL trick. First a little bit of background since its informative (scroll down if you dont want it)

Joins in LARGE tables are always expensive. Whenever possible they should be catered for in your ETL.

Here's what happened. Ericsson OSS has a table with time and another table with cell stats (many other tables in fact :) ). First I decided to copy the data as it is. However later on I wanted daily averages of counters. The plan was to use a simple view over these two tables for the stats. And then another view which averages this over every day.

The query performance = 54 secs

This is pretty fast considering its all cells for all hours of all days.

But then I read about Oracle function based indices (FBI)! Amazing stuff. I could add the day as an index to the table (using trunc(time,'DD')) . Cool. That should really boast the performance.
However VIEWs don't support indices. (Only materialized views do ... but a materialized view would consume too much space and be an overkill .. just to add indices). So I needed to convert the two tables into a single table.

Using function based index which truncates date I get a query performance = 21 secs.



SQL

So I decided to move the data from those two tables to the new table.

create table yourtable as select * from whatever

This will create the table from the results of your query. Quick and neat way to move data. Love SQL.... Love Oracle!

Oracle Materialized View Refresh using JDBC

I am a big fan of Materialized views. When used effectively it somewhat decreases the requirement to use Oracle Analysis (if all you want is queries and not pivots).

You can refresh a materialized view using the following SQL Script :

execute DBMS_SNAPSHOT.REFRESH('VIEWNAME','c');

Where VIEWNAME is the view you want to refresh.

For a detailed note one the parameters take a look at :

http://www.databasejournal.com/features/oracle/article.php/2200191/Manually-Refreshing-Materialized-Views-and-Creating-Refresh-Groups-in-Oracle.htm

However this is not a valid sql statement.

Using Oracle SQL developer you can execute it using the "Run script" button as shown:

However using the Execute script button with give you an error (Invalid SQL Statement):
The same error is observed if you were to do the following with jdbc:
stmt = connection.createStatement();
stmt.executeQuery("execute DBMS_SNAPSHOT.REFRESH('VIEWNAME','c')") ;

This is because statements cannot contain calls to stored procedures :). Don't know why, but that's the way it is.

Instead you should do the following:
stmt = connection.prepareCall("call DBMS_SNAPSHOT.REFRESH('VIEWNAME','c')");
stmt.execute();



For sql developer you can just do :
call DBMS_SNAPSHOT.REFRESH('VIEWNAME','c');


It should work like a charm :)

Enjoy!

getOutputStream() Error in Oracle BI Publisher

I encountered the following error while running oracle BI Publisher under tomcat:

getOutputStream() has already been called for this response



This error occurs when you have a user with roles assigned other than administrator and that user tries to access the shared folder.

Anyways got around to fixing it so I thought I'd mention it just in case anyone else encouters the same issue. I guess it is due to a bug in the security error redirector code.

Resolution: You need to share at least one folder to the user.

Stepwise guide :) :
Create new folder in the shared folders directory.
name it whatever you like.

Now go to admin interface and goto Roles and permissions. Create a new role.

Add datasources and any of the preconfigured roles to the newly created role.


Click on Add Folders and add the newly created folder:



Next assign the role to your user :). All set !

Enjoy :)

Crontab Basics on Solaris

Crontab is extremely useful and well documented all over the internet. Here i show how to make your experience with it a bit more pleasant.

First some key useful information.

• Looking at current crons:

crontab -l

• Editing crons:

crontab -e

• Changing the crontab Editor

export EDITOR="gedit"

I prefer gedit to vim or pico :)

• Crontab format

1. minute (from 0 to 59)
2. hour (from 0 to 23)
3. day of month (from 1 to 31)
4. month (from 1 to 12)
5. day of week (from 0 to 6) (0=Sunday)

e.g:
* * * * * /bin/something.sh

means run something.sh every minute of every hour of every day of every month on all weekdays :)

PERMANENTLY changing your crontab editor preference:
Login using CDE. Open your home folder:

Right click in the open browser and select "Show Hidden Objects" :


Find .profile and add the following line as shown:
export EDITOR="gedit"


Leave anything else intact (unless you want to change something)

Now when you start editor using crontab -e you should get gedit

Enjoy!

Splitting a string into an array using Oracle

Took this from :

http://www.orafaq.com/forum/t/11692/0/

Suppose you want to say :

select * from oss_cell_view where cell in (:cells)

Running this query in sql developer you get a dialog as:

But here is what sql developer does. Seeing that it is a string input it turn the query into:

select * from oss_cell_view where cell in ('asdf,fdsa')

Whereas what you expect is :

select * from oss_cell_view where cell in ('asdf','fdsa')

(notice the ',' in the middle :) )

Here is the solution:

Create a simple table type:

create or replace type myTableType as table of varchar2(255);


Create a string splitting function (default delimiter is , ):

create or replace function str2tbl
(p_str in varchar2,
p_delim in varchar2 default ',')
return myTableType
as
l_str long default p_str || p_delim;
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, p_delim );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+length(p_delim) );
end loop;
return l_data;
end;




You can test the newly created function:

SELECT column_value

FROM TABLE (CAST (str2tbl ('10,01,03,04,234') AS mytabletype))

Finally you can rewrite your query as :
select * from oss_cell_view where cell in (SELECT column_value FROM TABLE (CAST(str2tbl (:cells) AS mytabletype)))


Enjoy!

Displaying report parameters in Word using BI Publisher

There are many cases where you want to display the report parameters in the output. This is especially useful for scheduled reports. Finding no documentation on the subject online here is a step wise howto.

I explain how to do it in oracle. Your SQL Query will vary depeding on your database server e.g oracle uses ':' for named parameters, TopN queries are done using where rownum <>



Things to note:

• put all your parameters in the query e.g. above you see I put BSC (:BSC) and DaysBack (:DaysBack) into the query
• select from a table that has at least one entry :) otherwise no data will be returned from the query. Basically we are selecting the first row but putting are parameters as a column and not showing any of the the original tables columns.
  

Update: Oracle has a special table dual purely designed for this purpose :)
you should do:
select :BSC from dual

• Parameters are case sensitive. Therefore it should be :BSC and not :bsc as shown in the screenshot.
  

Save. Next startup your bi publisher template builder and the parameters should become available in fields as shown:

Enjoy!

Flex BIRT Report Viewer

Just though I should share this before I back it up on my harddisk (since I am not using BIRT anymore).

This is a simple BIRT report viewer (basically hides BIRT under your customized view).

It uses the famous IFrame code to show a simple web page (retrieved from the BIRT runtime) under a flex application.

It also determines where you are viewing the report (on my pc or on the server) and points to the appropriate location.

Here is a screen shot for you enjoyment. While it runs:

After completion:


All you need to do is change the following in the code :
ReportViewerUrl = "http://localhost:8400/ReportXViewer/"
to point to your server.
Then after deployment you can simply call appending ?__report=yourreport.rptdesign as you normally would with the servlet based report viewer.

Enjoy.
Of course you should change the logo to your own ;)
http://code.google.com/p/birtreportviewer/downloads/list

Major JAVA reporting toolkits review

Been really busy at the office lately (evaluating pentaho, Birt, Jasper and now Oracle XML Publisher Standalone).

All of these are great and I have a deep appreciation of the efforts made by open source. Just so you know :

• Jasper : Best open source solution.
  

Advantages:
Of all these evaluations this was the BEST performing (render speed)
I do not have much need for analysis (since I prefer view / materialized views) and therefore this was ultimate.
Great formating error detection.
Scheduling / email works.
Great (best) Parameter / programmed parameter support.
Clean code ( in case you were wondering about what is under the hood )

My concerns :
No DEFINED table structure. You need to put fields into the details region to make a table. Alignment etc can become an issue.
Comes pre configured with a mysql instance. Moving to a different database is an involved process.

• Pentaho: Really nice and has gathered the most momentum in open source for now.
  

Advantages:
Analysis. Like I mentioned I do not have much need for this.
Scheduling.

Disadvantages:
The report designers does not (as of version 2. It is planned soon though) have support to preview report with parameters. As far as I know, I see no use of a report without parameters. You need to upload the report to the server to preview such a report.
Update: They have a design studio where it is possible to preview the report in an integrated way (without opening the browser) as well as define action flows .... cool
Query designer kept giving errors (but that didn't bother me since I use SQL Developer anyways). The jasper query designer was much better. It uses netbeans data sources which are highly stable.

• BIRT

Advantages:
By far the most comprehensive report designer. Simply the best!


Disadvantages:
Not the best programmed parameter support. Although much more powerful than jasper (due to its use of java script) but takes a bit of hacking. It does work though ;). Also you need to put parameters as ? in the SQL query and cannot name them till afterwords. (Jasper had a better concept $P ;))
No free scheduling support.
Speed was the major concern. I was close to writing my own scheduling framework when preliminary testing by users was not satisfactory.

• XML Publisher

Since I have finally decided to move to it completely I will mention the disadvantages first:

Disadvantages:
Report design experience: Although they have the best idea (use word!) it keeps hanging from time to time.
Online query designer: I prefer an offline version where queries can be moved from place to place (you can do this by coping the xdo file from the server. But still)
No mouse over chart support like you get with BIRT. Like I said for small number of users with small number of reports. BIRT IS THE BEST.

Advantages:
BEST FORMATING OPTIONS (its word... enough said)
Awesome scheduling. You can send to printer/ fax/ ftp / email and more :). You can also check history etc. Nice admin interface. You can also install the scheduling repository from the web interface (unlike jasper where you need to manually run sql scripts)

Scheduling seems vital to the project. Along with oracle, easy cross platform, and easy template based support. XML Publisher it is :). Not to mention that once I have my oracle backup procedures in place I need to worry a lot less. :). Also the export to excel by XMLP was the best.

PS: I deployed the XMLP war to tomcat (blazeds). It works.