[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I convert a long datatype to a clob datatype?

Posted on 2001-06-21
2
Medium Priority
?
1,374 Views
Last Modified: 2008-03-10
I have seen references for the to_lob function, but it doesn't seem to be working in my insert statement.
 I am using Oracle 8.0.5.  

table1 consists of a number field and a clob field.
table2 consists of a number field and a long field.

example: insert into table1 select numberfield, to_lob(longfield) from table2 where numberfield = 123;

Is there another way to do this without writing to a flatfile and reading into a clob?
0
Comment
Question by:swardwell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 300 total points
ID: 6215624
I beleive the TO_LOB function was made available only from Oracle 8.1.x to convert LONG and LONG RAW datatypes to CLOB and BLOB datatypes respectively. The TO_LOB function is not provided in Oracle 8.0.x.

You may have to use PL/SQL to convert it. These are from from Metalink. We have used the second one and it worked. Let me know if you need any more help.

Example #1
----------

Example #1 demonstrates how to convert a LONG column that is less
than 64k into a BLOB using PL/SQL.

-- DROP table
drop table traw;
drop table tblob
 
-- CREATE table
create table traw (n1 number , l1 long raw);
create table tblob (n1 number , l1 blob);
  --- You can use  clob as well
 
-- INSERT table
begin
  for i in 1..10 loop
    insert into traw values (i,utl_raw.cast_to_raw(rpad(to_char(i),60,'&')));
    insert into tblob values (i,empty_blob());
  end loop;
end;
/
 
declare
 lobloc blob;
 buffer long raw(32000);
 amount number ;
 offset number := 1;
begin
 for rec in (select * from traw) loop
   select l1 into lobloc from tblob where n1=rec.n1 for update;
   buffer := rec.l1;
   amount := utl_raw.length(rec.l1);
   dbms_lob.write(lobloc,utl_raw.length(rec.l1),1,buffer);
 end loop;
end;
/


Example #2
----------

The following example shows an alternate method for converting LONG
columns into BLOBs using PL/SQL.

REM long2lob.sql
REM Version 1.0, last updated 8/8/97
REM This procedure copies LONG data into a CLOB, as described in
REM Chapter 21 of _Oracle8 PL/SQL Programming_ by Scott Urman.
 
CREATE OR REPLACE PROCEDURE Long2Lob(
  -- Uses DBMS_SQL to select a LONG column identified by p_LongQuery, and
  -- returns it in p_CLob.
  p_LongQuery IN VARCHAR2,
  p_CLob IN OUT CLOB) AS
 
  c_ChunkSize CONSTANT INTEGER := 100;
 
  v_CursorID INTEGER;
  v_RC INTEGER;
  v_Chunk VARCHAR2(100);
  v_ChunkLength INTEGER;
  v_Offset INTEGER := 0;
BEGIN
  -- Open the cursor, define, execute, and fetch.
  v_CursorID := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_CursorID, p_LongQuery, DBMS_SQL.V7);
  DBMS_SQL.DEFINE_COLUMN_LONG(v_CursorID, 1);
  v_RC := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID);
 
  -- Loop over the LONG, fetching c_ChunkSize characters at a time from
  -- the LONG and adding them to the LOB.
  LOOP
    DBMS_SQL.COLUMN_VALUE_LONG(v_CursorID, 1, c_ChunkSize, v_Offset,
                          v_Chunk, v_ChunkLength);
    DBMS_LOB.WRITE(p_CLob, v_ChunkLength, v_Offset + 1, v_Chunk);
    IF v_ChunkLength < c_ChunkSize THEN
      EXIT;
    ELSE
      v_Offset := v_Offset + v_ChunkLength;
    END IF;
  END LOOP;
 
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
  WHEN OTHERS THEN
   -- Clean up, and reraise the error.
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RAISE;
END Long2Lob;
.



0
 
LVL 2

Expert Comment

by:jammalk
ID: 6215799
SQL> desc y
 Name              Null?    Type
 ----------------- -------- ------------
 NO                         NUMBER(1)
 NAME                       CLOB

SQL> desc z
 Name              Null?    Type
 ----------------- -------- ------------
 NO                         NUMBER(1)
 NAME                       LONG

Here is the procedure to accomplish this task.


CREATE OR REPLACE PROCEDURE LONG_TO_LOB IS
    Lob_loc        CLOB;
    Buffer         VARCHAR2(32767);
    Amount         BINARY_INTEGER := 32;
    Position       INTEGER := 1 ;
    i              INTEGER;
    v_err        varchar2(1000) ;
BEGIN
    /* Select the LOB: */
    SELECT name INTO Lob_loc
        FROM Y WHERE no = 1
        FOR UPDATE;

    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE) ;

    /* Fill the Buffer with data to be written. */
    SELECT name INTO buffer FROM Z WHERE no = 2 ;

    /* If the length of LONG data is more than 32767, then LOOP through to capture all the data */

--    FOR i IN 1..3
--    LOOP
   
     /* Write data: */
        DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);

--        Position := Position + Amount;
--    END LOOP;

    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);


EXCEPTION
   WHEN OTHERS THEN
      v_err := SQLERRM ;
      DBMS_OUTPUT.PUT_LINE('Operation failed');
      DBMS_OUTPUT.PUT_LINE(v_err) ;
      DBMS_LOB.CLOSE (Lob_loc);
END;
/
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

649 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