Solved

sqlplus format settings for coloumns using perl in unix

Posted on 2010-08-13
12
808 Views
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( ' | ');
0
Comment
Question by:ashokvarma06
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 76

Expert Comment

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

set lines 10000
set trimspool on
0
 
LVL 34

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)
0
 

Author Comment

by:ashokvarma06
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
$sqlplus_settings
set colsep " | ";
set linesize 5000;
set feedback off;
#set RESCEP OFF;
#set COLOUMN FORMAT A10;
$sql
exit;
EOF
};
#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);
#}
#closeFILE;

print " Script Completed ....";
#exit;
~
0
 
LVL 76

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

http://download.oracle.com/docs/cd/E11882_01/server.112/e10823/ch_six.htm#i1081008

>>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?
0
 

Author Comment

by:ashokvarma06
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
0
 
LVL 76

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.



0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:ashokvarma06
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
0
 
LVL 76

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.
0
 
LVL 34

Accepted Solution

by:
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.
0
 

Author Comment

by:ashokvarma06
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.

Cheers...............

 
0
 

Expert Comment

by:shane54_
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.
0
 
LVL 76

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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now