Visit Our UNIX and Linux User Community


Please help: Oracle gqsql or sqlplus output format like mysql


 
Thread Tools Search this Thread
Special Forums UNIX and Linux Applications Please help: Oracle gqsql or sqlplus output format like mysql
# 1  
Old 03-08-2013
Please help: Oracle gqsql or sqlplus output format like mysql

On psql

Code:
select titolo,lingua from titolo where titolo ~*  'brivid';

Code:
 titolo  | lingua 
 ------- + ------  
 Brivido | 1

On Sqlplus/gqsql

Code:
SQL> select titolo,genere,anno,lingua  from titolo where titolo like '%rivid%';

Code:
TITOLO --------------------------------------------------------------------------------     GENERE ANNO     LINGUA ---------- ---- ---------- 
Brivido      1 1986      1

How can format the sql output on sqlplus to do something similar like mysql or psql?
Thanks
# 2  
Old 03-08-2013
If you use xigole jisql, you get the same presentation from all JDBC databases, and all JDBC drivers are available and free. There is also unixODBC isql and the various unixODBC drivers.

sqlplus: SQL*Plus Command Reference
Code:
SET COLSEP {_|text}
 
In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output 
that is rendered inside tags. HTML table output is the default. To generate preformatted output you must set 
PREFORMAT ON with the SET MARKUP HTML PREFORMAT ON command.
 
Sets the text to be printed between selected columns. If the COLSEP variable contains blanks or punctuation 
characters, you must enclose it with single quotes. The default value for text is a single space.
 
In multi-line rows, the column separator does not print between columns that begin on different lines. The 
column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the 
record separator. See SET RECSEP in this chapter for more information.
Example
 
To set the column separator to "|" enter
 
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;
 
LAST_NAME                |JOB_ID    |DEPARTMENT_ID
-------------------------|----------|-------------
Hartstein                |MK_MAN    |           20
Fay                      |MK_REP    |           20

# 3  
Old 03-08-2013
I have tried..

Code:
SQL> set COLSEP "|" 
SQL> select titolo,anno from titolo where titolo = 'Brivido';

TITOLO
--------------------------------------------------------------------------------
ANNO
----
Brivido
1986

SmilieSmilie
# 4  
Old 03-08-2013
What made you choose double quotes? In SQL they are very different in optional ways.
# 5  
Old 03-08-2013
Double quotes is just fine.

Increase your linesize to 9000 and set colsep to |
Code:
set linesize 9000 colsep |

Use SQL*Plus COLUMN format:
Code:
column titolo format a30
column anno   format a20

Run your query:
Code:
select titolo,anno from titolo where titolo = 'Brivido';

Note: Adjust column width as per your field size.
# 6  
Old 03-08-2013
Works fine,thanks
# 7  
Old 03-11-2013
Yes, sqlplus is much fussier than isql, can get into problems with big rows, manually set buffers and lacks speed. I am guessing Oracle did not want it to be friendly to scripting, and loaded it with appearance options for small scale ad hoc query. Luckily, you can jump ship to ODBC or JDBC and isql or jisql. Some like Toad, but I like SQuirreL, where skills transfer between RDBMS.

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sqlplus code format

Hi, I have some questions about sqlplus running from bash. I am still new and learning. I have the code shown below: echo "exit" | sqlplus... (10 Replies)
Discussion started by: mohca2020
10 Replies

2. Shell Programming and Scripting

Connect to Oracle using sqlplus

I have logged into oracle using SQLPLUS. When I type any kind of query, there is only 1 answer - '2'. What is wrong with it? (1 Reply)
Discussion started by: Subhasis
1 Replies

3. Shell Programming and Scripting

Need help getting my output from MYSQL query into right format

Good afternoon! I have been lurking in this forum for awhile now. I have just recently started posting. I think this is a really good site. With that being said, I don't like to just run and get an answer before I try my best first. I have poured some blood, sweat and tears into... (4 Replies)
Discussion started by: brianjb
4 Replies

4. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

5. Shell Programming and Scripting

Format the output from sqlplus while writing to log file.

Hi I have developed bash script to connect to database and execute .sql files. I am logging some statements in to log file using echo. While logging I am adding the date in front of the log statements which makes sense. I am unable to add date in front of output from the sqlplus and sqlldr,... (8 Replies)
Discussion started by: murtymvvs
8 Replies

6. Shell Programming and Scripting

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (26 Replies)
Discussion started by: shrutihardas
26 Replies

7. UNIX for Dummies Questions & Answers

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (1 Reply)
Discussion started by: shrutihardas
1 Replies

8. Shell Programming and Scripting

Formatting Oracle sqlplus output

a job extracts orcle data into unix as flat file. a single record breaks into two record in unix flat file. This is the case only for 6 records out of 60 lack records. (its not single record in two line. but its single record into record. ie., \n come into picture) can you tell me what... (6 Replies)
Discussion started by: Gopal_Engg
6 Replies

9. Shell Programming and Scripting

Help needed to format mysql output

Hi all, Does anyone know how to format the output from mysql from within a shell script? i.e. RESULT=`mysql command` echo ${RESULT} the ${RESULT} only displays the output on one line instead of how mysql would display it as columns etc (3 Replies)
Discussion started by: muay_tb
3 Replies

10. UNIX for Dummies Questions & Answers

File Format issue: Output of sqlplus

Hi, I am using a query like below in my shell script : { { echo "set echo off" echo "set head off" echo "whenever sqlerror exit -1; select NUMBER ||','|| FNAME ||','|| LOC ||','|| ... (2 Replies)
Discussion started by: deepakgang
2 Replies

Featured Tech Videos