Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Connection to Oracle data and dump text file
Top Forums Shell Programming and Scripting Connection to Oracle data and dump text file Post 302779431 by durden_tyler on Tuesday 12th of March 2013 04:06:05 PM
Old 03-12-2013
Ok, how about this idea?

1) Read the file called "file_to_read", extract the data, generate the query with the "IN" operator and redirect it to a file called "file.sql"

2) Connect to sqlplus and run the file "file.sql".

That way, you do not hold the entire query in a shell variable - you save it in a file.

My dummy Oracle table called "hello" looks like this:

Code:
SQL>
SQL> select * from hello;

         X Y
---------- --------------------
         1 Str - 1
         2 Str - 2
         3 Str - 3
         4 Str - 4
         5 Str - 5
         6 Str - 6
         7 Str - 7
         8 Str - 8
         9 Str - 9
        10 Str - 10

10 rows selected.

SQL>
SQL>

On the Linux filesystem, the files look like this -

Code:
$
$
$ cat -n file_to_read
     1  00#AA#1#xx
     2  00#AA#2#xx
     3  00#AA#3#xx
     4  00#AA#4#xx
     5  00#AA#5#xx
     6  00#AA#6#xx
$
$
$ cat -n example.sh
     1  #!/usr/bin/bash
     2  (
     3    echo "select * from hello where x in ("
     4    awk -F# '{ x = NR == 1 ? "" : ","; print x, "'\''"$3"'\''" }' file_to_read
     5    echo ");"
     6  ) > file.sql
     7
     8  sqlplus -s test/test <<EOF > tramit.dat
     9  set feed off timing off
    10  @file.sql
    11  exit
    12  EOF
$
$

When the shell script "example.sh" runs, it first creates "file.sql" which is then used to query the Oracle table and redirect the output to "tramit.dat".

Code:
$
$
$ ./example.sh
$
$ cat file.sql
select * from hello where x in (
 '1'
, '2'
, '3'
, '4'
, '5'
, '6'
);
$
$
$ cat tramit.dat

         X Y
---------- --------------------
         1 Str - 1
         2 Str - 2
         3 Str - 3
         4 Str - 4
         5 Str - 5
         6 Str - 6
$
$

 

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Compile and dump errors to a text file

Hi there, I want to compile a program and dump errors to a text file. How could I do so..? Thanks for reading and thanks in advance!!! (6 Replies)
Discussion started by: starstarting
6 Replies

2. Shell Programming and Scripting

Need Shell Script to upload data from Text file to Oracle database

Hi Does any one have any idea on uploading the data using Unix Shell script from text file to Oracle database. Requirement:- 1. Need to connect to Oracle database from Unix Shell script. 2. Need to pick Text file from some location on Unix Box. 3. Need to upload the data from text file to... (6 Replies)
Discussion started by: chandrashekharj
6 Replies

3. Shell Programming and Scripting

load a data from text file into a oracle table

Hi all, I have a data like, 0,R001,2,D this wants to be loaded into a oracle database table. Pl let me know how this has to be done. Thanks in advance (2 Replies)
Discussion started by: raji35
2 Replies

4. UNIX and Linux Applications

How to import and dump file to remote Oracle server?

Hi All, I have a linux centos instance which has a dump file. I need to import the dump file to the oracle server which is located at some remote location. I have installed the oracle client on my machine and I am able to connect to the remote oracle server. Now how to import the dump to the... (3 Replies)
Discussion started by: Palak Sharma
3 Replies

5. AIX

Issue while importing Oracle Dump File on AIX 5.3

Hi All, I am facing one problem related to importing Oracle Dump file. We have two different version of AIX boxes with oracle (version 10.2.0.4.0) installed. On one AIX box (version 6.1) we are creating oracle dump file (*.dmp) using oracle exp utility and importing it onto another AIX box... (1 Reply)
Discussion started by: gunjan_thakur
1 Replies

6. Shell Programming and Scripting

Read Oracle connection details from a configuration file

Hi, Is it possible to pass oracle connection information from a configuration file and use that to connect to oracle database in my unix shell scripts. Following is the scenario: (1) I would like to save oracle connection string details in a configuration file (ex., dbconfig.txt) (2) from my... (6 Replies)
Discussion started by: sudhakaratp
6 Replies

7. Homework & Coursework Questions

Oracle dump file (del format) import into db2

1. The problem statement, all variables and given/known data: are the oracle dump files compatible to direct import into db2? I already tried many times but it always truncated results. anyone can help/ advice or suggest? 2. Relevant commands, code, scripts, algorithms: exp... (3 Replies)
Discussion started by: Sonny_103024
3 Replies

8. Red Hat

CPU Usage statistics Dump in a text file over a period of time

I am facing issue related to performance of one customized application running on RHEL 5.9. The application stalls for some unknown reason that I need to track. For that I require some tool or shell scripts that can monitor the CPU usage statistics (what we get in TOP or in more detail by other... (6 Replies)
Discussion started by: Anjan Ganguly
6 Replies

9. UNIX for Beginners Questions & Answers

How to Dump data into CSV file which is Separate by <tab>?

Dear Team, please help me to solve this problem using Linux command. I want to dump this data into an excel sheet, Suppose I have a string like: ABC PQR XYZ ASD then I expect output as a ABC XYZ PQR ASD (3 Replies)
Discussion started by: Shubham1182
3 Replies

LEARN ABOUT DEBIAN

jifty::dbi::handle::oracle

Jifty::DBI::Handle::Oracle(3pm) 			User Contributed Perl Documentation			   Jifty::DBI::Handle::Oracle(3pm)

NAME

	 Jifty::DBI::Handle::Oracle - An oracle specific Handle object

SYNOPSIS

DESCRIPTION

       This module provides a subclass of Jifty::DBI::Handle that compensates for some of the idiosyncrasies of Oracle.

METHODS

   connect PARAMHASH: Driver, Database, Host, User, Password
       Takes a paramhash and connects to your DBI datasource.

   database_version
       Returns value of ORA_OCI constant, see "Constants" in DBD::Oracle.

   insert
       Takes a table name as the first argument and assumes that the rest of the arguments are an array of key-value pairs to be inserted.

   build_dsn PARAMHASH
       Takes a bunch of parameters:

       Required: Driver, Database or Host/SID, Optional: Port and RequireSSL

       Builds a dsn suitable for an Oracle DBI connection

   blob_params column_NAME column_type
       Returns a hash ref for the bind_param call to identify BLOB types used by the current database for a particular column type.  The current
       Oracle implementation only supports ORA_CLOB types(112).

   apply_limits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
       takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE starting with FIRST_ROW;

   distinct_query STATEMENTREF
       takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.

AUTHOR

       Jesse Vincent, jesse@fsck.com

SEE ALSO

       Jifty::DBI, Jifty::DBI::Handle, DBD::Oracle

perl v5.14.2							    2011-04-26					   Jifty::DBI::Handle::Oracle(3pm)
All times are GMT -4. The time now is 08:32 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy