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 MOJAVE

parse::method::signatures::param

Parse::Method::Signatures::Param(3)			User Contributed Perl Documentation		       Parse::Method::Signatures::Param(3)

NAME

       Parse::Method::Signatures::Param - a parsed parameter from a signature

ATTRIBUTES

       All attributes of this class are read-only.

   required
       Is this parameter required (true) or optional (false)?

   sigil
       The effective sigil ('$', '@' or '%') of this parameter.

   type_constraints
	   Type: Parse::Method::Signatures::TypeConstraint

	   Predicate: has_type_constraints

       Representation of the type constraint for this parameter. Most commonly you will just call "meta_type_constraint" and not access this
       attribute directly.

   default_value
	   Type: Str

	   Predicate: has_default_value

       A string that should be eval'd or injected to get the default value for this parameter. For example:

	$name = 'bar'

       Would give a default_value of "'bar'".

   constraints
	   Type: ArrayRef[Str]

	   Predicate: has_constraints

       "where" constraints for this type. Each member of the array a the string (including enclosing braces) of the where constraint block.

   param_traits
	   Type: ArrayRef[ Tupple[Str,Str] ]

	   Predicate: has_traits

       Traits that this parameter is declared to have. For instance

	$foo does coerce

       would have a trait of

	['does', 'coerce']

METHODS

   to_string
   meta_type_constraint
       Get the Moose::Meta::TypeConstraint for this parameter. Check first that the type has a type constraint:

	$tc = $param->meta_type_constraint if $param->has_type_constraints;

SEE ALSO

       Parse::Method::Signatures.

AUTHORS

       Ash Berlin <ash@cpan.org>.

       Florian Ragwitz <rafl@debian.org>.

LICENSE

       Licensed under the same terms as Perl itself.

perl v5.18.2							    2013-02-28				       Parse::Method::Signatures::Param(3)
All times are GMT -4. The time now is 10:50 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy