Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13

    Unanswered: Programming tool to extract Oracle data

    Hi,

    I was wondering would anyone suggest me a tool to use to achive the following result.

    I need to extract data from multiple tables from an Oracle database, manipulate the data and write it to a text file. I need to automate this process to run twice in a month through a cron job. Which one best suite, PL-SQL, C, or just a Unix script?

    Thanks for the help

    PV

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'd use PERL!

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Ana, can Perl do all the steps in one swoop? If that is true and you know perl Pvar (or have time to learn) that sounds like a way to go. Otherwise I would go for UNIX scripts and SQL combo. You are not mentioning what kind of manipulation is needed. If it is basic you might be able to do all in SQL/PLSQL.
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Jun 2003
    Posts
    10

    Re: Programming tool to extract Oracle data

    Look at DataJunction (now pervasive) if you are in the PC environment.



    =================
    Originally posted by pvar
    Hi,

    I was wondering would anyone suggest me a tool to use to achive the following result.

    I need to extract data from multiple tables from an Oracle database, manipulate the data and write it to a text file. I need to automate this process to run twice in a month through a cron job. Which one best suite, PL-SQL, C, or just a Unix script?

    Thanks for the help

    PV

  5. #5
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21

    Re: Programming tool to extract Oracle data

    Did you get this to work? What did you finally use Perl or PL/SQL? Please let me know, I have a similar problem.

    Originally posted by pvar
    Hi,

    I was wondering would anyone suggest me a tool to use to achive the following result.

    I need to extract data from multiple tables from an Oracle database, manipulate the data and write it to a text file. I need to automate this process to run twice in a month through a cron job. Which one best suite, PL-SQL, C, or just a Unix script?

    Thanks for the help

    PV
    It pays to share!

  6. #6
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13

    Re: Programming tool to extract Oracle data

    I went with PL/SQL and the UTL_FILE procedure to access the flat file. I created a stored procedure and wrote an SQL script to run the procedure and eventually I will call that script from the cron to automate the process.

    Originally posted by daymauler
    Did you get this to work? What did you finally use Perl or PL/SQL? Please let me know, I have a similar problem.

  7. #7
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21

    Re: Programming tool to extract Oracle data

    Thanks Pvar for your prompt response. Please can you post a sample of the script.
    It pays to share!

  8. #8
    Join Date
    Jan 2004
    Posts
    492

    Re: Programming tool to extract Oracle data

    Originally posted by daymauler
    Thanks Pvar for your prompt response. Please can you post a sample of the script.

    I caught this thread a bit late, but PL/SQL and UTL_FILE is my recommendation. If you are already using Oracle, the UTL_FILE is an included package.

    As long as you have permission to write out to the Oracle Server, its very easy to do with UTL_FILE.

    Sample Script: (Have not included Exceptions, although there are some built in ones) This is off the top of my head, but think the syntax is correct.

    DECLARE
    v_filehandle utl_file.file_type;
    v_file_dir varchar2(50) := '/test/files';
    v_file_name varchar2(30) := 'test_file_1';

    Cursor c_test is
    select ename, deptno
    from emp;

    BEGIN
    -- Open File
    v_filehandle := (v_file_dir, v_file_name, 'w') -- w for writing

    For r_test in c_test
    Loop

    utl_file.put_line(v_filehandle, r_test.ename || ',' || r_test.deptno);

    end loop;

    utl_file.fclose(v_filehandle);

    END;

  9. #9
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21
    Thanks
    It pays to share!

Posting Permissions

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