Overblog
Suivre ce blog Administration + Créer mon blog

Articles

Articles (English)

Articles (French)

Want to step on the Java stage?
Quick Start Guide to Oracle Fusion Development

List of all blog entries

Some Forms books

 

Search

Recherche

 

Free tool

Look and Feel Project

 

11 octobre 2005 2 11 /10 /octobre /2005 00:00

A simple way to display database blob stored image in a jsp

One of my recent assignments was to build a web page that needed to show images that are stored in a database.

Having a strong PLSQL coding background the solution seemed to be close to my fingertips and I created a PLSQL function that I called through the PLSQL Gateway.

 

 CREATE TABLE PHOTOS
(
  IMAGEID   NUMBER(10),
  IMAGE     BLOB
)
/

CREATE OR REPLACE PROCEDURE Display_Image(p_id NUMBER) IS
    Photo BLOB
    v_amt NUMBER DEFAULT 4096;
    v_off NUMBER DEFAULT 1;
    v_raw RAW(4096);
  BEGIN

    -- Get the blob image
    SELECT image
    INTO   Photo
    FROM   PHOTOS
    WHERE  IMAGEID = p_id;

    owa_util.mime_header('images/gif'); 
    BEGIN
      LOOP
        -- Read the BLOB
        dbms_lob.READ(Photo, v_amt, v_off, v_raw);
        -- Display image
        htp.prn(utl_raw.cast_to_varchar2(v_raw));
        v_off := v_off + v_amt;
        v_amt := 4096;
      END LOOP;
      dbms_lob.CLOSE(Photo);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;

  END;
/


The web page could be called with the following URL and did what I was asked to do

img src="http://machine:port/pls/myapp/display_image?p_id=12" width="115" border="0"

 

This works like a charm but has a caveat that I discovered when presenting it to the network department. The network department didn’t like the idea of exposing the database server to the Internet, which indeed is considerably unsafe.

Back to the whiteboard, I thought of using Web Service. This approach just didn’t feel right and appeared to be too complex for this little solution to build. Eventually I decided to write a JavaServer Page to do the job.


 The Java class to stream the image from the database column

package image;

import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class images
{
  /*-------------------------
   *   Get the Blob image
   *------------------------*/
  public static byte[] getPhoto (OracleConnection conn, int iNumPhoto)
       throws Exception, SQLException
  {

    String req = "" ;
    Blob img ;
    byte[] imgData = null ;
    Statement stmt = conn.createStatement ();
   
    // Query
    req = "Select image From IMAGES Where ImageID = " + iNumPhoto ;
   
    ResultSet rset  = stmt.executeQuery ( req );
   
    while (rset.next ())
    {   
      img = rset.getBlob(1);
      imgData = img.getBytes(1,(int)img.length());
    }   
   
    rset.close();
    stmt.close();
   
    return imgData ;

  }
 
} 

 The JavaServer Page includes the bean so its methods can be accessed in the JSP page using scriplets and “photo” as a named bean reference

 

<%@ page import = "image.*" %>
<%@ page import = "java.io.*" %>
<%@ page import = "oracle.jdbc.OracleConnection" %>
<jsp:useBean id="photo" class="image.images" scope="session" />
<%
 
  int iNumPhoto ;
  oracle.jdbc.driver.OracleConnection conn = null;
 
  if ( request.getParameter("imgID") != null )
  {
  
    iNumPhoto = Integer.parseInt(request.getParameter("imgID")) ;  
 
    try
    { 
       conn = …………;
       conn.setAutoCommit (false); 
 
       // get the image from the database
       byte[] imgData = photo.getPhoto( conn, iNumPhoto  ) ;  
       // display the image
       response.setContentType("image/gif");
       OutputStream o = response.getOutputStream();
       o.write(imgData);
       o.flush();
       o.close();
    }
    catch (Exception e)
    {
      e.printStackTrace();
      throw e;
    }
    finally
    {
      … Close the connexion … ;
    } 
  }
%>

To display the image on the web, I now use the following image URL

img src="image.jsp?imgID=12" width="115" border="0"

 

Special thank you to Frank Nimphius for its good suggestions on the style ;o)

Francois




Partager cet article
Repost0
Francois Degrelle - dans J2EE
commenter cet article …
9 octobre 2005 7 09 /10 /octobre /2005 00:00

A PL/SQL procedure to extract rows to a file

