Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Location
    Mumbai (India)
    Posts
    11

    Unanswered: Oracle DB Connection and spool data in text file

    Hi,

    I want to create bat file to connect oracle database with passing parameters username, password.

    After that, i have queries, so i can retrieve data and with spool command write data on txt files. name of file can be generated with user input and substitution variable.

    Simply this is the logic for import data from oracle database.
    But i want it programatically.

    Can anybody help me. I did little coding as follows and it works. Now i want introduce user input in it.
    =====================
    EXTRACT.bat as follows ...
    connect USER1/USERPASS@DBNAME
    CREATE OR REPLACE DIRECTORY UPLOAD
    AS 'C:\UPLOAD';
    GRANT READ,WRITE ON DIRECTORY UPLOAD TO user1;
    sqlplus user1/userpass@host @c:\log_dir\populate.sql


    and in populate.sql file as follows...(here i need date as user input)

    SET TERM OFF
    SET HEADING OFF
    SET HEAD ON
    SET PAGESIZE 0
    SET TRIMSPOOL ON
    SET TRIMOUT ON
    SET FEEDB OFF
    SET COLSEP "|"
    SET LINESIZE 1000
    spool C:\UPLOAD\APPU21SEP2006.TXT
    select * from tb_rule;
    spool off
    exit


    Please help..

    Vikrant Patil

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    You could use a tool you know how use, where you can accept parms & write a text file, and then create the textfile in your post dynamically, deleting it from the server when you're done running it.

    -Chuck

  3. #3
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Why are you bothering with creating a directory structure in Oracle? If you're just using sqlplus to pull some data out to a text file, it's unnecessary...

    bat:
    sqlplus user1/userpass@host @c:\log_dir\populate.sql logfile

    sql:
    spool &1

  4. #4
    Join Date
    Jan 2006
    Location
    Mumbai (India)
    Posts
    11
    Hi,

    After importing data in txt file, i have to export it in some tables. I use external tables and then manipulate data for routine use.


    Vikrant Patil

Posting Permissions

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