Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: UTL_FILE with parameters

    The below code snippet UTL_FILE.PUT_LINE(v_file,V_TEMPA);
    in the below BLOCK does not spool the data of the table but the columns instead as defined in variable V_TEMPA

    I want to spool dynamically for the table. Can you let me know what change is needed? Execute immediate?

    Thanks a ton


    --
    set serveroutput on
    DECLARE

    V_TABLE_NAME varchar2(60):='&Table';
    V_TEMPA varchar2(2000);
    V_TEMPB varchar2(2500);
    v_file UTL_FILE.FILE_TYPE;
    BEGIN
    v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIR',
    filename => V_TABLE_NAME||'.txt',
    open_mode => 'w',
    max_linesize => 32767);
    FOR TAB_REC IN (SELECT TABLE_NAME
    FROM ALL_TABLES
    WHERE TABLE_NAME = UPPER (V_TABLE_NAME))
    LOOP
    V_TEMPA :='';

    FOR COL_REC IN (SELECT *
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME = TAB_REC.TABLE_NAME
    ORDER BY COLUMN_ID)
    LOOP
    V_TEMPA := V_TEMPA || 'cur_rec2.'||COL_REC.COLUMN_NAME||'||''~''||';


    END LOOP;
    V_TEMPA := SUBSTR(V_TEMPA,1, LENGTH(V_TEMPA)-7);
    --V_TEMPB := 'SELECT '||V_TEMPA||' FROM '||TAB_REC.TABLE_NAME;
    DBMS_OUTPUT.PUT_LINE(V_TEMPA);
    FOR cur_rec2 IN (SELECT * FROM emp)
    loop
    UTL_FILE.PUT_LINE(v_file,V_TEMPA);
    end LOOP;
    UTL_FILE.FCLOSE(v_file);
    END loop;
    END;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to spool dynamically for the table
    I do not understand this statement.
    I do not know what procedure does now.
    I do not understand what the procedure should do differently.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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