Oracle-L: Concatenating formated number and date doesn't work

From: Abdul Aleem <dmit_at_beaconhouse.edu.pk>
Date: Mon, 25 Mar 2002 23:33:22 -0800
Message-ID: <F001.00432CEA.20020325233322@fatcity.com>

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