0

I want to store row results of a query in array in unix scripting.

I tried this :

array=`sqlplus -s $DB <<eof
select customer_id from customer;
eof`;

When i tried to print it , it shows me this result:

echo ${array[0]};

CUSTOMER_ID ----------- 1 2 51 52 101 102 103 104 105 106 108 11 rows selected.

But I want to store each row as an element by excluding column_name and that "11 rows selected" sentence.

Thanks in Advance.

  • By the way command is like this - array=($cmd); , anyways i have tried this also but problem with that is that it includes column name and 11 rows selected part as well. I do not want that ,I need only the rows results. –  Sep 12 '15 at 18:02
  • Can anyone tell if there is a way to assign the result of a nested sql query to a variable in BASH? A single select works (as mentioned below), but when I try select inside select, it doesn't work – Aravind Datta Feb 14 '19 at 02:36
  • `sqlplus` is not designed to be easy to use from scripts. Last time I was at an Oracle shop I built a custom tool in Python to replace it. That was the work of a single day, and it made the rest of my tenure much more pleasant; I'd recommend doing likewise. – Charles Duffy Oct 23 '21 at 01:10

1 Answers1

1

To create an array you need this syntax in BASH:

array=($(command))

or:

declare -a array=($(command))

For your sqlplus command:

array=($(sqlplus -s "$DB"<<eof
SET PAGESIZE 0;
select customer_id from customer;
eof))

and then print it as:

printf "%\n" "${array[@]}"

Just note that it is subject to all the shell expansions on white-spaces.

anubhava
  • 761,203
  • 64
  • 569
  • 643
  • As I explained in my comment above as well , I do not want column name and etc. I need only rows. –  Sep 12 '15 at 18:04
  • Skipping the first line can be done in different ways like appending `| tail +2` to your command. Similar for the last line (or use awk ). In this case you can also use grep (you only want digits). – Walter A Sep 12 '15 at 20:08
  • @AnkurVerma: There are ways to skip column names. See updated answer and please start looking into online documentation of `sqlplus`. – anubhava Sep 13 '15 at 05:26
  • @WalterA Your comment does not solve my problem. I also know how to grep it or use awk but i am looking for an answer where i will not have to use any filtering. Your answer does not help me. –  Sep 13 '15 at 13:55
  • @anubhava If I could have found out from documentation, I would have not posted it here. Thanks for trying to help. –  Sep 13 '15 at 13:57
  • 3
    See your question was **Store result of query in an array in shell scripting** which I answered correctly. Now after that was answered you asked an additional question in comments on how to skip column names. I believe my update answered that also. Please understand asking a new/additional question via comments is not usually encouraged here. – anubhava Sep 13 '15 at 14:19
  • 2
    See [http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus] or [http://stackoverflow.com/questions/2100293/clear-the-heading-in-oracle-spool] for things like `set echo off heading off feedback off pagesize 0 set und off` – Walter A Sep 13 '15 at 14:19