CODEWITHSUNDEEP
oracle-databasesqlplus
Bin Quan
Bin Quan

Reputation: 9

Hide SQL word in the sqlplus file

I want to hide/remove the "SSQL> SQL> SQL> SQL> 2 3 4 5 6 7 " in the file raw.txt

my steps

sqlplus -s /nolog <<EOF
    set heading off
    set pagesize 0
    set feedback off
    set echo off
    set colsep "|"

    select 
    v_tls_equipment_logon_ct2.equipment_id,v_tls_equipment_logon_ct2.user_type_c,v_tls_equipment_logon_ct2.logon_dt,
    tls_user.user_m,tls_user.user_id,tls_user.emp_n,tls_user.org_c 
    from 
    v_tls_equipment_logon_ct2
    INNER JOIN tls_user
    ON v_tls_equipment_logon_ct2.user_id = tls_user.user_id;

sending

email="[email protected] [email protected]"

DBpath=/opt/util/sqlplus10g
interFile=/archive/equipment/EI/Jovan/dataMinerUserLogon/raw.txt
FILEPATHDATEFORMAT=`TZ=GMT-8 date +%Y%m%d%H`
FILENAME=logonDetails_${FILEPATHDATEFORMAT}.csv
FILEDIR="/archive/equipment/EI/Jovan/dataMinerUserLogon/${FILENAME}"
EMAILBODY="/archive/operations/EI/scripts/eisupport_menu/EQPT-VERSION/WOSPC/CT3/output/SystemGeneratedMessageTemplate.txt"


#Execute SQL file
${DBpath} < /archive/equipment/EI/Jovan/dataMinerUserLogon/logonuser.sql > ${interFile} 
awk 'NR > 17 { print }' < ${interFile} > ${FILEDIR}

my result

SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7  PPM417                             |2  |2018 JUN 20 09:24:31      |BALASUBRAMANIAM A/L MANICKAM                                                              |CL$BAM          |F8031299R              |PSA

Upvotes: 1

Views: 906

Answers (2)

Alex Poole
Alex Poole

Reputation: 191455

The -s flag is part of the sqlplus (or in your case, sqlplus10g) command:

Suppresses all SQL*Plus information and prompt messages, including the command prompt, the echoing of commands, and the banner normally displayed when you start SQL*Plus. If you omit username or password, SQL*Plus prompts for them, but the prompts are not visible! Use SILENT to invoke SQL*Plus within another program so that the use of SQL*Plus is invisible to the user.

That flag needs to be in your shell script where the program is invoked, not in the SQL script file:

#Execute SQL file
${DBpath} -s < /archive/equipment/EI/Jovan/dataMinerUserLogon/logonuser.sql > ${interFile}

You've also mentioned the /nolog option; that prevents SQL*Plus from promoting for credentials on startup, and you would have to connect explicitly. In an interactive session that would look like:

> sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 20 10:13:45 2018

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

SQL> connect hr/hr
Connected.
SQL>

Your current code implies the first two line of your SQL script are the username and password you want to connect as:

your_username
your_password
set heading off
set pagesize 0
set feedback off
...

If you add the /nolog option as well:

#Execute SQL file
${DBpath} -s /nolog < /archive/equipment/EI/Jovan/dataMinerUserLogon/logonuser.sql > ${interFile}

then the SQL file would have to change to:

connect your_username/your_password
set heading off
set pagesize 0
set feedback off
...

Embedding credentials in a file obviously isn't very secure either way, of course.

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48121

To suppress the SQL prompts, you should use the -silent option to the sqlplus executable. It can be abbreviated to -s.

Upvotes: 3

Related Questions