Trouble formating in SQL Plus..

[FSM]

Hi guys, i'm new in oracle and my questions are:
I want to format (not the headers)but the OUTPUT of my query i've tried with "set linesize*" but it's not working for me, can I work with coordinates ?? something like Select name(x,y) last_name(x,y) from table where.......
And also, is it a good idea to work directly with sql plus, people tell me to use something else to retrieve my info, but i really want to learn to oracle so... any advise??

 

[rcurva]

You can make use of the COLUMN command in sqlplus to format your sql's columns.

example:
SQL> COLUMN DEPTNO HEADING Department
SQL> COLUMN ENAME HEADING Employee
SQL> COLUMN SAL HEADING Salary
SQL> COLUMN COMM HEADING Commission
SQL> SELECT DEPTNO, ENAME, SAL, COMM
2 FROM EMP
3 WHERE JOB = 'SALESMAN';
SQL*Plus displays the following output:

Department Employee Salary Commission
---------- ---------- ---------- ----------
30 ALLEN 1600 300
30 WARD 1250 500
30 MARTIN 1250 1400
30 TURNER 1500 0
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

In addition to Robbie's advice you may also play with FORMAT clause of column definition.
You can not work with coordinates directly, though you may force X position using different COLUMN definitions.

 

[FSM]

Thanx alot guys, but what im looking to format is the OUTPUT not the headings, So I can't do it??? what do you recomend for reports?? so the SPOOL comand is not going to work for me either??(what i mean is, i will not be able to fix it in a text file, because if its too long it would take for ever to format it)

Thanx again!!

FSM

 

[Turkbear]

Hi, for reports use a good reportwriter, but, in SqlPLus you can do some formatting with the Trim,rtrim,ltrim and concatenation operators and functions.
It depends on what you want to do..Can you post an example of what you would want it to look like?
Also, check the SqlPLus settings for feedback,pagesize,heading, etc.. to remove stuff you do not want in a spool file.

 

[FSM]

TurkBear thanks a lot for your response!! what kind of reportwriter works well with oracle??? and about what do i want to do with my reports(it's something simple)

No_emp First_name Last_name Etc
0001 John Dow etc

Instead of

No_emp First_name Last_name Etc
0001 John Dow etc etc

Do you get what i mean?? is that when do a larger query (or even one thats not so big) i get a mess that is not that easy to follow. Can you tell me how to use the "trim" function??

Thank you very much for your help turkbear

FSM

 

[Turkbear]

Hi,
I use Crystal Reports and have for sdeveral years..It seems to have the best mix of learning curve and power.

Their web site is

http://www.crystaldecisions.com

Heer is an example of trim and lpad,rpad ( which I forgot to mention)

  1  select trim(empl_nbr) ||'   '||rpad(office_nm,25,' ') || ' '||lpad(group_nm,25,'^')
  2* from hr_public where rownum < 12
SQL> /
00001018   Land Management           ^^^^^^^^^^Program Support
00004079   Audit                     ^^^Commissioner and Staff
00004417   Motor Carrier Services    ProgDel - Modal Operation
00005381   Financial Management      ^Corporate Business Group
00005648   EEO & Contract Management ^^^^Management Operations
00005782   Information Resource Mana ^^^^Management Operations
00006969   Metro - Program Delivery  ^ProgDel - Metro Division
00007138   Bridges & Structures      ^^^^^^^^^^Program Support
00008173   Metro - Traffic and Maint ^ProgDel - Metro Division
00008333   Land Management           ^^^^^^^^^^Program Support
00009939   Communications & Public R ^^^Commissioner and Staff

 

[FSM]

Thanks again turkbear!!!!!! I can't see it as well (the output) but i get your point, im going to try it (im going to look it up) Thanx!!!


FSM

 

[sem]

In fact FORMAT clause of COLUMN command formats column, not heading.

For example:
SQL> column long_text heading "Long Text" format a20 word
SQL> select 'This is very very very long text' long_text from dual;

Long Text
--------------------
This is very very
very long text

As you may see, long_text field has predefined width 20, style (word wrapping) and heading.