Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

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

Partager cet article

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

commentaires