Concatenate sets of intervals

Thursday, 5 May, 2011 Leave a comment
CLEAR;
with t as (
           select 11  AS ID, 1  AS val1, 'B'  AS val2 from dual union all
           select 12,1,'A' from dual union ALL
           select 13,1,'A' from dual union all           
           select 14,1,'B' from dual union ALL
           select 15,1,'A' from dual UNION ALL
           select 16,1,'B' from dual union ALL           
           select 17,1,'B' from dual union ALL
           select 18,1,'A' from dual union ALL           
           select 19,2,'B' from dual union all
           select 20,2,'A' from dual union ALL
           select 21,2,'B' from dual union ALL
           select 22,2,'A' from dual union ALL
           select 23,2,'B' from dual union ALL           
           select 24,2,'A' from dual union ALL           
           select 25,1,'B' from dual union ALL                      
           select 26,1,'A' from dual
           ),
tt AS(select t.*,
        case when lag (val2) over (partition by val1 order by id) || val2 in ('A', 'BA') then 1 end as start_of_group,
        case when lead(val2) over (partition by val1 order by id) || val2 in ('B', 'AB') then 1 end as   end_of_group
      from t),
ttt AS (SELECT tt.*, COUNT(start_of_group) over(PARTITION BY val1 ORDER BY id) AS group_no FROM tt),
tttt AS (SELECT row_number() over(ORDER BY val1, group_no) AS rn, 
       val1, 
       t1.ID AS id1, 
       t2.ID AS id2, 
       t1.val2 AS VAL2A, 
       t2.val2 AS VAL2B
  FROM (SELECT * FROM ttt WHERE start_of_group IS NOT NULL) t1
  FULL JOIN (SELECT * FROM ttt WHERE end_of_group IS NOT NULL) t2
 USING (val1, group_no)
 ORDER BY rn)
  SELECT * FROM tttt;
Advertisements
Categories: PL/SQL Tags:

Getting a Request Parameter Using JSTL in a JSP Page

Thursday, 17 February, 2011 Leave a comment

When using the JSTL’s expression language (see Enabling the JSTL Expression Language in a JSP Page), the request parameters are made available in the implicit object param. This example demonstrates how to include the value of a request parameter from the query string or posted data in the generated output:

<%-- Declare the core library --%>
<%@ taglib uri="/WEB-INF/tld/c.tld" prefix="c" %>

<c:choose>
    <c:when test="${empty param.name}">
        Please enter your name.
    </c:when>
    <c:otherwise>
        Hello <b><c:out value="${param.name}" /></b>!
    </c:otherwise>
</c:choose>

If the page was accessed with the URL:
http://hostname.com/mywebapp/mypage.jsp?name=UserName
the resulting output would be:
Hello UserName!

Categories: jsp Tags: , ,

Getting a Request Parameter in a Servlet

Monday, 31 January, 2011 Leave a comment

In a GET request, the request parameters are taken from the query string (the data following the question mark on the URL). For example, the URL http://hostname.com?p1=v1&p2=v2 contains two request parameters – – p1 and p2. In a POST request, the request parameters are taken from both query string and the posted data which is encoded in the body of the request. This example demonstrates how to get the value of a request parameter in either a GET or POST request.

// See also The Quintessential Servlet

// This method is called by the servlet container to process a GET request.
public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    doGetOrPost(req, resp);
}

// This method is called by the servlet container to process a POST request.
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    doGetOrPost(req, resp);
}

// This method handles both GET and POST requests.
private void doGetOrPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    // Get the value of a request parameter; the name is case-sensitive
    String name = "param";
    String value = req.getParameter(name);
    if (value == null) {
        // The request parameter 'param' was not present in the query string
        // e.g. http://hostname.com?a=b
    } else if ("".equals(value)) {
        // The request parameter 'param' was present in the query string but has no value
        // e.g. http://hostname.com?param=&a=b
    }

    // The following generates a page showing all the request parameters
    PrintWriter out = resp.getWriter();
    resp.setContentType("text/plain");

    // Get the values of all request parameters
    Enumeration enum = req.getParameterNames();
    for (; enum.hasMoreElements(); ) {
        // Get the name of the request parameter
        name = (String)enum.nextElement();
        out.println(name);

        // Get the value of the request parameter
        value = req.getParameter(name);

        // If the request parameter can appear more than once in the query string, get all values
        String[] values = req.getParameterValues(name);

        for (int i=0; i<values.length; i++) {
            out.println("    "+values[i]);
        }
    }
    out.close();
}

