sqlplus format settings for coloumns using perl in unix

Posted on 2010-08-13
Last Modified: 2013-12-26
I wrote a pearl script which basically connects to oracle db propmts for sql query, process it
and writes the queried data (tables)to a file.

Basically if the table is small it would display normally
name   age      sex      lastname    x    y     z

but if its a big table it would go like this
name age    sex                         salary         lastname                   x            y    
           z                         f                      g                      h                             j

which is very hard to find out which data is of which field(coloumn)

i was able to format wordwrap  for coloumns if i know the coloumnname.
"coloumn coloumn_name format a4 "

The problem is , i cant make these format settings for each and evert table,

So need a common command to format the coloumns such as set colsep( ' | ');
Question by:ashokvarma06
  • 5
  • 4
  • 2
  • +1
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33432324
Try setting the LINES[IZE] along with TRIMS[POOL]

set lines 10000
set trimspool on
LVL 35

Expert Comment

by:Mark Geerlings
ID: 33432630
SQL*Plus is not designed or intended to be an easy-to-use, full-featured reporting tool.  It is a programmer's (or DBA's) commend-line tool for interacting with an Oracle database.

Yes, maybe the settings that slightwv suggested will help you if you wan to use SQL*Plus for data extracts.  You may also want some (or all) of these:
set pagesize 0   (to eliminate line feed characters after each 20 lines)
set feedback off  (to eliminate the summary record: "xxx rows selected")
set header off  (to eliminate column headings)

Author Comment

ID: 33432695
I tried those options
here is my perl script:

#! /usr/bin/perl
use Text::Wrap;  # Imports wrap()
my $connect_string = 'prism_tl/weblogic@PCRACL1';
my $out="/home/z743749/Scripts/file.txt";
my $filename="home/z743749/Scripts/SqlFile.txt";
my $sqlplus_settings = '';
print "enter the sql statement:";
$stmt = <>;
my $sql="$stmt";
print "$sql\n";
my $result = qx { sqlplus $connect_string <<EOF > $out
set colsep " | ";
set linesize 5000;
set feedback off;
#foreach my $res (@results) {
#print "$res/n";
#print $result;
#open (FILE, ">$filename") or die "Couldnt open $filename: $!";
#foreach my $line ($results) {

#   print FILE sprintf("hi \n", @$line);

print " Script Completed ....";
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33432794
>>not designed or intended to be an easy-to-use, full-featured reporting tool.

Maybe not full-featured but it is pretty powerful and you can generate some pretty nice reports with it.

Even the 11g docs still dedicate an entire chapter to it:
6 Formatting SQL*Plus Reports

>>here is my perl script:
What are you wanting to do here.  Are you wanting to create a 'delimited' list of values or something like markgeer suggested and generate a report?

Remove colsep.  I don't think it is doing what you think it is doing.

Are you sure the output will never exceed 5000 characters?

Author Comment

ID: 33432981
what i was trying was to develop a tool such as to process any query and display the tables in a seperate file,
we can use sql developer to achieve the same but instead, our whole developers work on unix box. we dont use IDE's. so we thougt of developing a pearlscript so as to connect to db process the query
and redirect the output to a file with a formatted manner by which we can identify the data by having a glance at it.

The linesize 5000 is for a single line or is it for the whole no of characters in the page, i tried many of them, they never gave me a error,though it has like 1000 lines and 30000 characters
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33433049
>>The linesize 5000 is for a single line

Yes.  Single line only.  The max allowed value is 32767.

>>display the tables in a seperate file,

I had assumed you meant the lines (single row of data) were wrapping to separate lines.  In other words, one row of actual data takes up 2+ lines in the output file.

'set lines' should solve this unless you have LOB/LONG data in the tables.


Author Comment

ID: 33433165
yes i mean its coming in two lines beacuse the screen is not fitting into those 5000 characters so it is pulling up into 2 lines

Its just the appearence that gives you 2 lines

My question was if i could word wrap those coloumns so that it would appear in a single line like

last-    | first-   | age  |  ....................................... which  would other wise look like
name    name

lastname            |  firstname                |age                              |    .............................
so as to make the row to appear in a single line

i could do this by setting formatwordwrap if i know the coloumn name,  

but as we were doing various queries i cant specify the settings for each individual coloumn,

So i want to make sure that if there is a common setting to wordwrap the coloumns without using the coloumn name or any other method to resolve this
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33433373
This is I believe where our confusion is:  

Generating a nice 'readable' report 80/132 characters wide, breaking in ID columns, wrapping 4000 character varchar2 columns to only take up 40 columns in the report is something different than eliminating wrapping lines.

Making it 'look' like one line requires the data to be on more than one line.
LVL 35

Accepted Solution

Mark Geerlings earned 500 total points
ID: 33433445
No, SQL*Plus does not have a common setting to wordwrap the columns without using the column name.  So, no, it does not offer an easy way to do this across all possible tables.

What is not clear to me is why using another layer (Perl) on top of SQL*Plus would make executing SQL queries and working with the output any easier than just doing the work directly (interactively) in SQL*Plus.

Author Comment

ID: 33433597
i am using perl because when we use sql plus the data is in assorted manner. Ultimately even perl uses sql plus to query from which we can give the format settings.

I am not sure if we can add some settings to the sqlplus.

This is the problem with apperance.....when copmared to oracle sqldeveloper or others they get the queried output in a gui. we will have the navigation bar to traverse. which were lacking in these.

Thanks for the support(SlightWV) and (Mark) . Have a bash in the weekend.



Expert Comment

ID: 33434288

Hi, to solve your task, I propose such way. For the beginning let's imagine, your Perl script accepts just table name instead of SQL query. In this case, algorithm would be:

 - query DBA_TABLES to check, that table exists;
 - query DBA_TAB_COLUMNS for list of all columns in the table;
 - dynamically create SELECT statement with all columns, every column within MAX(LENGTH ()) functions
 - in the loop, using results from previous step accordingly populate your Perl variable $SQLPLUS_SETTINGS with format options for all columns (COL COL1 FORMAT Axx, COL COL2 FORMAT Axx etc.)
 - execute SELECT from table, output will be formatted accordingly stored table’s data

So, all is simple and clear.
Note: using such approach we query table 2 times.
Thinking about same realization, but for QUERY, we face problems:
 - how to detect tables in executed query?
 - how to detect table’s columns in executed query?

As idea – we can put executed query into cursor and parse it using DBMS_SQL package. After parsing all columns will be known and we can use same approach as before – detect maximum length of every column and define accordingly COL COLx FORMAT XX statements.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33434624
I'm now on board with markgeer.  It sounds like you are trying to develop a perl based reporting tool for ANY table/column set in your Oracle schema for a fixed width report.

You might be able to take your passed in SQL and query the DBA(or USER)_TAB_COLUMNS to get column types and widths to have perl generate the necessary script to pass to another sql*plus call but that would involve A LOT of code/work.

I bet you could find a GNU reporting tool that can connect to Oracle before you could write/debug your code.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (,  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question