DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Migrating a table from Long raw to Blob

  1. #1
    Join Date
    Aug 2001
    Posts
    3
    Hello,
    I want to migrate a Long raw column to BLOB. The table is very big and so I cannot use the to_lob function since this function cannot be used in PL/SQL .
    Can somebody give me some suggestions or some scripts which does this task.

    Thanks

  2. #2
    If you have metalink have a look at the following articles:

    1012454.7

    77327.1

  3. #3
    Originally posted by birdax1
    If you have metalink have a look at the following articles:

    1012454.7

    77327.1
    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;
    .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width