w3

Categories: JAVA EE Tags: , , ,

How to get information about user using Servlet

Friday, 21 January, 2011 1 comment

The method getRemoteUser() of the HttpServletRequest gives the username of the client. With the remote user’s name, a servlet can save information about each client. Over the long term, it can remember each individual’s preferences. For the short term, it can remember the series of pages, viewed by the client and use them to add a sense of state to a stateless HTTP protocol.

A simple servlet that uses getRemoteUser() can greet its clients by name and remember when each last logged in as shown in the example below:

import java.io.*;
import java.sql.Date;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class PersonalizedWelcome extends HttpServlet{
    
    Hashtable accesses = new Hashtable();
    
    public void doGet(HttpServletRequest req,HttpServletResponse res)
            throws ServletException,IOException{
        
        res.setContentType("text/plain");
        PrintWriter out= res.getWriter();
        
        // Some introductory HTML...
        String remoteUser = req.getRemoteUser();
        
        // See if the client is allowed
        if(remoteUser == null){
            out.println("Welcome");
        } else{
            out.println("Welcome " + remoteUser + "!");
            Date lastAccess = (Date)accesses.get(remoteUser);
            if(lastAccess==null){
                out.println("This last visit was "  + accesses.get(remoteUser));
            }
            if(remoteUser.equals("Rohit")){
                out.println("Shall we play  a game");
            }
            accesses.put(remoteUser, new Date());
        }
        
        //continue handling the request
    }
}

The following HttpServletRequest interface methods are also available to access security information about the component’s caller:

getRemoteUser: This method is called to get the user name with which the client authenticated. It returns the name of remote user associated by the container with the request. If no user has been authenticated, the method returns null.
isUserInRole: This method determines whether a remote user is in a specific security role. If no user has been authenticated, it returns false. This method expects a String user role-name parameter. The security-role-ref element should be declared in the deployment descriptor with a role-name sub-element containing the role name to be passed to the method.
getUserPrincipal: The getUserPrinicipal method is called to determine the principal name of the current user and returns a java.security.Principal object. If no user has been authenticated, it returns null. Calling the getName method on the Principal returned by getUserPrincipal returns the name of the remote user.

getRemoteUser() [java.lang.String]
Returns the login of the user making this request, if the user has been authenticated, or null if the user has not been authenticated.

isUserInRole(String role) [boolean]
Returns a boolean indicating whether the authenticated user is included in the specified logical “role”.

getUserPrincipal() [java.security.Principal]
Returns a java.security.Principal object containing the name of the current authenticated user

1 w3
2 w3
3 w3

Categories: JAVA EE, secure Tags: ,

Oracle PL/SQL – Merge table

Thursday, 20 January, 2011 2 comments
clear;
create table myTable
(pid number, sales number, status varchar2(6));
 create table myTable2
(pid number, sales number, status varchar2(6));

 insert into myTable  values(2,24,'CURR');
 insert into myTable  values(3, 0,'OBS' );
 insert into myTable  values(4,42,'CURR');
 insert into myTable  values(6,56,'C44URR');


 insert into myTable2 values(1,12,'CURR');
 insert into myTable2 values(2,13,'NEW' );
 insert into myTable2 values(3,15,'CURR');

select * from myTable;
select 't2' from dual;

merge into myTable2 m
using myTable d
on (m.pid = d.pid)
when matched then
  update
     set m.sales = d.sales, m.status = d.status 
when not matched then  
   insert values (d.pid, d.sales, d.status);
  
 delete myTable2 where pid in (select m.pid from myTable2 m where m.pid not in (select pid from myTable));

 select * from myTable order by pid;
 select * from myTable2 order by pid;

 drop table myTable;
 drop table myTable2;
Categories: PL/SQL Tags:

Autoincrement primary key for Oracle

Thursday, 20 January, 2011 1 comment

Suppose you have a database and you want each entry to be identified by a unique number. You can do this easily in mysql by specifying “auto_increment” for your number, but Oracle makes you work a little more to get it done.

