Solved

Convert LONG to CLOB in PL/SQL. How??

Posted on 2001-08-27
4
604 Views
Last Modified: 2008-01-16
I have 16k LONG variable from a pl/sqk program and i want to Convert CLOB witin PL/SQL. How??

Please help

Raghu.Kulkarni@oracle.com
0
Comment
Question by:rpkulkar
4 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 50 total points
ID: 6429403
We have used the following sample script from Oracle. It worked for us. Hope this helps in your case.

PL/SQL Example using DBMS_LOB.LOADFROMFILE()
--------------------------------------------

The example below shows the PL/SQL version of method 3.b.i listed
above for loading a long which has been written to a flat file
named /tmp/sound_clip into a LOB column.
 
Perform the following steps to run the loadlob.sql PL/SQL script:
 
1. Create the file sound_clip with the following contents and copy
   it to the /tmp directory:
 
   sound_clip:
   -----------
   abcdefghijklmnopqrstuvwxyz
 
2. Run the following SQL script:
 
   % sqlplus scott/tiger @loadlob
 
loadlob.sql
-----------
 
set echo on;
connect sys/change_on_install;  
grant all on dbms_lob to scott;  
grant create any directory to scott;  
connect scott/tiger;
drop directory some_dir_alias;
create directory some_dir_alias as '/tmp';  
drop table multimedia;
 
/* CREATE THE TABLE */  
 
create table multimedia  
(  
   id           number,  
   video_clip   clob default empty_clob(),  
   audio_clip   clob default null,  
   some_file    bfile default null  
) ;  
 
 
/* LOAD DATA INTO THE TABLE */  
/* Insert 10 rows into the table which defaults to initializing  
 * the video_clip to empty and the audio_clip and some_file to null.  
 */  
 
/* The fast way to do this is to use array inserts with oci  
 * (see OCIBindArrayOfStruct)  
 * The not so fast way is to use a loop in plsql as follows.  
 */  
 
declare  
   loop_count integer;  
begin  
   loop_count := 1;  
   while loop_count <= 10 loop  
     insert into multimedia (id) values (loop_count);  
     loop_count := loop_count + 1;  
   end loop;  
end;  
/  
 
/* Initialize the first audio clip to the actual value.  
 * Then copy this value to all rows in the table.  
 */  
declare  
  ac      clob;  
  amount  integer;  
  a_file  bfile := BFILENAME('SOME_DIR_ALIAS', 'sound_clip');  
begin  
  update multimedia set audio_clip = empty_clob() where id = 1 returning  
        audio_clip into ac;  
 
  /* Open the server side file that contains the audio clip, load it into  
   * the clob and then close the file.  
   */  
  dbms_lob.fileopen(a_file, dbms_lob.file_readonly);  
  amount := 26;  
  /* note that the destination and source offsets default to 1 */  
  dbms_lob.loadfromfile(ac, a_file, amount);  
  dbms_lob.fileclose(a_file);  
  commit;  
 
  /* update all rows in the table to the audio clip we just loaded. */  
  update multimedia set audio_clip =  
        (select audio_clip from multimedia where id = 1)  
        where audio_clip is null;  
end;  
/  
 
select id, audio_clip from multimedia;
 
3. The output should resemble:  
 
SQL> @loadlob
SQL> set echo on;
SQL> connect sys/change_on_install;
Connected.
SQL> grant all on dbms_lob to scott;
 
Grant succeeded.
 
SQL> grant create any directory to scott;
 
Grant succeeded.
 
SQL> connect scott/tiger;
Connected.
SQL> drop directory some_dir_alias;
 
Directory dropped.
 
SQL> create directory some_dir_alias as '/tmp';
 
Directory created.
 
SQL> drop table multimedia;
 
Table dropped.
 
SQL>  
SQL> /* CREATE THE TABLE */
SQL>  
SQL> create table multimedia
  2  (
  3        id           number,
  4        video_clip   clob default empty_clob(),
  5        audio_clip   clob default null,
  6        some_file    bfile default null
  7  ) ;
 
Table created.
 
SQL>  
SQL>  
SQL> /* LOAD DATA INTO THE TABLE */
SQL> /* Insert 10 rows into the table which defaults to initializing
DOC> * the video_clip to empty and the audio_clip and some_file to null.
DOC> */
SQL>  
SQL> /* The fast way to do this is to use array inserts with oci
DOC> * (see OCIBindArrayOfStruct)
DOC> * The not so fast way is to use a loop in plsql as follows.
DOC> */
SQL>  
SQL> declare
  2        loop_count integer;
  3  begin
  4        loop_count := 1;
  5        while loop_count <= 10 loop
  6          insert into multimedia (id) values (loop_count);
  7          loop_count := loop_count + 1;
  8        end loop;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL>  
