f



formatting output from a column in sql plus

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
amwi
7/7/2004 6:52:53 AM
comp.databases.oracle.misc 8435 articles. 1 followers. Post Follow

3 Replies
249 Views

Similar Articles

[PageSpeed] 31

"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
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
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
Mark
7/7/2004 1:47:40 PM
Reply:

Similar Artilces:

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... > > 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? > Try typing the following into SQL*Plus and re-run the query: column id_xxxx format a10 HTH -g where do I put it? My query is now: select id_XXXX, sum(aantal_YYYY) from ZZZ.ZZZZ group by id_XXXX where do I put it? My query is now: select id_XXXX, sum(aantal_YYYY) from ZZZ.ZZZZ group by id_XXXX What does your SQL statement look like? Cheers, Brian -- =================================================================== Brian Peasland oracle_dba@nospam.peasland.net ht...

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 could do this with PL/SQL as follows: Define a cursor: CURSOR myCur IS SELECT column_name, data_type FROM Cols WHERE table_name = UPPER( tableName ); and then loop through the result set, get the data type of each column, and then kick off the appropriate column format command. Unfortunately, it seems that one can't issue SQL*Plus commands from a PL/SQL script. So... I can try to do all the processing from SQL*Plus, but then I don't know how to capture the output from a SQL statement and loop through it to issue the column format commands... any ideas? thanks, Jeff On 22 Dec 2005 13:14:38 -0800, "Jeff Calico" <jeffCalico@hotmail.com> wrote: >Hello everyone, > >I frequently want to display query results in SQL*Plus, >but they come out poorly formatted becau...

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 set define off set timing off set linesize 32767 set pagesize 0 set colsep '|' set newpage none set trimspool on spool output.txt SELECT column1,column2, column3 FROM my_table; spool off Is there any way to remove the trailing spaces in the column values? I'm using Oracle 8.1.7.4.0 on HP/UX Any input is greatly appreciated!! Cheers Thomas On Wed, 08 Dec 2004 18:08:49 +0100, Thomas Kellerer <NNGNVRDSJEBN@spammotel.com> wrote: >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. There is a way which I've used to make the dbms_metadata.get_ddl package spool out a decent usable (as DDL SQL) piece of code, this might help you. Look for the put_line PL./SQL procedure which has a "compress" ...

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

login to SQL* Plus in Oracle 9i database
I just downloaded Oracle 9i database from Oracle's site, and I want to login SQL * Plus, but dont know the username, password, and host string?? any ideas?? thanks Matt wrote: > I just downloaded Oracle 9i database from Oracle's site, and I want to > login SQL * Plus, but dont know the username, password, and host > string?? any ideas?? > > thanks Many of them and all start with reading the documentation. If you approach Oracle like it is MS Access you are going to have a very unpleasant experience. You need to log on as SYS or SYSTEM and create a user. Then gran...

