[Oracle Database] how to run an external file in the specified folder without each time connection

When you are creating a tool to compare the execution plan after the release before the release, SQL to be evaluated had been divided into a plurality of external files

In writing, such as the following, I would like to run an external file each time connected in SQL * PLUS

#!/bin/bash

### CONF ###
exec_env="***"
user_id="***"
pass="***"
conn_str="***"
############

for file in `\find ./sql/release -maxdepth 1 -type f`; do
echo "Reading ${file}"
filename=`basename $file | sed 's/\.[^\.]*$//'`
Sqlplus $ {User_id}/$ {Pass} Atto $ {Conn_str} << EOF - here is called every time
spo ./plan/release/${exec_env}/${filename}.txt
@@${file}
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
spo off
exit
EOF
done

Execution result

[11:13:12 oracle@tscserver12 tool]$ ./release_out_plan.sh

Reading ./sql/release/dual2.sql

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 28 11:13:13 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Tue May 28 2019 11:12:55 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> SQL>
PL/SQL procedure successfully completed.

Explained.

SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Reading ./sql/release/dual.sql

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 28 11:13:13 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Tue May 28 2019 11:13:13 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> SQL>
PL/SQL procedure successfully completed.

Explained.

SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

1 - filter(TO_NUMBER(:NUM)=1)

14 rows selected.

SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Reading ./sql/release/dual3.sql

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 28 11:13:13 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Tue May 28 2019 11:13:13 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> SQL>
Explained.

SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Do it by passing a pipe to SQL * PLUS in advance generates a command you want to execute in the SQL * PLUS in such Chinese characters, it is possible to simultaneously run an external file in the specified folder in a single connection

release_out_plan_1.sh
 #!/bin/bash

### CONF ###
exec_env="***"
user_id="***"
pass="***"
conn_str="***"
############

sh ./release_out_plan_2.sh | sqlplus ${user_id}/${pass}@${conn_str}
exit

release_out_plan_2.sh
#!/bin/bash

### CONF ### exec_env="***" user_id="***" pass="***" conn_str="***" ############

for file in `\find ./sql/release -maxdepth 1 -type f`; do
filename=`basename $file | sed 's/\.[^\.]*$//'`
echo "set termout off"
echo "spo ./plan/release/${exec_env}/${filename}.txt"
echo "@@${file}"
echo "SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());"
echo "spo off" done

Execution result

[11:16:19 oracle@tscserver12 tool]$ ./release_out_plan_1.sh

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 28 11:16:24 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Tue May 28 2019 11:13:13 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> SQL> SQL> SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> SQL> SQL> SQL> SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

1 - filter(TO_NUMBER(:NUM)=1)

14 rows selected.

SQL> SQL> SQL> SQL> SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options