How to assign select statement from Oracle to shell variable

These commands worked for me:

DB_CONNECT="XXXXX/XXXXX@XXXXX"

ID_VAL=$(sqlplus -s $DB_CONNECT <<END
set head off
set feedback off
set pagesize 2400
set linesize 2048
SELECT max (ID)+1 from apps_details;
exit;
END
)

echo $ID_VAL

Here are some alternatives, along the lines of terdon's answer, that try to make it easier to handle SQL*Plus' output given that you are trying to fetch it into the shell variable ID_VAL.

By default, SQL statements executed in SQL*Plus produce formatted reports, made of columns whose width will depend on the respective data types. This will lead to some possibly unwanted effects: for instance, the left-padded numbers you pointed out in a comment.

Demonstration:

$ DB_CONNECT="user/password@db"
$ ID_VAL="$(sqlplus -s $DB_CONNECT <<'END'
set feedback off
set head off
set pagesize 0
SELECT 123 FROM DUAL
/
END
)"
$ printf '"%s"\n' "$ID_VAL"
"       123"
$ printf '%s' "$ID_VAL" | od -An -tx1
 20 20 20 20 20 20 20 31 32 33

Some alternatives:

1. If you are selecting a number, convert it to a string and trim it in your SQL statement. With everything else in the above script unchanged (I'll keep using my dummy SELECT statement, for ease of testing):

   SELECT TRIM(TO_CHAR(123)) FROM DUAL;
   

2. Use dbms_output.put_line in a PL/SQL block:

   ID_VAL="$(sqlplus -s $DB_CONNECT <<'END'
   set serveroutput on
   set feedback off
   DECLARE
   myvar NUMBER;
   BEGIN
   SELECT 123 INTO myvar FROM DUAL;
   dbms_output.put_line(myvar);
   END;
   /
   END
   )"
   

   Note that set serveroutput on is required, no output is shown otherwise.

With both (1) and (2) you will get:

$ printf '"%s"\n' "$ID_VAL"
"123"
$ printf '%s' "$ID_VAL" | od -An -tx1
 31 32 33

Note, however, that these approaches will fail if you are selecting a string with flanking blank characters you want to preserve (e.g. foo ). You may enclose that string in quotes and remove them afterwards, or:

3. Let SQL*Plus format its output as CSV:

   ID_VAL="$(sqlplus -s -M "CSV ON" $DB_CONNECT <<'END'
   set feedback off
   set head off
   SELECT '  foo  ' FROM DUAL;
   END
   )"
   printf '"%s"\n' "$ID_VAL"
   ""  foo  ""
   

   Unlike numbers, strings will be double-quoted, requiring a bit of post-processing. Assuming that the returned value does not contain <newline> characters, one way could be to use sed to remove the starting and the ending " characters (everything else unchanged):

   ID_VAL="$(sqlplus -s -M "CSV ON" $DB_CONNECT <<'END' | sed 's/^"//; s/"$//;'
   

The best option will depend on your actual use case.