Here is one way to do it by creating two database objects, a sequence and a trigger. I find myself wanting to do this every now and then but not often enough that I remember the syntax from time to time, so I decided it was time to write myself up a little cheat sheet. This is an extremely basic outline, so please try it first on a test table if you don’t know what you’re doing.

1. Let’s say we have a table called “test” with two columns, id and testdata. (This is just a dumb quick example, so I won’t bother to specify any constraints on id.)

create table test (id number, testdata varchar2(255)); 

2. Next we’ll create a sequence to use for the id numbers in our test table.

create sequence test_seq 
start with 1 
increment by 1 
nomaxvalue; 

You could change “start with 1” to any number you want to begin with (e.g. if you already have 213 entries in a table and you want to begin using this for your 214th entry, replace with “start with 214”). The “increment by 1” clause is the default, so you could omit it. You could also replace it with “increment by n” if you want it to skip n-1 numbers between id numbers. The “nomaxvalue” tells it to keep incrementing forever as opposed to resetting at some point. i (I’m sure Oracle has some limitation on how big it can get, but I don’t know what that limit is).

3. Now we’re ready to create the trigger that will automatically insert the next number from the sequence into the id column.

create trigger test_trigger
before insert on test
for each row
begin
IF :new.id IS NULL then
select test_seq.nextval into :new.id from dual;
end IF;
end;
/

Obviously you would replace “test_trigger” with something a little more meaningful for the database table you want to use it with, “test” would be your table name, and the “id” in :new.id would be replaced with the name of the column. Every time a new row is inserted into test, the trigger will get the next number in the sequence from test_seq and set the “id” column for that row to whatever the sequence number is. Note that sequences sometimes appear to skip numbers because Oracle caches them to be sure that they are always unique, so this may not be your ideal solution if it’s really important that the id is exactly sequential and not just mostly sequential and always unique.

Greg Malewski writes:

You’ve demonstrated an implementation using triggers. This is not necessary, since instead it can be included as part of the INSERT statement. Using your example, my INSERT statement would be:

    
insert into test values(test_seq.nextval, 'voila!');

Here are a couple of questions the above might raise. This is pretty intuitive stuff, but I’m aiming it at the Oracle newbie since no expert would be reading this page anyway.

How do you tell what sequences and triggers are already out there?

select sequence_name from user_sequences;
select trigger_name from user_triggers;

How do you get rid of a sequence or trigger you created?

drop sequence test_seq;
drop trigger test_trigger;

Again, replace test_seq and test_trigger with the specific names you used. You can also keep the trigger but disable it so it won’t automatically populate the id column with every insert (and enable it again later if you want):

alter trigger test_trigger disable;
alter trigger test_trigger enable;

w3

Categories: PL/SQL Tags: ,

Oracle DBMS_JOB – Session Killer

Thursday, 11 November, 2010 1 comment

Note This package is deprecated and has been supplanted by DBMS_SCHEDULER.
Source {ORACLE_HOME}/rdbms/admin/dbmsjob.sql
Dependencies job$, dba_jobs, all_jobs, user_jobs, dba_jobs_running, all_jobs_running, user_jobs_running

Job Intervals
Execute daily 'SYSDATE + 1'
Execute once per week 'SYSDATE + 7'
Execute hourly 'SYSDATE + 1/24'
Execute every 10 min. 'SYSDATE + 10/1440'
Execute every 30 sec. 'SYSDATE + 30/86400'
Do not re-execute NULL

Security Model Execute is granted to PUBLIC with GRANT option

Stored Procedure

create or replace procedure kill_session(p_program in varchar)
as
begin
    for x in (select *
                 from v$session 
                where lower(PROGRAM) = lower(p_program) and upper(USERNAME) <> 'ADMIN')
    loop
        execute immediate 'alter system kill session ''' || 
                 x.sid || ',' || x.serial# || '''';
        dbms_output.put_line( 'Alter session done' );
    end loop;
end;
/

JOB

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what     => 'kill_session(''cpi2.exe'');'
     ,next_date => to_date('11.11.2010 03:50:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE+1)'
     ,no_parse  => false
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
Categories: Oracle Tags: ,