formatting output from a column in sql plus

  • Permalink
  • submit to reddit
  • Email
  • Follow


I have read the documentation of the column command in sql plus version 10.1
and i still don't understand how to format the data field as follows.
I have tried different ways to use the column command, without success,
believe me...

Questions:
1) How to format a varchar2 field so that it is "right justified"?
2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
column command, if possible?

Thank you


0
Reply amwi 7/7/2004 6:52:53 AM

See related articles to this posting


"amwi" <amwi@yahoo.com> wrote in message news:<95NGc.98019$dP1.327068@newsc.telia.net>...
> I have read the documentation of the column command in sql plus version 10.1
> and i still don't understand how to format the data field as follows.
> I have tried different ways to use the column command, without success,
> believe me...
> 
> Questions:
> 1) How to format a varchar2 field so that it is "right justified"?
> 2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
> column command, if possible?
> 
> Thank you

1) select lpad(<your expression>,<max length>) ....
2) the column command supports strings and numbers. Dates are treated
as strings. You'll need to_char(<date>,'YYYY-MM-DD') in your select
statement.

Sybrand Bakker
Senior Oracle DBA
0
Reply sybrandb 7/7/2004 11:32:50 AM

"amwi" <amwi@yahoo.com> wrote in message news:<95NGc.98019$dP1.327068@newsc.telia.net>...
> I have read the documentation of the column command in sql plus version 10.1
> and i still don't understand how to format the data field as follows.
> I have tried different ways to use the column command, without success,
> believe me...
> 
> Questions:
> 1) How to format a varchar2 field so that it is "right justified"?
> 2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
> column command, if possible?
> 
> Thank you

The column command doesn't do either of those things, unfortunately
for you.
1) Use LPAD(col,<width>) in the SQL
2) Use TO_CHAR(col,'YYYY-MM-DD') in the SQL, or SET NLS_DATE_FORMAT =
'YYYY-MM-DD' if you want the same format for ALL columns.
0
Reply andrewst 7/7/2004 11:35:50 AM

"amwi" <amwi@yahoo.com> wrote in message news:<95NGc.98019$dP1.327068@newsc.telia.net>...
> I have read the documentation of the column command in sql plus version 10.1
> and i still don't understand how to format the data field as follows.
> I have tried different ways to use the column command, without success,
> believe me...
> 
> Questions:
> 1) How to format a varchar2 field so that it is "right justified"?
> 2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
> column command, if possible?
> 
> Thank you

You normally format dates in the select list of the query using the
to_char function as in to_char(date_col,'YYYY-MM-DD') as label_name

HTH -- Mark D Powell --
0
Reply Mark 7/7/2004 1:47:40 PM
comp.databases.oracle.misc 8432 articles. 2 followers. Post

3 Replies
14 Views

Similar Articles

[PageSpeed] 4


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

formatting columns in SQL*Plus
Hello everyone, I frequently want to display query results in SQL*Plus, but they come out poorly formatted because the default length of the columns causes excessive wrapping on lines. I can fix the problem to some extent by: set linesize 130 set pagesize 80 and then I can *manually* issue a bunch of format comands like: column <columnName> format a40 What I would like to be able to do is run a script that would take a table name as its input and it would detect the columns and datatypes of the table and issue the column format commands. I initially thought I c...

Format of output in Sql*plus
Hello everybody, I have a problem with the output of my query in Sql*Plus. Hopin for an answer... My output looks like: ID_XXXX SUM(XXXXXXXXXXXXXX) ------- -------------------- V I 0 4 V K 0 1 V L 0 488 The problem is it doesn't show all of ID_XXXX, which should be like VI030, VK019 and VL004. Why does it place spaces and not showing all of it? Thanks, Patrick patrick wrote: > Hello everybody, > > I have a problem with the output of my query in Sql*Plus. Hopin for an > answer... >...

