Hibernate Calling Oracle Stored procedure

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#propertyresults

I had a long struggle to figure out a way to get the output from Oracle stored procedures. Here is what I could find in the manuals.

16.2.2.1. Rules/limitations for using stored procedures

You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

The recommended call form is standard SQL92: { ? = call functionName() } or { ? = call procedureName(}. Native call syntax is not supported.

For Oracle the following rules apply:

· A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.

For Sybase or MS SQL server the following rules apply:

· The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.

· If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

Find last 12 months a MYSQL way

select monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)) , year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 2 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 2 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 3 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 3 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 4 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 4 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 5 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 5 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 6 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 6 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 7 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 7 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 8 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 8 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 9 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 9 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 10 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 10 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 11 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 11 MONTH)),
monthname(date_sub(CURRENT_TIMESTAMP(), INTERVAL 12 MONTH)),year(date_sub(CURRENT_TIMESTAMP(), INTERVAL 12 MONTH))

I was able to overcome the IE cache problem on AJAX with the following headers in PERL CGI.

my $q = CGI->new;
print $q->header( -head => $q->meta({ '-http_equiv'
=> "cache-control", '-content' => "no-cache" }),
-expires => 'now');

perl hash sort values example CGI script

In the following example what we are trying to do is to sort the Value part of the Hash and display it inside a popup menu sorted by the value rather than the key.



#!/usr/bin/perl
use CGI;

my $qry = CGI->new();

my %c = (
"1016"=>"W",
"1017"=>"Cl",
"1018"=>"Fu",
"1023"=>"Emerg",
"1024"=>"Responsibler",
"1025"=>"Director",
"1026"=>"Accountable"
);

$default = 1017;

my $html = $qry->popup_menu (

-name=>"XXX",
-values=>[sort { $c{$a} cmp $c{$b} } keys %c],
-labels=>\%c,
-override=>'true',
-default=> ( $default )

);

print $html;

Sample Ajax function call

function xmlhttpPost(strURL) {
   var xmlHttpReq = false;
   var self = this;
   // Mozilla/Safari
   if (window.XMLHttpRequest) {
       self.xmlHttpReq = new XMLHttpRequest();
   }
   // IE
   else if (window.ActiveXObject) {
       self.xmlHttpReq = new ActiveXObject("Microsoft.XMLHTTP");
   }
   self.xmlHttpReq.open('POST', strURL, true);
   self.xmlHttpReq.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
   self.xmlHttpReq.onreadystatechange = function() {
       if (self.xmlHttpReq.readyState == 4) {
           updatepage(self.xmlHttpReq.responseText);
       }
   }
   self.xmlHttpReq.send(getquerystring());
}

Cloning Data Objects in perl

use Storable qw/dclone/;

my $hash =

$newhash = dclone($hash);

You may get an error if you try to simply use an equal to sign within in a Perl object.