Sometimes, we need, in an application, to extract some table rows to a flat file.
This is a PL/SQL procedure that allows to extract the rows of a table, or the INSERT ordrers.

You can download the script here

Extraction_table procedure
 

CREATE OR REPLACE PROCEDURE Extraction_Table
 (
  PC$Table      in Varchar2,                      -- Table name
  PC$File       in Varchar2,                      -- Output file name
  PC$Directory  in Varchar2,                      -- Outpur Directory name
  PC$Delimiter  in Varchar2 Default ',',          -- delimiter character
  PC$Header     in Varchar2 Default 'O',          -- Output column header
  PC$DateFMT    in Varchar2 Default 'DD/MM/YYYY', -- Date format
  PC$Where      in Varchar2 Default Null,         -- Where clause
  PC$Order      in Varchar2 Default Null          -- Order by
 ) IS


LF$Fichier UTL_FILE.FILE_TYPE ;
LC$Ligne Varchar2(32767) ;
LI$I  Integer ;
LC$DateFMT  Varchar2(40) := '''' || PC$DateFMT || '''' ;


TYPE REFCUR1 IS REF CURSOR ;
cur  REFCUR1;


-- Columns --
  CURSOR C_COLTAB ( PC$Tab IN VARCHAR2 ) IS
  SELECT
   COLUMN_NAME,
 DATA_TYPE
  FROM
   USER_TAB_COLUMNS
  WHERE
   TABLE_NAME = PC$Tab
  AND
   DATA_TYPE IN ('CHAR','VARCHAR2','NUMBER','DATE','FLOAT')
  ;

LC$Separateur Varchar2(2) := PC$Delimiter ;
LC$Requete   Varchar2(10000) ;
LC$Desc       Varchar2(10000) ;
LC$SQLW       VARCHAR2(10000):= 'SELECT ';
LC$Col       VARCHAR2(256);


-----------------------------
--  Open the output file   --
-----------------------------
FUNCTION Ouvrir_fichier
 (
  PC$Dir in Varchar2,
  PC$Nom_Fichier in Varchar2
 ) RETURN UTL_FILE.FILE_TYPE
IS
  Fichier UTL_FILE.FILE_TYPE ;
  LC$Msg Varchar2(256);

Begin

  Fichier := UTL_FILE.FOPEN( PC$Dir, PC$Nom_Fichier, 'W', 32764 ) ;

  If not UTL_FILE.IS_OPEN( Fichier ) Then
 LC$Msg := 'Erreur ouverture du fichier (' || PC$Dir || ') ' || PC$Nom_Fichier ;
   RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
  End if ;

  Return( Fichier ) ;

Exception

When UTL_FILE.INVALID_PATH Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File location is invalid.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MODE Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The open_mode parameter in FOPEN is invalid.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILEHANDLE Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File handle is invalid.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OPERATION Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File could not be opened or operated on as requested.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.READ_ERROR Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the read operation.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.WRITE_ERROR Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the write operation.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INTERNAL_ERROR then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Unspecified PL/SQL error';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.CHARSETMISMATCH Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'A file is opened using FOPEN_NCHAR,'
   || ' but later I/O operations use nonchar functions such as PUTF or GET_LINE.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.FILE_OPEN Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested operation failed because the file is open.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MAXLINESIZE Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The MAX_LINESIZE value for FOPEN() is invalid;'
   || ' it should be within the range 1 to 32767.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILENAME Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The filename parameter is invalid.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.ACCESS_DENIED Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Permission to access to the file location is denied.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OFFSET Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid;'
   ||' it should be greater than 0 and less than the total number of bytes in the file.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.DELETE_FAILED Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file delete operation failed.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.RENAME_FAILED Then
 LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file rename operation failed.';
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;

When others Then
 LC$Msg := 'Erreur : ' || To_char( SQLCODE ) || ' sur ouverture du fichier ('
    || PC$Dir || ') ' || PC$Nom_Fichier ;
 RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;

End Ouvrir_fichier ;