formatting output with SQL*Plus
Hello, I'm hitting the old problem that I need to spool data into a text file, but SQL*Plus insists on padding the fields to the length of the column definition. The problem is that I cannot concatenate the columns as I get an "ORA-01489: result of string concatenation is too long" when doing so. What I want is a file that has the following format value1|value2|value3 but what I get is: value1 |value2 |value3 The columns are all VARCHAR2(2000) What I'm basically doing is: set term off set echo off set feedback off set heading off s...

sql*plus column format remove whitespace
Hi, Is there a column format in SQL*PLUS to trim whitespace? For eg. the following query output SQL> select owner, table_name, tablespace_name, cluster_name from all_tables where rownum < 2; OWNER TABLE_NAME ------------------------------ ------------------------------ TABLESPACE_NAME CLUSTER_NAME ------------------------------ ------------------------------ SYS UNDO$ SYSTEM SQL> should be something like this. SQL> select owner, table_name, tablespace_name, cluster_name from all_tables whe...

SQL: mapping same input column to different output columns
Using PROC SQL, I would like to map different values of the same input column to different output columns. Here is a simplified example of the problem. Suppose I have 4 tables: COUPLES (1 column called COUPLE_ID) HUSBANDS (2 columns: COUPLE_ID, BIRTH_DATE) WIVES (2 columns: COUPLE_ID, BIRTH_DATE) ZODIAC (2 columns: BIRTH_DATE, ZODIAC_SIGN) I'd like to create an output table called COUPLE_ZODIAC, containing three columns called COUPLE_ID, HUSBAND_ZODIAC_SIGN, and WIFE_ZODIAC_SIGN. What is the best way to do this? The trick (for me) is that HUSBAND_ZODIAC_SIGN and WIFE_ZODIAC_SIGN come fro...

Fast output of SQL statement to file SQL Plus
Is there a way to quickly output large number of rows from a SQL statement to a file through SQL plus. I tried using spool but that is slow. Is there another way? I am using Oracle 10g. Thanks! On Jun 13, 1:46 pm, Mensur <mmedic...@gmail.com> wrote: > Is there a way to quickly output large number of rows from a SQL > statement to a file through SQL plus. I tried using spool but that is > slow. Is there another way? I am using Oracle 10g. > > Thanks! With sqlplus, spool is it... On Wed, 13 Jun 2007 10:46:14 -0700, Mensur <mmedic123@gmail.com> wro...

Re: SQL: mapping same input column to different output columns
It's quite common to include a table more than once in a join. Just use aliases to distinguish, as in ... zodiac AS h_zodiac, zodiac AS w_zodiac ... Another possibility is to use correlated subqueries to look up the signs. On Wed, 7 Jun 2006 10:16:06 -0700, Paul <paulvonhippel@YAHOO.COM> wrote: >Using PROC SQL, I would like to map different values of the same input >column to different output columns. Here is a simplified example of the >problem. > >Suppose I have 4 tables: >COUPLES (1 column called COUPLE_ID) >HUSBANDS (2 columns: COUPLE_ID, BIRTH_DATE) &...

SQL column formating
Hi this is the first time i am using Oracle PLSQL to produce a report, i know it's not the best but that's the option i have. Date/ String fields left justify their values and number right justifies it. i have a string field : Start Date ========= 01-Jan-2001 Jan-2001 2001 May i know do i have to go to the extend using lpad to right justify this field ? Just to confirm there is no auto formating to resolve this simple alignment of field. Thank you Boon Yiang chuaby@hotmail.com wrote: > Hi > > this is the first time i am using Oracle PLSQL to produce a report, i > k...

formatted SQL output
Hi All, Is there any way to get the output of Sql Queries from Tandem through Some emulator like Sql Developer? Also, Is there any way to get the sql output through VBscript. Thanks in advance! Ricky.. Ricky wrote: > Hi All, > > Is there any way to get the output of Sql Queries from Tandem through Some emulator like Sql Developer? > > Also, Is there any way to get the sql output through VBscript. > > Thanks in advance! > Ricky.. A Google search turns up a lot of things named "Sql Developer" and I can't guess which one you are referring to. If you ...