SQL>  
SQL>  
SQL> /* Initialize the first audio clip to the actual value.
DOC> * Then copy this value to all rows in the table.
DOC> */
SQL> declare
  2    ac      clob;
  3    amount  integer;
  4    a_file  bfile := BFILENAME('SOME_DIR_ALIAS', 'sound_clip');
  5  begin
  6    update multimedia set audio_clip = empty_clob() where id = 1 returning
  7             audio_clip into ac;
  8  
  8    /* Open the server side file that contains the audio clip, load it into
  9        * the clob and then close the file.  
 10        */
 11    dbms_lob.fileopen(a_file, dbms_lob.file_readonly);
 12    amount := 26;
 13    /* note that the destination and source offsets default to 1 */
 14    dbms_lob.loadfromfile(ac, a_file, amount);
 15    dbms_lob.fileclose(a_file);
 16    commit;
 17  
 18    /* update all rows in the table to the audio clip we just loaded. */
 19    update multimedia set audio_clip =
 20             (select audio_clip from multimedia where id = 1)
 21             where audio_clip is null;
 22  end;
 23  /
 
PL/SQL procedure successfully completed.
 
SQL> column id format a4
SQL> column audio_clip format a26

SQL> select id, audio_clip from multimedia;
 
  ID        AUDIO_CLIP
----        --------------------------
   1        abcdefghijklmnopqrstuvwxyz
   2        abcdefghijklmnopqrstuvwxyz                                                    
   3        abcdefghijklmnopqrstuvwxyz
   4        abcdefghijklmnopqrstuvwxyz
   5        abcdefghijklmnopqrstuvwxyz
   6        abcdefghijklmnopqrstuvwxyz
   7        abcdefghijklmnopqrstuvwxyz
   8        abcdefghijklmnopqrstuvwxyz
   9        abcdefghijklmnopqrstuvwxyz
  10        abcdefghijklmnopqrstuvwxyz

10 rows selected.
 
SQL> quit
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6430003
I don't know of a way to convert inside PL/SQL.  I think you will have to convert the data in the table to CLOB.  If you want to do this, you can covert using the TO_LOB function.  An example of how to do this follows:

------------------------------------------
Copying LONGs to LOBs
LOB datatypes (BFILE, BLOB, CLOB, and NCLOB) can provide many advantages over LONG datatypes. See Oracle8i Concepts for information about the differences between LOB and LONG datatypes.

In release 8.1, a new SQL operator, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must be the same for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data.

In the examples in the following procedure, the LONG column named LONG_COL in table LONG_TAB is copied to a LOB column named LOB_COL in table LOB_TAB. These tables include an ID column that contains identification numbers for each row in the table.

Complete the following steps to copy data from a LONG column to a LOB column:

Create a new table with the same definition as the table that contains the LONG column, but use a LOB datatype in place of the LONG datatype.

For example, suppose you have a table with the following definition:

CREATE TABLE long_tab (
    id NUMBER,
    long_col LONG);


Create a new table using the following SQL statement:

CREATE TABLE lob_tab (
    id NUMBER,
    clob_col CLOB);



--------------------------------------------------------------------------------
Note:
When you create the new table, make sure you preserve the table's schema, including integrity constraints, triggers, grants, and indexes. The TO_LOB operator only copies data; it does not preserve the table's schema.  

--------------------------------------------------------------------------------
 
 


Issue an INSERT command using the TO_LOB operator to insert the data from the table with the LONG datatype into the table with the LOB datatype.

For example, issue the following SQL statement:

INSERT INTO lob_tab
    SELECT id,
    TO_LOB(long_col)
    FROM long_tab;


When you are certain that the copy was successful, drop the table with the LONG column.

For example, issue the following SQL command to drop the LONG_TAB table:

DROP TABLE long_tab;


Create a synonym for the new table using the name of the table with LONG data. The synonym ensures that your database and applications continue to function properly.

For example, issue the following SQL statement:

CREATE SYNONYM long_tab FOR lob_tab;


Once the copy is complete, any applications that use the table must be modified to use the LOB data.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7259774

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: mathavra
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Expert Comment

by:Jgould
ID: 7289445
Question has been closed as per recommendation

JGould-EE Moderator
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now