Begin

  -- Open file  --
  LF$Fichier := Ouvrir_fichier( PC$Directory, PC$File ) ;

  -- Output column header ? --
  If Upper(PC$Header) = 'O' Then
 LI$I := 1 ;
 For COLS IN C_COLTAB( PC$Table ) Loop
    If LI$I = 1 Then
       LC$Ligne := LC$Ligne || COLS.COLUMN_NAME ;
    Else
       LC$Ligne := LC$Ligne || LC$Separateur || COLS.COLUMN_NAME ;
    End if ;
    LI$I := LI$I + 1 ;
 End loop ;
 -- Output column header --
 UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ;
  ElsIf Upper(PC$Header) = 'I' Then
    LC$Separateur := ',' ;
    LC$Desc := 'INSERT INTO ' || PC$Table || ' (' ;
    LI$I := 1 ;
    For COLS IN C_COLTAB( PC$Table ) Loop
      If LI$I = 1 Then
   LC$Desc := LC$Desc || COLS.COLUMN_NAME ;
      Else
   LC$Desc := LC$Desc || LC$Separateur || COLS.COLUMN_NAME ;
      End if ;
      LI$I := LI$I + 1 ;
    End loop ;
    LC$Desc := LC$Desc || ' ) VALUES (' ;
  End if ;

  -- Building of the query --
  LI$I := 1 ;

  FOR COLS IN C_COLTAB( PC$Table ) LOOP
    IF LI$I > 1 THEN
       LC$SQLW := LC$SQLW || '||' ;
    END IF ;

    If COLS.DATA_TYPE IN ('NUMBER','FLOAT') Then
     LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',To_char("'
        || COLS.COLUMN_NAME || '"))' ;
    ElsIf COLS.DATA_TYPE = 'DATE' Then
       If Upper(PC$Header) = 'I' Then
           LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL,''NULL'',''to_date(''''''||'
              || 'To_char("' || COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' || '||'''''','''|| LC$DateFMT||''')'')' ;
       Else
    LC$Col := 'To_char("'|| COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' ;
       End if ;
    Else
       If Upper(PC$Header) = 'I' Then
     LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',' || ''''''''''
        || '||"'|| COLS.COLUMN_NAME || '"' || '||' || ''''''''')' ;
       Else
     LC$Col := '"'|| COLS.COLUMN_NAME || '"' ;
       End if ;
    End if ;

    IF LI$I = 1 THEN
       LC$SQLW := LC$SQLW || LC$Col ;
    ELSE
       LC$SQLW := LC$SQLW || '''' || LC$Separateur || '''' || '||' || LC$Col  ;
    END IF ;
    LI$I := LI$I + 1 ;
  END LOOP ;

  LC$Requete := LC$SQLW || ' FROM ' || PC$Table ;

  If PC$Where is not null Then
    -- add the WHERE clause --
    LC$Requete := LC$Requete || ' WHERE ' || PC$Where ;
  End if ;
  If PC$Order is not null Then
    -- add the ORDER BY clause --
    LC$Requete := LC$Requete || ' ORDER BY ' || PC$Order ;
  End if ;


  -- Extrac the raws --
  Open cur For LC$Requete ;
  Loop
 Fetch cur Into LC$Ligne ;
 Exit when cur%NOTFOUND ;
 -- Write to the output file --
 If Upper(PC$Header) = 'I' Then
   UTL_FILE.PUT_LINE( LF$Fichier, LC$Desc || LC$Ligne || ' );' ) ;
 Else
   UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ;
 End if ;
  End loop ;

  Close cur ;

  -- Close file --
  UTL_FILE.FCLOSE( LF$Fichier ) ;

End Extraction_Table ;
/


Examples:

Extraction of the EMP raws to the EMP.TXT file with column header :

 SQL> execute extraction_table( 'EMP','EMP.TXT','FICHIERS_OUT' ) ;

Procédure PL/SQL terminée avec succès.

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,18/12/1980,880,,20
7499,ALLEN,SALESMAN,7698,21/02/1981,1936,300,30
7521,WARD,SALESMAN,7698,23/02/1981,1375,500,30
7566,JONES,MANAGER,7839,03/04/1981,3273,,20
7654,MARTIN,SALESMAN,7698,29/09/1981,1375,1400,30
7698,BLAKE,MANAGER,7839,02/05/1981,3135,,30
7782,CLARK,MANAGER,7839,10/06/1981,2695,,10
7788,SCOTT,ANALYST,7566,20/04/1987,3300,,20
7839,KING,PRESIDENT,,18/11/1981,5500,,10
7844,TURNER,SALESMAN,7698,09/09/1981,1650,0,30
7876,ADAMS,CLERK,7788,24/05/1987,1210,,20
7900,JAMES,CLERK,7698,04/12/1981,1045,,30
7902,FORD,ANALYST,7566,04/12/1981,3300,,20
7934,MILLER,CLERK,7782,24/01/1982,1430,,10

 

