Overblog Suivre ce blog
Administration Créer mon blog

Search

Free tool

Look and Feel Project

13 avril 2013 6 13 /04 /avril /2013 09:21

Un nouveau forum francophone vient d'être créé sur l'incontournable site de Developpez.net.

Il s'adresse aux étudiants/dévelopeurs APEX.

Pour tous les développeurs APEX de Belgique, Suisse, Canada, Luxembourg, Afrique, etc. et ceux qui causent la langue de Molière partout ailleurs, je vous invite a venir poser et répondre aux questions sur ce merveilleux outil.

Accéder au forum APEX de Developez.net

 

Francois

 

 

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
16 octobre 2012 2 16 /10 /octobre /2012 17:11

For those people who don't want to hear about anything else than PL/SQL, here is Web 2.0 framework that lets you build world class Web applications with only PL/SQL! 

Formspider apps run using 100% AJAX, DHTML and JavaScript without the need to know any of them.

 

More information on the Formspider site.

 

Francois

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
25 mars 2008 2 25 /03 /mars /2008 12:09
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.
Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
25 septembre 2007 2 25 /09 /septembre /2007 14:22

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;

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
13 août 2007 1 13 /08 /août /2007 12:10
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.
Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
9 mai 2006 2 09 /05 /mai /2006 22:09

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)
Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
25 janvier 2006 3 25 /01 /janvier /2006 21:20

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

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
18 janvier 2006 3 18 /01 /janvier /2006 12:07

I have just seen a recent Laurent Schneider's blog entry about selecting from a LONG column
http://laurentschneider.blogspot.com/2006/01/select-from-test-where-mylong-like.html.

Then i remember another question on this same forum where the guy need to read some strings stored in a LONG RAW column
(don't ask me why !)
Then i provided the following solution, a kind of LONG_RAW_TO_VARCHAR2 function

SQL*Plus: Release 9.0.1.3.0 - Production on Me Jan 18 11:56:04 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connecté à :
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL>
SQL>
SQL> CREATE TABLE TEST_LONGRAW ( num NUMBER(3), lr LONG RAW )
  2  /

Table créée.

SQL> INSERT INTO TEST_LONGRAW VALUES (1, RAWTOHEX('This is some text in a long raw column'))
  2  /

1 ligne créée.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     ch VARCHAR2(1000);
  3  BEGIN
  4    SELECT lr INTO ch FROM TEST_LONGRAW WHERE num = 1 ;
  5    dbms_output.put_line( ch ) ;
  6  END;
  7  /
5468697320697320736F6D65207465787420696E2061206C6F6E672072617720636F6C756D6E

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

SQL>

SQL> DECLARE
  2    LC$hex  VARCHAR2(4000) ;
  3    res     VARCHAR2(4000) ;
  4    j       PLS_INTEGER := 1 ;
  5    car     VARCHAR2(2) ;
  6  BEGIN
  7    SELECT lr
  8    INTO   LC$Hex
  9    FROM   TEST_LONGRAW
 10    WHERE  NUM = 1 ;
 11    FOR i IN 1 .. LENGTH( LC$hex ) / 2 LOOP
 12      car := SUBSTR( LC$hex, j, 2 ) ;
 13      res :=res || CHR(TO_NUMBER(car,'XXXXX')) ;
 14      j := j + 2 ;
 15    END LOOP ;
 16    dbms_output.put_line( res ) ;  
 17  END ;
 18  /
This is some text in a long raw column

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

SQL>

Francois

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
10 janvier 2006 2 10 /01 /janvier /2006 09:35

I have just read the Jeff Moss article concerning Hex to decimal conversion and vice versa.

In this article we can find some great conversions routines.

This is one that allows to convert hexadecimal numbers greatest than 255 to a decimal format:

CREATE OR REPLACE FUNCTION hex_to_dec ( pc$hex IN VARCHAR2 )
RETURN PLS_INTEGER
IS
  hexch  VARCHAR2(40) := UPPER( pc$hex) ;
  len PLS_INTEGER := LENGTH( hexch) ;
  res PLS_INTEGER := 0 ;
  pos PLS_INTEGER := 0 ;
  val PLS_INTEGER ;
  car VARCHAR2(1) ;
BEGIN

  FOR i IN REVERSE 1..len LOOP

    car := SUBSTR( hexch, i , 1 ) ;

 IF ASCII(car) > 57 THEN
       val := ASCII(car) - 55 ;
    ELSE
       val := ASCII(car) - 48 ;
    END IF ;

    res := res + (val * ( POWER(16,pos) ) ) ;

 pos := pos + 1 ;
 
  END LOOP ;

  RETURN( res ) ;
 
END;
/

SQL> SELECT hex_to_dec('FFFF') FROM dual
  2  /

HEX_TO_DEC('FFFF')
------------------
             65535

SQL>

Francois

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
6 décembre 2005 2 06 /12 /décembre /2005 16:57
This is a small function that allows to extract tokens from a string.

CREATE OR REPLACE FUNCTION Split
(

   PC$Chaine
IN VARCHAR2,
         -- input string
   PN$Pos
IN PLS_INTEGER,
         -- token number
   PC$Sep
IN VARCHAR2 DEFAULT ','
-- separator character
)
RETURN
VARCHAR2
IS
  LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine
;
  LI$I      PLS_INTEGER
;
  LI$I2     PLS_INTEGER
;
BEGIN
  LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos )
;
  IF LI$I > 0
THEN
    LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1)
;
    IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ; END IF
;
    RETURN( SUBSTR( LC$Chaine, LI$I+1, LI$I2 - LI$I-1 ) )
;
  ELSE
    RETURN NULL
;
  END IF
;
END;

/



SQL> DECLARE
  2    LC$String  VARCHAR2(50) := 'one|two|three|four|five|six|seven' ;
  3    LC$Token
   VARCHAR2(100) ;
  4    i          PLS_INTEGER := 1 ; 
  5  BEGIN
  6    LOOP
  7      LC$Token := Split( LC$String, i , '|') ;
  8      EXIT WHEN LC$Token IS NULL ;
  9      dbms_output.put_line( LC$Token ) ;
 10      i := i + 1 ;
 11    END LOOP ;
 12  END ;
 13 
 14  /
one
two
three
four
five
six
seven

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

SQL>

Francois

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article