Output is not comming as expected

Shell Programming and Scripting
1

Hi All,

I am in middle of one script. I want output in the form of xls file.

There are 4 fields - user name, email Id, full name, date of birth. I want these details to get in seperate columns.

But, i am getting it in the single cell and as like a paragraph.:mad:

Please suggest me some command to format this looks.. :confused:

Thankuz! :):slight_smile:

2

Try this.

sql - How do I spool to a CSV formatted file using SQLPLUS? - Stack Overflow

3

Post an example of the input and expected output using code tags, thanks.

1 Like
4

Write the o/p as CSV file

"col1", "col 2", "col 3"

And while opening it as spreadsheet chose proper delimiters..
Or show us what the script prints.. So that it can be corrected.

5 
#!/bin/bash
ORACLE_HOME=/app/oracle/product/10.0.0.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
x=`sqlplus -s abc_USER/password@abcd.pfizer.com <<EOFSQL
select USERNAME, EMAIL, FULL_NAME, user_bdate from sfuser where (trunc(sysdate) -trunc(Last_login))>120 and status = 'Active'
and username not in (select username from columnABC)
and ID not in (SELECT distinct created_by_id FROM audit_entry WHERE (TRUNC(SYSDATE) -TRUNC(date_created)) < 120);
EOFSQL`

echo $x>ash_t.xls
uuencode ash_t.cls ash_t.xls | mail mymailid@abc.com

===============================================================
This is the script that i worked on!
I actually want to avoid SPOOL command.

6

Environment variables are not files. Your $x is unsuitable for receiving more than one line.

Why? It is the sensible approach.
The link in post #2 has some useful tips about how to produce a correctly formatted CSV file.

Convention says use file extension ".csv" (not ".xls"). Only use ".xls" for a genuine old-format Microsoft Excel spreadsheet.

1 Like
7

Sensitive approach coz.. i tried all the possible ways to work on with spool but script iz nt getting executed..

below is the script that i tried earlier

#!/bin/bash
ORACLE_HOME=/app/oracle/product/10.0.0.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
sqlplus -s abc_USER/password@abcd.pfizer.com <<EOFSQL
set feedback off
set verify off
set pagesize 0
set term off
set termout off
set heading off
set echo off
SPOOL user.csv
select USERNAME, EMAIL, FULL_NAME, LAST_LOGIN, DATE,CREATED from sfuser where (trunc(sysdate) -trunc(Last_login))>120 and status = 'Active'
and username not in (select username from scm_authorized_keys)
and ID not in (SELECT distinct created_by_id FROM audit_entry WHERE (TRUNC(SYSDATE) -TRUNC(date_created)) < 120);
SPOOL OFF

---------- Post updated at 06:28 AM ---------- Previous update was at 06:19 AM ----------

@zaxxon :

My Input is as like i have mentioned in couple of above posts
And i am seeking out put as :

USERNAME |EMAIL |FULL_NAME | LAST_LOGIN | DATE CREATED
ABC |Abc.com |Abcdef |10th Sep'11 | 10th mar'09

| = seperator in two columns from Excel

---------- Post updated at 06:30 AM ---------- Previous update was at 06:28 AM ----------

Please find my below script using spool command and suggest me some changes..

---------- Post updated at 06:30 AM ---------- Previous update was at 06:30 AM ----------

Please find my below script using spool command and suggest me some changes..

8

I suggest create the sql file and .sh file seprated and call the sql file in .sh file and schedule in crontab for desired time , I'm using the same..

eg:-

user.sh:

ORACLE_HOME=/app/oracle/product/10.0.0.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
CDATE=`date +%d%m%Y`
export CDATE
echo $CDATE
. /home/oracle/.bash_profile
sqlplus abc_USER/password@abcd.pfizer.com  @/home/oracle/script/user.sql


user.sql:
clear breaks
clear columns
clear compute
set pagesize 50000
set NEWPAGE 1
set linesize 32767
set colsep ,
set headsep on
set serveroutput on size 1000000 format wrap
set feedback off
set underline off
SPOOL user.csv
select USERNAME, EMAIL, FULL_NAME, LAST_LOGIN, DATE,CREATED from sfuser where (trunc(sysdate) -trunc(Last_login))>120 and status = 'Active'
and username not in (select username from scm_authorized_keys)
and ID not in (SELECT distinct created_by_id FROM audit_entry WHERE (TRUNC(SYSDATE) -TRUNC(date_created)) < 120);
SPOOL OFF
9

I have given input output example below!

---------- Post updated at 06:44 AM ---------- Previous update was at 06:33 AM ----------

Could you pls temme way to create SQL file? Becoz.. whatever i am familiar with is that.. we need to install some packages before creating it.

If it is working then it would be good for me to go with the same. Becoz its just the basic script.. Going ahead.. i need to make give timer to execute this script.