formatting column in sql
Im tring to set a column format for a rather large column. COLUMN '<AHREF="REPORT'||SYSDATE||'.HTML#'||A.US_ID||'">' HEADING link select '<a href="report'||sysdate||'.html#'||a.us_id||'">', a.first_name||' '||a.last_name||'</a>', a.phone_area||'-'||a.phone_exchange||'-'||a.phone_line, b.vaccination_date, c.name, d.occupation_desc||'<br>' from patients a, vaccines b, organizations c, occupations d, pat_adult_info e where e.us_id = a.us_id and e.occu...

formatting columns in an awk output
I can't figure out how to force the awk output to begin in a certain column after the file name is printed by a separate column, to have a nicely aligned output. I've tried piping through fmt and sed 's/ /\t/ g', but nothing does what I want... The one-liner script that prints selected fields on the last line of each record in temp is: for i in temp/*;do print -n $i" ";tail -1 $i|awk '{printf "%2.2f %4.1f %1.3f\n", $4/3600/24, $8*1e6, $9}';done The following example illustrates the formatting problem: file000xx0.122 8.62 1318.0 4.200 fil...

sql*Plus formatting question
Platform: Oracle EE 10.2 on HPUX Given this snippet of code: set pages 999 set lines 512 set trimspool on -- spo plus.log -- select name "Name", owe||to_number(grade) "Grade", from ... where owe is varchar2(1) grade is varchar2(3) and grade is actually a 2-digit number, all of which are in the range '01' to '09' When I run the query, the length of the 'Grade' column comes out to 41 characters, left justified. I've tried bracketing the results ('['|| owe||to_number(grade)||']') to see if I'm getting ...

SQL column formating #2
Hi this is the first time i am using Oracle PLSQL to produce a report, i know it's not the best but that's the option i have. Date/ String fields left justify their values and number right justifies it. i have a string field : Start Date ========= 01-Jan-2001 Jan-2001 2001 May i know do i have to go to the extend using lpad to right justify this field ? Just to confirm there is no auto formating to resolve this simple alignment of field. Thank you Boon Yiang ...

How to format SQL-Output with HTML
Hey, I'm working on a project, programming with JSP making a connection to an oracle server and running a script which output I want to have in HTML. The Output is Line by Line, each Line looks like this: MAVINFO_NOSORBAdmin11036unknownJDBCThinClientINACTIVE that means there are no blanks between the data. The original script is following: set echo off rem rem Script: whoson.sql rem rem Purpose: Shows who is currently logged into Banner, either through rem Banner GUI (shows F45RUN.EXE or Windows 95 OraPgm) or sqlplus rem (shows sqlplus@Edison) or as a process (such as for jobsub...

Format a datetime columns output?
Hi All, Currently the query returns 2006-03-27 00:00:00, can I make it output 03/27/2006, I want to truncate the time, and replace the hyphens with forward slashes. Any ideas? Thanks In Advance, ~CK CK wrote: > Hi All, > Currently the query returns 2006-03-27 00:00:00, can I make it output > 03/27/2006, I want to truncate the time, and replace the hyphens with > forward slashes. Any ideas? > > Thanks In Advance, > ~CK SQL Server has no control over how dates are displayed. You need to fix the formatting in your client application or development environment. It's...

Column seperator in Sql/Plus
I will like to be able to seperate the columns based on a comma For example given ............. Tyler,,COMP1,prd,main.exe Smith,Smith,COMP23,dev.exe Watu,,COMP7,prde.exe ******************************** I will like to select the first column and the third column seperated by "," The query should return Tyler COMP1 Smith COMP23 Watu COMP7 Thanks in advance for your assistance TY Have you looked at the DBMS_UTILITY.TABLE_TO_COMMA stored procedure? Or, you could write a simple SQL statement as follows: SELECT column1||','||column3 FROM my_table; HTH, Brian Tyler S...

I should know this
In Unix shell (HP-UX 11i and IDS V9.31HC5) using 'output to pipe "cat" without headings'; if the result set is wider than a terminal screen's worth of characters, the output is split onto a newline, making the results unusable. e.g. dbaccess my_database <<EOF output to pipe "cat" without headings select a_char_100 from mytable EOF gives me two lines, for example: a very long string of many many characters indeed that will wrap past the end of a display screen Note that the second part ("end of a display screen" is offs...

Format the SQL-Output with HTML
Hey, I'm working on a project, programming with JSP making a connection to an oracle server and running a script which output I want to have in HTML. The Output is Line by Line, each Line looks like this: MAVINFO_NOSORBAdmin11036unknownJDBCThinClientINACTIVE that means there are no blanks between the data. The original script is following: set echo off rem rem Script: whoson.sql rem rem Purpose: Shows who is currently logged into Banner, either through rem Banner GUI (shows F45RUN.EXE or Windows 95 OraPgm) or sqlplus rem (shows sqlplus@Edison) or as a process (suc...

Formatted report in SQL* Plus
Hi, I would like to generate a formatted report in SQL* Plus as below. Can somebody please help me with the SQL statements for the same? Generate a Billing Summary Report for a Patient by name �David Blanks� with the following Format: Patient Billing Summary Patient Name: Patient ID: Encounter ID: Admission Date: Discharge Date: Charge Category Cost Room Stay Summary Charges: $ Supplies Summary Charges: $ Radiology Summary Charges: $ Laboratory Summary Charges: $ Pathology Summary Charges: $ Total Charges: $ Thanks. c...

How to print column heading only in SQL plus
Hi, I want print extra column heading in sqlplus. ( Signature ) Eg: Code List ABC Bank Code Name Amount Signature --------- ----------------------- ----------- ------------ 100 ABC 10.00 200 DEF 20.00 300 JKL 30.00 I am unable to get the above result. SQL Column c1 format a10 Heading 'Code' Column n2 format a20 Heading 'Name' Column a1 format 99.99 Heading 'Am...

formatted text output from pl/sql
Hi all, I'm looking for a replacement of dbms_output.put_line() to format my pl/sql output a little bit nicer. Actually i wonder if there is something available like a C/Perl like printf. Or a package that has a output that formats nice tables etc. ;) cu Stefan Hueneburg wrote: > Hi all, > > I'm looking for a replacement of dbms_output.put_line() to format my > pl/sql output a little bit nicer. > > Actually i wonder if there is something available like a C/Perl like > printf. Or a package that has a output that formats nice tables etc. ;) > > cu Outpu...

how to get dbms output from sql plus
Hello If i use sqlplus to run pl/sql procs. how can i get dbms output contents printed in my sqlplus window. I use windows workstatrion to connect to oracle in a unix server. can i spool the dbms output file to my windows workstation TorontoTrader wrote: > Hello > If i use sqlplus to run pl/sql procs. how can i get dbms output > contents printed in my sqlplus window. > I use windows workstatrion to connect to oracle in a unix server. can i > spool the dbms output file to my windows workstation http://www.psoug.org click on Morgan's Library click on DBMS_OUT...

Re: I should know this
Art Kagel wrote: > Andeas's script is neat, but it would be easier to use UNLOAD instead > of OUTPUT. The resulting delimited file is far easier to parse. To > directly answer your question, you cannot get dbaccess to stop > changing the output format for records wider than 80 characters. > There are 3rd party tools you can use instead of dbaccess, but if you > cannot install those, then UNLOAD is your friend. It is trivial to > write a parser and reformatter for the delimited output in awk or perl. > > Art > Yup agreed - that's what ...

Can only see 2 columns in SQL*Plus
When I write SELECT * FROM mytable; I only get the two first columns, no matter how many columns there are in the table. I can do SELECT on each or several columns in the table, but there are never more than two displayed in the output. I can't find a suitable setting to specify "all" columns. Ideas, please? Rgds Peter In article <68d3c32.0402261352.73fdadf2@posting.google.com>, deleteallspam@yahoo.com says... > When I write > SELECT * FROM mytable; > I only get the two first columns, no matter how many columns there are > in the table. I can do ...