sqlplus format settings for coloumns using perl in unix

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( ' | ');
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

set lines 10000
set trimspool on
Mark GeerlingsDatabase AdministratorCommented:
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)
ashokvarma06Author Commented:
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 ....";
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
>>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?
ashokvarma06Author Commented:
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
slightwv (䄆 Netminder) Commented:
>>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.

ashokvarma06Author Commented:
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
slightwv (䄆 Netminder) Commented:
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.
Mark GeerlingsDatabase AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ashokvarma06Author Commented:
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.



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.
slightwv (䄆 Netminder) Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.