Extraction of INSERT orders for the EMP table where JOB='CLERK' :

SQL>  execute extraction_table( 'EMP','EMP.INS','FICHIERS_OUT', ',', 'I', 'JOB = ''CLERK''' ) ;

Procédure PL/SQL terminée avec succès. 

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7369,'SMITH','CLERK',7902,to_date('18/12/1980','DD/MM/YYYY'),880,NULL,20 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7876,'ADAMS','CLERK',7788,to_date('24/05/1987','DD/MM/YYYY'),1210,NULL,20 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7900,'JAMES','CLERK',7698,to_date('04/12/1981','DD/MM/YYYY'),1045,NULL,30 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7934,'MILLER','CLERK',7782,to_date('24/01/1982','DD/MM/YYYY'),1430,NULL,10 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9991,'Dupontont','CLERK',NULL,NULL,NULL,NULL,NULL );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9992,'Duboudin','CLERK',NULL,NULL,NULL,NULL,NULL );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9994,'Schmoll','CLERK',NULL,NULL,2500,NULL,20 );

 

Francois

Partager cet article
Repost0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article …
6 octobre 2005 4 06 /10 /octobre /2005 00:00

Frank Zappa quotations

Since I am a very big fan of the music of Frank Zappa, i get everything i can fin about him.

Here is some of his best quotations:

 

Stupidity is the basic building block of the universe.

There is no hell. There is only France.

Don't mind your make-up, you'd better make your mind up.

Without music to decorate it, time is just a bunch of boring production deadlines or dates by which bills must be paid.

It is always advisable to be a loser if you cannot become a winner.

A mind is like a parachute. It doesnt work if it's not open.

If we can't be free at least we can be cheap.

Sometimes you got to get sick before you can feel better.

You can't be a Real Country unless you have a BEER and an airline - it helps if you have some kind of a football team or some nuclear weapons, but at the very least you need a BEER.

There will never be a nuclear war; there's too much real estate involved.

Consider for a moment any beauty in the name Ralph.

Why do you necessarily have to be wrong just because a few million people think you are?

Outdoors for me is walking from the car to the ticket desk at the airport

You drank beer, you played golf, you watched football - WE EVOLVED!

You have just destroyed one model XQJ-37 nuclear powered pansexual roto-plooker....and you're gonna have to pay for it.

Interviewer: "So Frank, you have long hair. Does that make you a woman?"
FZ: "You have a wooden leg. Does that make you a table?"

Without deviation from the norm, 'progress' is not possible.

It's better to have something to remember than nothing to reget...

Who are the brain police?

The people of your century no longer require the service of composers.
A composer is as useful to a person in a jogging suit as a dinsoaur turd in the middle of his runway.

There are more love songs than anything else.
If songs could make you do something we'd all love one another.

I'm not black, but there's a whole lot of times I wish I could say I'm not white.

Politics is the entertainment branch of industry.

 

François


Partager cet article
Repost0
Francois Degrelle - dans Other
commenter cet article …
6 octobre 2005 4 06 /10 /octobre /2005 00:00

Managing record sets returned by a stored function in a client side application

I often see on forums, questions related to the managing, for the client side, of record sets returned by stored functions.

This is an article that shows some basic examples about how to manage record sets in a client side application.

Francois
Partager cet article
Repost0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article …
4 octobre 2005 2 04 /10 /octobre /2005 00:00

New Forms-Javabean tutorial

Hello,

 

I have written a little tutorial that demonstrates how to implement a Javabean alert box in Forms that allows to select more than 3 options.

 

Javabean Alert Box

 

Francois



Partager cet article
Repost0
Francois Degrelle - dans Oracle Forms
commenter cet article …
4 octobre 2005 2 04 /10 /octobre /2005 00:00

New Forms-Javabean tutorial

Hello,

 

I have written a little tutorial that demonstrates how to implement a Javabean input dialog box in Forms

 

Javabean input dialog box

 

Francois


Partager cet article
Repost0
Francois Degrelle - dans Oracle Forms
commenter cet article …
4 octobre 2005 2 04 /10 /octobre /2005 00:00

Oracle PL/SQL - Forms tutorials home page

Hello there,

I am happy to present my home page, about Oracle PL/SQL and Forms tutorials:

English tutorials

French tutorials

Francois


Partager cet article
Repost0
Francois Degrelle - dans Oracle Forms
commenter cet article …