Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    4

    Unanswered: get data > 4000 bytes into a flat file using pl/sql

    Hi all,

    I am working on an application to get data older than 18 months from Oracle 8i tables into flat files.

    I am using simple sql queries along with spool to get all rows data into flat files,
    however, I am using PL/SQL for those tables whose total rowlength can be more than 4000 bytes (determined from the sum of all columns lengths). This is because in 8i, SQL*plus has a limitation of 4000 bytes.

    I have attached an autogenerated file that is used to retrieve data from one of these tables into a flat file.

    It works fine for smaller volumes of data, say, when I change the where clause from
    where months_between('13-MAY-2003 16:31:46',TO_DATE(DC_BSN,'YYYYMMDD')) > 18

    TO
    where months_between('13-MAY-2003 16:31:46',TO_DATE(DC_BSN,'YYYYMMDD')) > 18 and rownum < 10

    But when I try to do this for all rows, the query just hangs there...
    When this pl/sql script is invoked via a ksh script, an empty data file is generated.

    Could you please look at it and tell me what could possibly be wrong ?

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: get data > 4000 bytes into a flat file using pl/sql

    Originally posted by Muskaan
    Hi all,

    I am working on an application to get data older than 18 months from Oracle 8i tables into flat files.

    I am using simple sql queries along with spool to get all rows data into flat files,
    however, I am using PL/SQL for those tables whose total rowlength can be more than 4000 bytes (determined from the sum of all columns lengths). This is because in 8i, SQL*plus has a limitation of 4000 bytes.

    I have attached an autogenerated file that is used to retrieve data from one of these tables into a flat file.

    It works fine for smaller volumes of data, say, when I change the where clause from
    where months_between('13-MAY-2003 16:31:46',TO_DATE(DC_BSN,'YYYYMMDD')) > 18

    TO
    where months_between('13-MAY-2003 16:31:46',TO_DATE(DC_BSN,'YYYYMMDD')) > 18 and rownum < 10

    But when I try to do this for all rows, the query just hangs there...
    When this pl/sql script is invoked via a ksh script, an empty data file is generated.

    Could you please look at it and tell me what could possibly be wrong ?

    Thanks in advance!

    You can try to use dbms.utl_file utility to write your file

    make sur to open a directory in init.ora and make the directory accessible by oracle os user

Posting Permissions

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