Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    21

    Unanswered: How do I dump LONG to a flat file?

    Using solutions that were given to me in various threads here, I was able to put together various SQL statements and an anonymous block to enable me to generate a display in TOAD that includes two longs for each row displayed.

    But when I try to export this to Excel, the LONG columns disappear.

  2. #2
    Join Date
    Aug 2003
    Posts
    21

    I found an answer.

    It can be done through MS-Access, exporting to Excel.

    If anyone can come up with a simpler way, though, I'd appreciate hearing about it.

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    since u have already got some sql statements and anonymous pl/sql block, would don't u spool it to a simple file using SQL*Plus or write it to file from pl/sql using utl_file.

    The reason why the long column gets lost using toad, is because of toad.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: I found an answer.

    Originally posted by Pithecanthropus
    It can be done through MS-Access, exporting to Excel.

    If anyone can come up with a simpler way, though, I'd appreciate hearing about it.
    Below is what I always use... HTH & YMMV!
    =====================================
    PROCEDURE dump_long (
    tab IN VARCHAR2,
    col IN VARCHAR2,
    whr IN VARCHAR2 := NULL,
    pieces IN OUT DBMS_SQL.VARCHAR2S)

    /* Requires Oracle 7.3 or above */

    IS
    cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
    fdbk PLS_INTEGER;

    TYPE long_rectype IS RECORD (
    piece_len PLS_INTEGER,
    pos_in_long PLS_INTEGER,
    one_piece VARCHAR2(255),
    one_piece_len PLS_INTEGER
    );

    rec long_rectype;
    v_message VARCHAR2(32765);
    v_crlf VARCHAR2(2) := CHR(10) || CHR(13);
    BEGIN
    DBMS_SQL.PARSE (
    cur,
    'SELECT ' || col ||
    ' FROM ' || tab ||
    ' WHERE ' || NVL (whr, '1 = 1'),
    DBMS_SQL.NATIVE);

    DBMS_SQL.DEFINE_COLUMN_LONG (cur, 1);

    fdbk := DBMS_SQL.EXECUTE (cur);

    fdbk := DBMS_SQL.FETCH_ROWS (cur);

    IF fdbk > 0
    THEN
    rec.piece_len := 255;
    rec.pos_in_long := 0;

    LOOP
    DBMS_SQL.COLUMN_VALUE_LONG (
    cur,
    1,
    rec.piece_len,
    rec.pos_in_long,
    rec.one_piece,
    rec.one_piece_len);
    EXIT WHEN rec.one_piece_len = 0;
    pieces (NVL (pieces.LAST, 0) + 1) := rec.one_piece;
    rec.pos_in_long := rec.pos_in_long + rec.one_piece_len;
    END LOOP;
    END IF;
    DBMS_SQL.CLOSE_CURSOR (cur);
    EXCEPTION
    WHEN OTHERS
    THEN
    v_Message := v_Message || 'ERROR EXTRACTING ' || WHR || V_CRLF;
    v_Message := v_Message || v_crlf|| 'SQL code = ' || SQLCODE || ' --> ' || SQLERRM;
    DBMS_OUTPUT.PUT_LINE(v_Message);
    END dump_long;

  5. #5
    Join Date
    Aug 2003
    Posts
    21
    Originally posted by evanhattem
    hi,

    since u have already got some sql statements and anonymous pl/sql block, would don't u spool it to a simple file using SQL*Plus or write it to file from pl/sql using utl_file.

    The reason why the long column gets lost using toad, is because of toad.

    Hope this helps.
    Thanks very much, this sounds good.

    I don't like UTL_FILE, but I will try using spool and vanilla sql. I assumed it wouldn't work because of what TOAD was doing.

Posting Permissions

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