Openning/Shutting down Oracle database from the Client's SQL*Plus
Dear all, Server machine running Oracle Database Server on Linux and a Client machine running Oracle Client on WIndows XP. HOw it is possible to shut down/start up the Oracle database on the server from SQL*Plus of the client. I know it can be done from SQL*Plus of the server but the point is that I want to do it from SQL*Plus of the Client. Many many thnx for any tips, Miori Miori <cattorayye@yahoo.com> wrote in message news:<413ebeb7$0$559$e4fe514c@news.xs4all.nl>... > Dear all, > Server machine running Oracle Database Server on Linux > and a Client machine running Oracle Client on WIndows XP. > > HOw it is possible to shut down/start up the Oracle database > on the server from SQL*Plus of the client. I know it can be done from > SQL*Plus of the server but the point is that I want to do it from > SQL*Plus of the Client. > Many many thnx for any tips, > Miori Of course. it is possible. Log on to DB using SQL*Plus as sysdba and Shutdown. C:\> sqlplus /nolog SQL> connect sys/****@DB_SID as sysdba SQL> shutdown also you need set DB_SID in tnsnames.ora file on Client side. ...

oracle sql plus - modified table column
Inside of Oracle Sql *plus i use the commands alter table tablename modify columname varchar2(10) ; to change the field size to 10 When I open up Access to view the linked table via an odbc connection, in the design view, it still shows the field as having a field size of 5. What did I miss. This is all tied to a web app, where, I changed all of the web forms to accept 10 characters..... On 24.03.2009 19:20, BookerT wrote: > Inside of Oracle Sql *plus > i use the commands alter table tablename > modify columname varchar2(10) > ; > > to change the field size to 10 > > When I open up Access to view the linked table via an odbc connection, > in the design view, it still shows the field as having a field size > of 5. > > What did I miss. Probably the refresh button in Access. > This is all tied to a web app, where, I changed all of the web forms > to accept 10 characters..... You have tied MS Access into a web app which also uses Oracle? That sounds spooky. Your question sounds more suitable for a MS Access specific forum. Even there you should probably include the version of Access. Good luck! robert On Mar 24, 2:45=A0pm, Robert Klemme <shortcut...@googlemail.com> wrote: > On 24.03.2009 19:20, BookerT wrote: > > > Inside of Oracle Sql *plus > > =A0 i use the commands alter table tablename > > modify =A0columname varchar2(10) > &g...

Bare essentials to install ralce 9i client sql plus and oracle database on redhat linux
what constituents do I need to install these products on a redhat linux 9 No Spam wrote: > > what constituents do I need to install these products on a redhat linux 9 Strongly suggest you go to http://www.puschitz.com or http://www.dizwell.com and read their "how to install" documentation. Note that Red Hat 9 download is not the same content as Red Hat 9 boxed version. Some different techniques required to do the install for each variant. /Hans ...

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> 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! Put the sql in a file, the spool command prior to the select, and you won't have to scroll your output over the screen. -- Sybrand Bakker Senior Oracle DBA On Jun 13, 7:40 pm, sybra...@hccnet.nl wrote: > On Wed, 13 Jun 2007 10:46:14 -0700, 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! > > Put the sql in a file, the spool command prior to the select, and you > won't have to scroll your ou...

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

Connect to Oracle 11g using PL-SQL developer and SQL PLus
I have just installed Oracle 11g Personal Edition on my PC today. When I open PL-SqL developer, it asks me for four things:- 1. Username (oracle never asked for this in the installation) 2. Password (xyz, that I myself set) 3. Database ( Oracle created a starter database with the name of db_2 and global db with the name "orcl") 4. Connect as: Normal What do I enter in the 4 required fields. The password field is obvious and I would probably enter db_2 in the database name. But what should i enter in the username? Oracle 11g never asked for the username during the installatio...

Oracle Introduces Oracle(r) SQL Developer
Oracle Introduces Oracle(r) SQL Developer - Free, Database Development Tool REDWOOD SHORES, Calif. 13-MAR-2006 Oracle today announced the general availability of Oracle(r) SQL Developer (formerly code named Project Raptor), a new, free, database development tool. Designed for Oracle Database developers, Oracle SQL Developer simplifies development cycles and reduces the need to buy third-party tools for developing and debugging SQL and PL/SQL code. The introduction of this tool underscores Oracle's commitment to improving the productivity and supporting the needs of the database developmen...

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

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

Oracle Introduces Oracle(r) SQL Developer
Oracle Introduces Oracle(r) SQL Developer - Free, Database Development Tool Computers, REDWOOD SHORES, Calif. 13-MAR-2006 Oracle today announced the general availability of Oracle(r) SQL Developer (formerly code named Project Raptor), a new, free, database development tool. Designed for Oracle Database developers, Oracle SQL Developer simplifies development cycles and reduces the need to buy third-party tools for developing and debugging SQL and PL/SQL code. The introduction of this tool underscores Oracle's commitment to improving the productivity and supporting the needs of t...

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

difference between SQl*plus and Sql*plus worksheet !?
Hello, A Q from a newbee on oracle here. I do have access to the tool SQL*plus today, the disadvantage there is that I only have one pane/window where my Questions and Answers are seen, I have seen a tool that you have two panes / one upper and one lower - the questions are raised in the upper and answers given in the lower. What tool is that ? If I do want to create procedures, which tool should I use ? Best regards, i Following Q: Do I use SQL*Plus to write my procedures and functions ? regards, i "Inki" <inkimar_1968@hotmail.com> skrev i meddelandet n...

Converting Format Function In MS Access SQL Query To Equivalent Oracle SQL
http://www.progneer.com/wp/information_more_public.aspx?search_fd0=132498 Stop that spamming! takveen@gmail.com wrote on 10.09.2010 18:50: > http://www.progneer.com/wp/information_more_public.aspx?search_fd0=132498 ...

What Oracle SQL syntax does Oracle have that is not ANSI SQL ?
Hi I am just trying to get a feel for what oracle SQL syntax there is that would not be covered at all in anyway from a functionality perspective in the standard ANSI SQL.. Any input is appreciated.. Thank you, Jack Jack wrote: > Hi I am just trying to get a feel for what oracle SQL syntax there is > that would not be covered at all in anyway from a functionality > perspective in the standard ANSI SQL.. For the life of me, I can't remember the ANSI syntax for 'CONNECT BY'. jack_posemsky@yahoo.com (Jack) wrote in message news:<209b7e58.0405121548.48544b26@posting.google.com>... > Hi I am just trying to get a feel for what oracle SQL syntax there is > that would not be covered at all in anyway from a functionality > perspective in the standard ANSI SQL.. > > Any input is appreciated.. > > Thank you, > > Jack An appendix in the SQL Reference document contains information regarding ANSI/ISO compliance. You might wanna check that out. Also, you could test for compliance of any particular SQL statement you have by turning on FIPS flagging by using: in sqlplus: set flagger {off|entry|intermediate|full} or alter session set flagger Any non-compliant construct will raise an error when this is turned on. Hope that helps. rolympia@hotmail.com (Romeo Olympia) wrote in message news:<42fc55dc.0405130133.6401dc08@posting.google.com>... > jack_posemsky@yahoo.com (...

upsize to SQL plus split database plus change Access Forms to DAP what order?
I have a standard Access database with standard Forms and it is not split. I am going to run the upsizing wizard to change the data tables to SQL. I also need to split the database and I am going to change the Forms to be Data Access Pages so that records can be added and edited through Internet Explorer. What order should I take these steps? Can I just save the forms as Data Access Pages and they will work? I think maybe I should get all the DAP pages working correctly first. Then split, then upsize to SQL? Does this sound right? 1) DAP 2) Split 3) SQL Skip the part about Data Access...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283557612)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283557612) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. Willingness to work flexible hours, including on-call database support. Experience with N-Tier application systems using JDBC/XML database access. Scope: Database administrator will create, monitor and administer databases in Sybase and Oracle. Will perform full scope of administrative and monitoring task related to the management of the relational databases. Participate in the logical and physical design of relational databases, as required. Create scripts and routines using PERL and /or Sybperl or other scripting languages to perform admin function. Perform database performance monitoring with KM Sybase and Oracle. Maintain relational client/server databases (creating, designing, reorga...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283832411)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45283832411) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. Willingness to work flexible hours, including on-call database support. Experience with N-Tier application systems using JDBC/XML database access. Scope: Database administrator will create, monitor and administer databases in Sybase and Oracle. Will perform full scope of administrative and monitoring task related to the management of the relational databases. Participate in the logical and physical design of relational databases, as required. Create scripts and routines using PERL and /or Sybperl or other scripting languages to perform admin function. Perform database performance monitoring with KM Sybase and Oracle. Maintain relational client/server databases (creating, designing, reorga...

US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284132405)
US-TX-Austin: Database Administrator, Oracle 8i, SQL, PL/SQL, PERL, Sun Solaris (45284132405) ============================================================================================== Position: Database Administrator Reference: SMC01134 Location: Austin TX Duration: 1Y Skills: Working knowledge/experience with Oracle 8i and above supporting large production environments. Extensive knowledge of SQL, PL/SQL, PERL or other scripting experience. 2 years minimum supporting databases on Sun Solaris. Willingness to work flexible hours, including on-call database support. Experience with N-Tier application systems using JDBC/XML database access. Scope: Database administrator will create, monitor and administer databases in Sybase and Oracle. Will perform full scope of administrative and monitoring task related to the management of the relational databases. Participate in the logical and physical design of relational databases, as required. Create scripts and routines using PERL and /or Sybperl or other scripting languages to perform admin function. Perform database performance monitoring with KM Sybase and Oracle. Maintain relational client/server databases (creating, designing, reorga...

Web resources about - formatting output from a column in sql plus - comp.databases.oracle.misc

Wikipedia:Manual of Style/Text formatting - Wikipedia, the free encyclopedia
The most common use of boldface is to highlight the article title, and often synonyms, in the lead section (first paragraph). This is done for ...

Paper by 53 gains app extension, new text formatting tools on iOS
... Extension so you can easily send images and notes from other apps directly to Paper. The app has also been updated with some new text formatting ...

Formatting « Above the Law: A Legal Web Site – News, Commentary, and Opinions on Law Firms, Lawyers ...
Above the Law: A Legal Web Site – News, Commentary, and Opinions on Law Firms, Lawyers, Law School, Law Suits, Judges and Courts

Gmail v5.6 Sets The Stage For Rich Text Formatting And Integration With Calendar Events [APK Teardown ...
The app updates hit quickly yesterday as about a dozen Google apps were given new versions. Among them, Gmail made an incremental bump to v5.6. ...

Paper by FiftyThree updated with smart text formatting and share extension
... by FiftyThree has received another update, further expanding its feature set with a couple of new and noteworthy enhancements. Smart text formatting ...

WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight
Holen Sie sich „WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight“ im App Store. Sehen Sie sich Screenshots, ...

ClipboardFusion Free automatically strips formatting from clipboard text
... it into a document. But now there’s a problem. Not only have you got the text, but it’s also come with links, colors, and a host of other formatting ...

16 Tips on Blog Writing and Formatting
... again. My point is that you ought to take advice with a grain of salt. And that applies to this post as well. Writing blog posts and formatting ...

Select Text With Similar Formatting in Google Docs
Google Docs added a cool new feature that lets you select all the text with similar formatting, so you can quickly make some changes. It's like ...


Resources last updated: 2/21/2016 8:17:00 AM