Go Premium for a chance to win a PS4. Enter to Win


sqlplus format settings for coloumns using perl in unix

Posted on 2010-08-13
Medium Priority
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 78

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 ....";
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 78

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 78

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 78

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 2000 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 78

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

885 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