Kind of LONG_RAW_TO_VARCHAR2 function

18.01.2006

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

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