Displaying command return in one line

Hello all

I have a query (SQL) that returns a rather long field from an Oracle database. The field in question is defined on 400 characters but all these 400 cannot be displayed by the echo command. Thus when I launch the following command:

echo "SELECT FIELD01 FROM TABLE_NAME;" | sqlplus -s <login>/<password>@<CONNECTION_NAME>

I get the folloiwng:

https://dec.parent_groupe.com/C10/cgi-bin/cognos.cgi?b_action=cognosViewer&
ui.action=run&ui.object=storeID(%22i18DCC8B926BB446BA88844C3DFDED8A6%22)&ui.name
=Contr%c3%b4les%20ICARE&run.outputFormat=&run.prompt=true&cv.header=false&ui.bac
kURL=%2fC10%2fcps4%2fportlets%2fcommon%2fclose.html

Where as what I need is all of the above on one line.

Can anyone please help me out?

Thanks a lot

S. BASU

echo is not producing any output - sqlplus is.

Try:

sqloutput=$(echo "SELECT FIELD01 FROM TABLE_NAME;" | sqlplus -s <login>/<password>@<CONNECTION_NAME>)
echo $sqloutput

Thanks for that Carlo. I'm however stuck because echo $sqloutput doesn't return anything except a '>'

Furthermore, I can't seem to get out of that prompt

Then you might have lost a quote (or so) somewhere. I guess it's the prompt...

For your problem: can't you set the line length somewhere in sqlplus? Or column width? To 400?

As an Oracle DBA, I'd strongly recommend some changes to the way you're doing things.

don't use:

sqlplus -s <login>/<password>@<CONNECTION_NAME>

That makes your password visible to anyone logged onto the system via

ps -ef 

command

Instead ... consider doing something like:

sqlplus -s /nolog << EOF         
   connect <login>/<password>@<CONNECTION_NAME>
   @your_sql_script.sql
   exit
EOF

The advantages of using this structure over what you have are many:

1) keeps your password slightly more protected from prying eyes. it's much harder to sniff out (probably not impossible, but I'm not aware of a simple way to do it at this time).

2) you can build sql scripts in stand alone files, that actually "LOOK" like sql scripts. So they can have "set" commands at the start, var commands set if needed, parameters passed into them (using "&1", "&2", etc to catch them), and many queries if need be. Or as you need, even a simple, single SQL.

3) you can wrap unix braces around that block to capture all output in log file, variable, or whatever you need:

ie:

{
sqlplus -s /nolog << EOF         
   connect <login>/<password>@<CONNECTION_NAME>
   @your_sql_script.sql
   exit
EOF
}   > $my_log_file.log  2>&1

or you can assign it to a variable:

my_var = 
$(sqlplus -s /nolog << EOF         
   connect <login>/<password>@<CONNECTION_NAME>
   @your_sql_script.sql
   exit
EOF)

(that one's untested, sorry, I don't use that format alot myself)

but yeah, two main points are:

a) don't expose your password - use the connect keyword to delay the login and keep the password off the command line, or use External Ids, or some other login mechanism to help protect your password.
b) keep your sql in standalone sql scripts so you can easily test/modify it. Then all you need is "pass it through" to sql, via some wrapper, or such as above.

Tried setting the line size using myriad commands. However I keep getting the error message 'linesize option not a valid number' which is really weird since I am giving 400 which is definitely a valid number.

To be clear I am launching both queries together which is:

set lines 400;SELECT <column_name> FROM <table_name>;

This is because I ideally would not like to again have to create a script file (since that would mean an extra component.

Anywhere specific that I could be going wrong?

Irregardless of your final destination, you have a problem, and are troubleshooting.

Create a script file for test purposes .. and see how it behaves ...
You just might find how much easier it is to troubleshoot

Once you have things figured out, you can always splice it back into your "one-liner" solution if you need it

unix > more *
::::::::::::::
my.sql
::::::::::::::
set linesize 400
set pause off

select *
  from dual;

::::::::::::::
sql.ksh
::::::::::::::
{
sqlplus -s /nolog << EOF
   connect myid/password@dbname
   @./my.sql
   exit
EOF
}   >./my_log.log    2>&1

unix > ksh sql.ksh
unix > ls -ltr
total 6
-rw-r-----   1 ditto    group1         57 Jan 13 09:48 my.sql
-rw-r-----   1 ditto    group1        121 Jan 13 09:51 sql.ksh
-rw-r-----   1 ditto    group1         83 Jan 13 09:51 my_log.log
unix > more my_log.log

D
-
X

unix >

You can also try something like this (untested):

sqlplus -s <login>/<password>@<CONNECTION_NAME> << EOF 
set serveroutput on
set trimspool on
set feedback off 
set pagesize 0
set linesize 400
spool sqlresults.txt
SELECT <column_name> FROM <table_name>;
spool off
EOF

Again .. don't do this:

sqlplus -s <login>/<password>@<CONNECTION_NAME>

it leaves the password visible by anyone else logged into the system.

do:

sqlplus -s /nolog << EOF 
connect <login>/<password>@<CONNECTION_NAME>
set serveroutput on
set trimspool on

instead (rest of you code is fine, viable option )

Thanks for the tip Ditto! We use external authentication so we do not supply/hardcode passwords in our scripts.