Hide SQL word in the sqlplus file

Question

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="abcd1234@gmail.com dsasd@gmail.com.com"

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

I don't see, in your code, where you are sending anything to a .txt file. Please show us ALL your code, otherwise we can't help you properly. — , Jun 20 '18 at 02:16
You may want to take a look at this. It's a few years old, but I believe things haven't changed. https://stackoverflow.com/questions/19033943/spool-command-do-not-output-sql-statement-to-file — , Jun 20 '18 at 02:33
I always have trouble remembering which SQLPlus setting controls which output. I think you may want to add `set termout off` to your script. (As general practice, you may also want to use `set verify off` when sending output to a file, but I don't think that is relevant to this example.) — Dave Costa, Jun 20 '18 at 02:38

score 3 · Answer 1 · answered Jun 20 '18 at 02:49

3

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

answered Jun 20 '18 at 02:49

Dave Costa

47,262
8
56
72

Where do i put it? – Bin Quan Jun 20 '18 at 02:54
On the SQL*Plus command line. [See (somewhat dated) docs here](https://docs.oracle.com/cd/B10501_01/server.920/a90842/ch4.htm#1005759) - I don't think much has changed. Best of luck. – Bob Jarvis - Слава Україні Jun 20 '18 at 03:07
so i just do a " sqlplus -s /nolog < – Bin Quan Jun 20 '18 at 03:13
@KaushikNayak Then where should i put it? – Bin Quan Jun 20 '18 at 06:44
@BinQuan - it doesn't go inside your .sql script file; it goes in your shell script, so `${DBpath} -s < /archive...`. (Presumably the first two lines of that are file are your credentials; if you add `/nolog` then those woudl change to an explicit `connect` command.) – Alex Poole Jun 20 '18 at 08:59

Alex Poole · Answer 2 · 2018-06-20T09:22:17.633

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.

Hide SQL word in the sqlplus file

2 Answers2