Mardi 25 mars 2008

Test if a file exists on a web site from the database

Here is a code snippet showing how you can test if a file exists on a web site (that you have access, of course).
It uses the UTL_HTTP database package.

 DECLARE
  url       VARCHAR2(256) := 'http://sheikyerbouti.developpez.com/tmp/f6_menus/menudef.mmb';
  username  VARCHAR2(256);
  password  VARCHAR2(256);
  req       UTL_HTTP.REQ;
  resp      UTL_HTTP.RESP;
BEGIN
  req := UTL_HTTP.BEGIN_REQUEST(url);
  IF (username IS NOT NULL) THEN
    UTL_HTTP.SET_AUTHENTICATION(req, username, password);
  END IF;
  resp := UTL_HTTP.GET_RESPONSE(req);
  DBMS_OUTPUT.PUT_LINE('response -->' || resp.status_code);
END;

If the file exists, you get a 200 (also known as UTL_HTTP.HTTP_OK) return code. If it does not exist, you get the famous 404 (also known as UTL_HTTP.HTTP_NOT_FOUND) return code.
par Francois Degrelle publié dans : Oracle PL/SQL
ajouter un commentaire commentaires (0)    créer un trackback recommander
Mardi 25 septembre 2007

PL/SQL : a procedure to output content of BLOB columns onto the file system

It is frequently asked how to load a BLOB table column from the content of an external file.
This can be achieved by using the DBMS_LOB package's functions.
Sometimes it is also useful to extract the BLOB content back to a file.

Here is a procedure that achieves this task:
(Available since Oracle database 9i, that introduced the UTL_FILE.PUT_RAW() function)

CREATE OR REPLACE PROCEDURE Write_Binary_file
(
   PC$Directory IN VARCHAR2
  ,PC$File_Name IN VARCHAR2
  ,PC$SQL_Order IN VARCHAR2
  ,PB$Raise     IN BOOLEAN DEFAULT FALSE
)
-- -------------------------------------------
-- Procedure to dump a BLOB column onto a file
-- -------------------------------------------
-- parameters:
-- PC$Directory : name of an existing Oracle Directory
-- PC$File_Name : name of the expected output file
-- PC$SQL_Order : SQL order to select the BLOB column
-- PB$Raise     : boolean to indicate if the process
--                would be stopped after an error
--
IS
  src_lob    BLOB;
  buffer     RAW(16384);
  amt        BINARY_INTEGER := 16384;
  pos        INTEGER := 1;
  LF$FicOUT  UTL_FILE.FILE_TYPE ;
  LC$Msg     VARCHAR2(2000) ;
BEGIN

-- get the BLOB column --
BEGIN
  EXECUTE IMMEDIATE PC$SQL_Order INTO src_lob ;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    LC$Msg := 'Write_Binary_File(): NO_DATA_FOUND' ;
    IF PB$Raise THEN
      RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
    END IF ;
    Dbms_Output.Put_Line(LC$Msg) ;
  RETURN ;
END ;

-- open the output file --
LF$FicOUT := UTL_FILE.FOPEN( PC$Directory, PC$File_Name, 'W', 32764 ) ;

-- write the file --
LOOP
  -- read the chunks --
  Dbms_Lob.READ (src_lob, amt, pos, buffer);
  -- write the chunks --
  Utl_File.Put_Raw(LF$FicOut, buffer);
  pos := pos + amt;
END LOOP;
-- close the file --
Utl_File.Fclose(LF$FicOut);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    utl_file.fclose(LF$FicOut);
  WHEN OTHERS THEN
    LC$Msg := 'Write_Binary_File() Error : ' || TO_CHAR( SQLCODE ) || ' while managing file ('
|| PC$Directory || ') ' || PC$File_Name ;
    IF PB$Raise THEN
      RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
    END IF ;
    Dbms_Output.Put_Line(LC$Msg);
END Write_Binary_file;
/


Here is a calling sample:
BEGIN
  Write_Binary_file
  (
     'OUTPUT_DIRECTORY'
    ,'image.jpg'
    ,'select image from images where id=3'
  );
END;

par Francois Degrelle publié dans : Oracle PL/SQL
ajouter un commentaire commentaires (2)    créer un trackback recommander
Lundi 13 août 2007

Laurent Schneider's Book

I've just discovered that Laurent Schneider has published a book about "Advanced Oracle SQL Programming".
As I already know and have learned from him, I am pretty sure this book would be a gold mine.
par Francois Degrelle publié dans : Oracle PL/SQL
ajouter un commentaire commentaires (0)    créer un trackback recommander
Mardi 9 mai 2006

Using a collection to sort a string

This a small function that allows to sort the content of a string (ASCII sort).
It use a collection or records to do the job:

