Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Extracting data from tables and storing in a file
Special Forums UNIX Desktop Questions & Answers Extracting data from tables and storing in a file Post 302428334 by durden_tyler on Wednesday 9th of June 2010 01:28:46 PM
Old 06-09-2010
Oracle's sqlplus has a rich set of commands that can format your results in a large number of ways, thereby rendering "sed" or "grep" or "egrep" useless.

If you use these sqlplus commands, you don't have to do extraneous processing using Unix utilities. And you don't have to create and remove temporary files either.

Here's a short script that illustrates their usage:

Code:
test@ORA11G>
test@ORA11G> -- The data in my Oracle table looks like this
test@ORA11G> select * from t;
         X Y Z         W
---------- - --------- ----------------------------------------------------------------------------------------------------
         1 a 19-SEP-08 the quick brown fox
         2 b 19-OCT-08 @#$ jumps over the ::
         3 c 19-NOV-08 lazy dog...
         4 d 19-DEC-08 abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789~!@#$%^&*()_+{}|:"<>?`-=[]\;',./
4 rows selected.
test@ORA11G>
test@ORA11G>

And here's the Bash shell script and its execution:

Code:
$
$ # display the content of the shell script
$ cat tst.sh
#!/usr/bin/bash
sqlplus -s test/test <<EOF >outfile.log
SET NEWPAGE NONE
SET LINESIZE 9999
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SET COLSEP ","
select * from t;
exit
EOF
$
$ # source the shell script
$ . tst.sh
$
$ # check the generated flat file
$ cat outfile.log
         X,Y,Z        ,W
         1,a,19-SEP-08,the quick brown fox
         2,b,19-OCT-08,@#$ jumps over the ::
         3,c,19-NOV-08,lazy dog...
         4,d,19-DEC-08,abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789~!@#$%^&*()_+{}|:"<>?`-=[]\;',./
$
$

HTH,
tyler_durden
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies

2. UNIX for Dummies Questions & Answers

Extracting Data from a File

Hi I need to calculate the number of occurrences of a item in a number of files using Perl. The item appears continually throughout the files but in each case I only want to calculate it in certain blocks of the file. Example - Calculalte the number of occurrences of a 'pass' in a block of... (0 Replies)
Discussion started by: oop
0 Replies

3. Shell Programming and Scripting

Extracting particular string in a file and storing matched string in output file

Hi , I have input file and i want to extract below strings <msisdn xmlns="">0492001956</ msisdn> => numaber inside brackets <resCode>3000</resCode> => 3000 needs to be extracted <resMessage>Request time getBalances_PSM.c(37): d out</resMessage></ns2:getBalancesResponse> => the word... (14 Replies)
Discussion started by: sushmab82
14 Replies

4. UNIX for Dummies Questions & Answers

Storing data from a table into a csv file

Hi I need to write a bash script to take the data stored in 3 oracle tables .. and filter them and store the results in a csv file. It is an Oracle database Thank you (1 Reply)
Discussion started by: ladyAnne
1 Replies

5. Shell Programming and Scripting

Extracting data from tables......

HOw to extracts data from tables in database. Merges them into one output file. This output file is loaded into another tables in database. (1 Reply)
Discussion started by: nari.bommi
1 Replies

6. Shell Programming and Scripting

Extracting columns from a matrix and storing each column in a separate file

Hi All, I have a huge matrix file consisting some some millions rows and 6000 columns. The contents are just floating point numbers in the matrix. I want to extract each column (i.e. 6000 of them) and store each column in a separate file. For example, 1.dat will consist of elements from column... (4 Replies)
Discussion started by: shoaibjameel123
4 Replies

7. Shell Programming and Scripting

Extracting specific lines of data from a file and related lines of data based on a grep value range?

Hi, I have one file, say file 1, that has data like below where 19900107 is the date, 19900107 12 144 129 0.7380047 19900108 12 168 129 0.3149017 19900109 12 192 129 3.2766666E-02 ... (3 Replies)
Discussion started by: Wynner
3 Replies

8. UNIX for Dummies Questions & Answers

Extracting data from file

I am trying to compare the data in lines 3 & 5 to see if they match up to the '-S570' (see first code set, all proprietary information has been removed from code set) spawn telnet Trying ... Connected to CA-LOS1234-ASE-S570.cl . Escape character is '^]'. CA-LOS1234-ASE-S570 Username: ... (1 Reply)
Discussion started by: slipshft
1 Replies

9. Shell Programming and Scripting

Extracting characters and storing in some variable

eg: ./myProgram.sh filename.cpp I want to remove the :".cpp" extension from the filename.cpp expected output: filename (3 Replies)
Discussion started by: umesh314
3 Replies

10. UNIX for Beginners Questions & Answers

Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI I have a dsv file that looks like: <<BOF>> record_number|id_number|first name|last name|msisdn|network|points|card number|gender 312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M 313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M... (4 Replies)
Discussion started by: tera
4 Replies

LEARN ABOUT LINUX

dep3changelog

DEP3CHANGELOG(1)                                              General Commands Manual                                             DEP3CHANGELOG(1)

NAME

       dep3changelog - generate a changelog entry from a DEP3-style patch header

SYNOPSIS

       dep3changelog patch [patch ...] [options] [-- [dch_options]]

DESCRIPTION

       dep3changelog  extracts the DEP3 patch headers from the given patch files and builds a changelog entry for each patch.  If the patch author
       differs from the one detected from the DEBEMAIL, NAME, DEBEMAIL, or EMAIL environment variables, "Thanks to author <email>" is added to the
       changelog entry for that patch.  Any bug-debian or bug-ubuntu fields are added as "Closes" to the changelog entry.  The generated changelog
       entries are passed to debchange as an argument along with the given dch_options.

OPTIONS

       --help, -h
              Display a help message and exit successfully.

       --version
              Display version and copyright information and exit successfully.

ENVIRONMENT

       DEBEMAIL, EMAIL, DEBFULLNAME, NAME
              See the above description of the use of these environment variables.

AUTHOR

       Steve Langasek <vorlon@debian.org>

SEE ALSO

       debchange(1)

DEBIAN                                                           Debian Utilities                                                 DEP3CHANGELOG(1)
All times are GMT -4. The time now is 11:09 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy