Check oracle db mode as another user faling

Question

I am trying to get the Oracle DB mode - if it is in read write mode - by executing the below code but getting an error.

I tried:

status=$(su - orasid -c "sqlplus "/as sysdba " \<\<EOF
select name,open_mode from v$database;
exit;
EOF")

echo $status

Expectation:

NAME  OPEN_MODE
-----------------------------
SMJ   READ WRITE

Result:

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 22 10:08:57 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. SQL*Plus: Release 19.0.0.0.0 - Production Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements. Usage 1: sqlplus -H | -V -H Displays the SQL*Plus version and the usage help. -V Displays the SQL*Plus version. Usage 2: sqlplus
...truncated

Answer 1

You appear to want something more like:

status=$(su - orasid -c "sqlplus -s -l / as sysdba" <<EOF
select name,open_mode from v\$database;
exit;
EOF
)

Your had extra double-quotes and escapes; but were not escaping the $ in v$database, and the closing ) needs to be on a line on its own so it doesn't confused the heredoc processing.

I've also added the -l flag so it doesn't try to log in three times if the first one fails, and -s to suppress the banner.

Depending on what you plan to do with the result you might want to suppress column heading too, and maybe only query the open_mode column.