CREATE OR REPLACE FUNCTION sort_string(pc$string IN VARCHAR2)
RETURN VARCHAR2
IS
  TYPE typ_rec IS RECORD(lettre VARCHAR2(10), nbre pls_integer);
  TYPE tab_rec IS TABLE OF typ_rec INDEX BY binary_integer;
  tabr tab_rec;
  LC$Result VARCHAR2(32767);
BEGIN

  FOR i IN 1 .. LENGTH(pc$string)
  LOOP
    tabr(ASCII(SUBSTR(pc$string, i, 1))).lettre := SUBSTR(pc$string, i, 1);
    tabr(ASCII(SUBSTR(pc$string, i, 1))).nbre   := NVL(tabr(ASCII(SUBSTR(pc$string, i, 1))).nbre, 0) + 1;
  END LOOP;

  FOR i IN tabr.FIRST .. tabr.LAST
  LOOP

    IF tabr.EXISTS(i) THEN
      LC$Result := LC$Result || rpad(tabr(i).lettre, tabr(i).nbre, tabr(i).lettre);
    END IF;

  END LOOP;

  RETURN LC$Result;
END;

That you can call with the following:

SQL> Begin
  2    dbms_output.put_line(sort_string('Zas95f1g6Az7b3k5a'));
  3  End ;
  4  /
1355679AZaabfgksz

PL/SQL procedure successfully completed.


Here are some links about 2 great articles about Sorting Collection from the AMIS Technology Blog:

Sorting PL/SQL Collections, the hard way, the intermediate way and the quite simple way (part one)
Sorting PL/SQL Collections, the quite simple way (part two: Have the SQL Engine do the heavy lifting)
par Francois Degrelle publié dans : Oracle PL/SQL
ajouter un commentaire commentaires (3)    créer un trackback recommander
Mercredi 25 janvier 2006

A dynamic "IN" clause within a PL/SQL block

It is not the first time i see some posts on forums that ask:

How can I make a dynamic IN clause within a PL/SQL block when the list of values for the IN clause is provided with a string or a table column ?

I have seen, on the ORAQA a post that explains "How to SELECT from a comma delimited list"
with the following instruction:

select * from table(sys.dbms_debug_vc2coll(1,2,'a'))

But it is not possible to populate the collection with the content of another string.

For example, the following code does not give the correct result:

SQL> set serveroutput on
SQL>
SQL> Declare
  2    LC$List  Varchar2(30) := '10,30' ;
  3    ttab sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll( LC$List ) ;
  4    Cursor Cur is Select * from table( cast(ttab  as sys.dbms_debug_vc2coll ) );
  5  Begin
  6    For C in Cur Loop
  7        Dbms_Output.put_Line( C.COLUMN_VALUE ) ;
  8    End loop ;
  9  End ;
 10 
 11  /
10,30

PL/SQL procedure successfully completed.

SQL>

because only the first element of the collection is initialized with the value '10,30'

So we have to populate the collection with every single value, which is done with the following function:

CREATE OR REPLACE FUNCTION Dynamic_In ( PC$list IN VARCHAR2 )
RETURN sys.dbms_debug_vc2coll
IS
  ttab sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll() ;
  LC$Token  VARCHAR2(100) ;
  i         PLS_INTEGER := 1 ;
BEGIN
  -- Populate the collection --
  LOOP
    LC$Token := Split( PC$List, i , ',') ;
    EXIT WHEN LC$Token IS NULL ;
    ttab.extend ;
    ttab(ttab.COUNT) := LC$Token ;
    i := i + 1 ;
  END LOOP ;
  RETURN ttab ;
END ;

Finally, we can use this function to get the correct result

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    LC$List  VARCHAR2(30) := '10,30' ;
  3    CURSOR Cur IS
  4    SELECT * FROM DEPT
  5    WHERE  DEPTNO IN ( SELECT * FROM TABLE( CAST ( Dynamic_In(LC$List) AS sys.dbms_debug_vc2coll ) ) ) ;
  6  BEGIN
  7    FOR C IN Cur LOOP
  8      Dbms_output.put_line( C.deptno || ' -> ' || C.Loc ) ;
  9    END LOOP ;
 10  END ;
 11 
 12  /
10 -> NEW YORK
30 -> CHICAGO

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

SQL>

In this example, the list of values for the IN clause is stored in a varchar2 variable, but it can be read from a table column as well.

You can find the source of the Split() function here

Francois

par Francois Degrelle publié dans : Oracle PL/SQL
ajouter un commentaire commentaires (3)    créer un trackback recommander
blog gratuit sur over-blog.com - Contact - C.G.U. - Rémunération en droits d'auteur avec TF1 Network - Signaler un abus