Struts plugin in Eclipse Helios

Go to Eclipse and from menu select Help->Install New Software.
Add the following update site using 'Add' button

http://download.jboss.org/jbosstools/updates/development/

Select Struts present under 'Web and Java EE Development'

AJAX script on JSP

var req;

function getText( ) {
var key = document.getElementById("key");
// var keypressed = document.getElementById("keypressed");
// keypressed.value = key.value;
// var url = "/ajaxdecimalcodeconverter/response?key=" + escape(key.value) + "$idn=" ;
var url = "response.jsp?key=" + key.value + "&idn=" + Math.ceil( Math.random() * 100) ;
if (window.XMLHttpRequest) {
req = new XMLHttpRequest( );
}
else if (window.ActiveXObject) {
req = new ActiveXObject("Microsoft.XMLHTTP");
}
req.open("Get",url,true);
req.onreadystatechange = callbackValue;
req.send(null);
}

function callbackValue( ) {
if (req.readyState==4) {
if (req.status == 200) {
var decimal = document.getElementById('decimal');
decimal.value = req.responseText;

}
}
//clear( );
}
function clear( ) {
var key = document.getElementById("key");
key.value="";
}
function focusIn( ) {
document.getElementById("key").focus( );
}

How to plugin sql into Eclipse?

Help -> Install New Software -> Work With :

http://eclipsesql.sourceforge.net/

Select SQL Exporter, Click Next, Click Finish.

File -> New -> other , SQL Development -> SQL File,
Enter file name, create database (any) connection throuh wizard and
click finish.

How to write blob into OS file ?

1. create a database directory :
create or replace database directory image_test_dir
as 'd:\db_dir\'

2. use dbms_lob and utl_file package to read data chunk wise.

declare
    v_offset        number  := 1;
    v_buffer_size   number  := 1000;
    v_file_handle   UTL_FILE.FILE_TYPE;
    v_src_lob       blob;
    v_len           integer;
    v_buffer          raw(1000);

begin
    /*select image_front into v_src_lob
    from inward_image where rownum=1;*/
    
    select img into v_src_lob
    from image_upload where rownum=1;
    
    v_len   := DBMS_LOB.GETLENGTH(v_src_lob);
    
    v_file_handle := UTL_FILE.FOPEN('IMAGE_TEST_DIR', 'f.pdf', 'wb');
    
    while v_offset < v_Len
    loop
        
        DBMS_LOB.READ(v_src_lob, v_buffer_size, v_offset, v_buffer );
        
        UTL_FILE.PUT_RAW(v_file_handle, v_buffer);
        
        --DBMS_OUTPUT.PUT_LINE( v_offset || '                '  ||  v_buffer_size );
        
        v_offset := v_offset + v_buffer_size;
        
    
    end loop;
    
    
    --DBMS_OUTPUT.PUT_LINE();
    
    UTL_FILE.FFLUSH(v_file_handle);
    
    UTL_FILE.FCLOSE(v_file_handle);
    
    
end;          

how to load os file into blob field ?

If you want to to load a file (i.e. text file, image file, pdf file etc.) into
a blob field, then follow the following procedure.

1. create a database directory :
create or replace database directory image_test_dir
as 'd:\db_dir\'

2. Use dbms_Lob package to load os file into blob;

   declare
    
        v_file_loc      bfile;
        
        v_Image_size    integer;
        
        v_img           blob;
    
    begin
        
        --v_file_loc      := bfilename('IMAGE_TEST_DIR', 'testRpt.pdf');
        v_file_loc      := bfilename('IMAGE_TEST_DIR', 'front.tif');
        
        v_image_size    := DBMS_LOB.GETLENGTH(v_file_Loc);
        
        dbms_output.put_line(v_image_size);
        
        insert into image_upload(id, img)
        values (1, empty_blob()) returning img into v_img;
        
        dbms_Lob.open(v_file_Loc, DBMS_LOB.FILE_READONLY );        
        
        DBMS_LOB.LOADFROMFILE( v_img, 
         v_file_loc,
         v_image_size   );
         
         DBMS_LOB.CLOSE(v_file_Loc);
        
        commit;
        
    
    exception
        when others then dbms_output.put_line('Exception :  ' || sqlerrm);    
        
    end;

Line NUmber in Eclipse

To turn on line numbering in Eclipse, simply do the following;

1) Windows -> Preferences
2) General – > Editors -> Text Editors

and click the “Show Line Numbers” checkbox.

Problem to login Oracle BI Publisher Enterprise with Administration

1. go to \xmlp\XMLP\Admin\Configuration
2. open xmlp-server-config.xml in notepad
3. specify SECURITY_MODEL property value to XDO. For example:


<property name="SECURITY_MODEL" value="XDO">
Now you can log in at username : Administrator
and password : Administrator

Get the N-th highest value Or N-th lowest Value from a column in a table

I want to find the N-th highest or lowest salary from EMP table.

At first, create table emp:

create table emp

(empid int, salary number);


Insert some data into emp:


insert into emp values(1, 200);

insert into emp values(10, 150);

insert into emp values(15, 10000);

insert into emp values(6, 11100);

insert into emp values(4, 12000);

insert into emp values(5, 5000);

insert into emp values(20, 2000);

insert into emp values(30, 200);


insert into emp values(40, 200);

commit;

Select data from table: 

select * from emp;

Now i want to find out the third highest salary from the above employees:

select * from
emp a

where 3 = (select count(distinct(b.salary)) from emp b where b.salary>=a.salary);

Now i want to find out the second highest salary from the above employees:

select * from
emp a
where 2 = (select count(distinct(b.salary)) from emp b where b.salary>=a.salary);

Now i want to find out the second lowest salary from the above employees:

select * from
emp a
where 2 = (select count(distinct(b.salary)) from emp b where b.salary<=a.salary);

So for N-th highest or N-th lowest salary, i just include sql bind variable in the query:

select * from
emp a
where &N = (select count(distinct(b.salary)) from emp b where b.salary>=a.salary);



select * from
emp a
where &N = (select count(distinct(b.salary)) from emp b where b.salary<=a.salary);

How to connect sys without sysdbda or sysoper privileges

conn sys/sys as sysdba


alter system set O7_DICTIONARY_ACCESSIBILITY = true scope=spfile;


O7_DICTIONARY_ACCESSIBILITY is static parameter restart is necessary


shutdown immediate

startup

conn sys/sys

To create an external table you have to create a directory.
this directory contains a file, book1.csv
so logon to sys as sysdba
then issue the command
create or replace directory EXTERNAL as 'E:\EXTERNAL\';

GRANT READ, WRITE ON DIRECTORY EXTERNAL TO SCOTT;

Now connect scott

now issue the following script :

create table extern_emp
( 
    id      varchar2(10),
    name    varchar2(200),
    address varchar2(1000)
)
    organization external
    (
        default directory EXTERNAL
        access parameters
        (
            records delimited by newline
            fields  terminated by ','
        )
        location('book1.csv')
    )
    parallel
; 

But the CSV files are comma-seperated.


Any field of the csv file may contain comma.
So the previous script will be the following with highlighted portion:

create table extern_emp
( 
    id      varchar2(10),
    name    varchar2(200),
    address varchar2(1000)
)
    organization external
    (
        default directory EXTERNAL
        access parameters
        (
            records delimited by newline
            fields  terminated by ','
            optionally enclosed by '"'
        )
        location('book1.csv')
    )
    parallel
;