Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Concatenating formated number and date doesn't work

RE: Concatenating formated number and date doesn't work

From: Lord, David - CSG <David.Lord_at_hayscsg.com>
Date: Tue, 26 Mar 2002 01:53:20 -0800
Message-ID: <F001.00432DF3.20020326015320@fatcity.com> 





Aleem



Your emp_char_id variable only has room for seven characters but the result
of to_char(12345,'0999999') is eight characters long - seven for the number
and one for the sign.  Try to_char(12345,'FM9999999') or
trim(to_char(12345,'0999999')).



HTH



David Lord



> -----Original Message-----

> From: Abdul Aleem [mailto:dmit_at_beaconhouse.edu.pk]

> Sent: 26 March 2002 09:33

> To: Multiple recipients of list ORACLE-L

> Subject: RE: Concatenating formated number and date doesn't work

> 

> 

> Thank you Catherine,

> 

> It does work on SQL>_, but in procedure it gives this error.

> 

> Any other suggestion?

> 

> TIA!


> 

> Aleem

> 

>  -----Original Message-----

> Sent:	Tuesday, March 26, 2002 1:18 PM

> To:	Multiple recipients of list ORACLE-L

> Subject:	RE: Concatenating formated number and date doesn't work

> 

> Hi Aleem,

>  

> I think it's because of the space in front. Try SELECT

> ';'||TO_CHAR(12345,'0999999')||';' from dual and you will 

> notice that there

> is a space in front.

>  

>   1* SELECT  ';'||TO_CHAR(12345,'0999999')||';' from dual

> SQL> /


>  

> ';'||TO_CH

> ----------

> ; 0012345;

>  

> The problem does not lies in TO_CHAR( SysDate, 'YYYYMMDD' ).  

> Hope it helps.

>  

> Regds,

> Catherine

> 

> -----Original Message-----

> Sent: Tuesday, March 26, 2002 3:33 PM

> To: Multiple recipients of list ORACLE-L

> 

> 

> 

> Hi,

> 

>  

> 

> In a database procedure that accepts employee_ID as numeric 

> parameter I have

> the following code that doesn't work together, it however 

> works separatly, I

> wonder why?

> 

>  

> 

> PROCEDURE abc ( emp_ID IN NUMBER := 12345 )

> 

> AS

> 

>   Emp_Char_ID VARCHAR2(7);

> 

>   Bar_Char_Date VARCHAR2(8);

> 

>   Emp_Bar_Code VARCHAR2(15);

> 

> BEGIN


> 

>   Emp_Char_ID := TO_CHAR( emp_id, '0999999' ) 

> 

>   Bar_Char_Date := TO_CHAR( SysDate, 'YYYYMMDD' );

> 

>   Emp_bar_code :=  Emp_Char_ID || Bar_Char_Date;

> 

>   /* The concatenation line compiles fine, but at execution time gives

> error:  ORA-06502: PL/SQL: numeric or value error  */

> 

>   UPDATE Employee SET Bar_Code = Emp_Bar_Code WHERE 

> Employee_ID = emp_id;

> 

> END;


> 

>  

> 

> The same procedure works fine after the following modifications

> 

> Removed format mask on emp_id (First line of the BEGIN section)

> 

> Modified the update statement and used LPAD( Emp_Bar_Code, 15, '0')

> 

> The question is why the formatted number conversion gives error in

> concatenation with date converted to character.

> 

>  

> 

> TIA!


> 

>  

> 

> Aleem

> 

>  

> 

> -----Original Message-----

> Sent: Tuesday, March 26, 2002 9:48 AM

> To: Multiple recipients of list ORACLE-L

> 

>  

> 

> 

> I suspect that you need a new version of Veritas Volume 

> manager, I would

> check around the Veritas website for info. or there is a 

> driver you need to

> load in the boot of the installation  process. 

> If you are still mystified try asking your question over on 

> the sunmangers

> list. www.sunmanagers.org 

> 

> you almost certainly will find an answer there 

> 

> 

> Cheers 

> 

> 

> 

> --

> =================================================

> Peter McLarty               E-mail: Peter.Mclarty_at_mincom.com

> Technical Consultant        WWW: http://www.mincom.com

> APAC Technical Services     Phone: +61 (0)7 3303 3461

> Brisbane,  Australia        Mobile: +61 (0)402 094 238

>                            Facsimile: +61 (0)7 3303 3048

> =================================================

> A great pleasure in life is doing what people say you cannot do.

> 

>    - Walter Bagehot (1826-1877 British Economist)

> =================================================

> Mincom "The People, The Experience, The Vision"

> 

> =================================================

> 

> 

> 

> 

> 

> 

> 

> 

> 

>  

> 

> "CHAN Chor Ling Catherine (CSC)" <clchan_at_nie.edu.sg> 

> Sent by: root_at_fatcity.com 

> 

> 26/03/2002 11:33 AM 

> Please respond to ORACLE-L 

> 

>         

>         To:        Multiple recipients of list ORACLE-L

> <ORACLE-L_at_fatcity.com> 

>         cc:         

>         Fax to:         

>         Subject:        OT : Upgrade Unix OS with Veritas 

> Volumn Manager

> 

> 

> 

> 

> Hi Unix Gurus,

> 

> My UNIX administrator has to upgrade the OS from 5.6 to 5.8.  

> We are using

> Veritas Volumn Manager.  The upgrade fails because we are not 

> familiar with

> Veritas Volumn Manager. Has anyone upgraded SUN-Solaris Unix 

> OS from 5.6 to

> 5.8 with Veritas Volumn Manager ? Please help. Thanks.

> 

> Regds,

> New Bee

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> -- 

> Author: CHAN Chor Ling Catherine (CSC)

>  INET: clchan_at_nie.edu.sg

> 

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

> 

> 

> 

> 

> -- 

> This transmission is for the intended addressee only and is 

> confidential

> information. If you have received this transmission in error, 

> please delete

> it and notify the sender. The contents of this e-mail are the 

> opinion of the

> writer only and are not endorsed by the Mincom Group of 

> companies unless

> expressly stated otherwise.

> 

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> -- 

> Author: CHAN Chor Ling Catherine (CSC)

>   INET: clchan_at_nie.edu.sg

> 

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> -- 

> Author: Abdul Aleem

>   INET: dmit_at_beaconhouse.edu.pk

> 

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

> 







This message (including any attachments) is confidential and may be 
legally privileged.  If you are not the intended recipient, you should 
not disclose, copy or use any part of it - please delete all copies 
immediately and notify the Hays Group Email Helpdesk at
email.helpdesk_at_hays.plc.uk


Any information, statements or opinions contained in this message
(including any attachments) are given by the author.  They are not 
given on behalf of Hays unless subsequently confirmed by an individual
other than the author who is duly authorised to represent Hays.
 


A member of the Hays plc group of companies.
Hays plc is registered in England and Wales number 2150950.
Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lord, David - CSG
  INET: David.Lord_at_hayscsg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Tue Mar 26 2002 - 03:53:20 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US