1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Optimise output to file

Discussion in 'SQL PL/SQL' started by akika, May 10, 2018.

  1. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    hi,

    i ve a procedure with cursor that does a
    select unique_id from test_table;
    Loop thru the cursor and output the id in a text file using UTL_FILE.put_line .

    The table contains around 50Million records.
    Is there a way to optimise the select and utl_file ??
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    768
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    What is your oracle version?
    Describe your task more clearly: what fields, what format does the output set need, etc.
    For example, as variant's:
    1) use pl/sql collection (for batch process) and UTL_FILE
    2) use dbms_lob & pl/sql
    3) use expdp|data pump /sqlplus/sql dev, if your task/solution not needed placement on side-db
     
    Last edited: May 10, 2018
  3. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hiya,
    Using ora11g.
    Attached is sample of the table & procedure. can u pls assist?
     

    Attached Files:

  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    768
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    For example:
    1) EXTERNAL_TABLES
    Code (SQL):


    CREATE TABLE test_ext
      ORGANIZATION EXTERNAL
       (
         TYPE ORACLE_DATAPUMP
         DEFAULT DIRECTORY    HERE_YOUR_DIRECTORY
         LOCATION ('test.dmp')
       )
    AS SELECT  unique_id     FROM   tbl_test;
     
     
    Last edited: May 11, 2018
    akika likes this.