sqlplus format settings for coloumns using perl in unix

Try setting the LINES[IZE] along with TRIMS[POOL]

set lines 10000
set trimspool on

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)

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;
~

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

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

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

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

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.

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

 

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.

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.