Retrive sqldata through shell script

It could be useful get data from DB by a  bash script Linux.
Using a shell Bash is possible to create the following query:
dir_in=`sqlplus -s $PMS_CONN_STRING <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select directory_path FROM all_directories WHERE directory_name = ‘GARBAGE_SFTP_AREA’;
exit
EOF
Analyze this script.
The query above, very simple, extracts a path from the system table all_directories where directory name is GARBAGE_SFTP_AREA.
At the beginning, dir_in = indicate the variable where we’ll put the result (the value of directory_path field).
With the symbol s `COMMAND`  bash execute the command between `  and replace it with his output.
The string called $PMS_CONN_STRING is a system variable that contains user/password@dbserver  that permits to connect to database.
The part with different set indicated :
SET PAGESIZE  0
– PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
 
 
 

SET FEEDBACK  OFF

 

-- FEEDBACK = ON will count rows returned

 

SET VERIFY    OFF

 

-- VERIFY = ON will show before and after substitution variables

 

SET HEADING   OFF

 

-- HEADING = OFF will hide column headings

 

SET ECHO      ON

 

-- ECHO = ON will Display the command on screen (+ spool)

 

-- ECHO = OFF will Display the command on screen but not in spool files.

 

-- Interactive commands are always echoed to screen/spool.

 
 
 
After these rows there is the sql instruction.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *