Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 13174 invoked from network); 2 Feb 2006 16:25:08 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 2 Feb 2006 16:25:07 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 42DE428FA53; Thu, 2 Feb 2006 17:23:03 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23418-01; Thu, 2 Feb 2006 17:23:03 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BC9F528F32E; Thu, 2 Feb 2006 17:23:02 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 02 Feb 2006 17:22:47 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C49B428F347 for ; Thu, 2 Feb 2006 17:22:47 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23291-09 for ; Thu, 2 Feb 2006 17:22:47 -0500 (EST) Received: from pd5mo1so.prod.shaw.ca (shawidc-mo1.cg.shawcable.net [24.71.223.10]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D576028F32E for ; Thu, 2 Feb 2006 17:22:46 -0500 (EST) Received: from pd4mr2so.prod.shaw.ca (pd4mr2so-qfe3.prod.shaw.ca [10.0.141.213]) by l-daemon (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IU2004YNZFC7800@l-daemon> for oracle-l@freelists.org; Thu, 02 Feb 2006 15:21:12 -0700 (MST) Received: from shaw.ca ([10.0.122.119]) by pd4mr2so.prod.shaw.ca (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IU2006YCZFC6HN0@pd4mr2so.prod.shaw.ca> for oracle-l@freelists.org; Thu, 02 Feb 2006 15:21:12 -0700 (MST) Received: from [10.0.144.80] by pd2ims1.prod.shaw.ca (mshttpd); Thu, 02 Feb 2006 15:21:12 -0700 Date: Thu, 02 Feb 2006 15:21:12 -0700 From: MARK BRINSMEAD Subject: Re: RE: SQL question To: mark.powell@eds.com Cc: johan.muller@gmail.com, oracle-l@freelists.org Message-id: <17825f4177faa9.177faa917825f4@shaw.ca> MIME-version: 1.0 Content-Type: text/plain; charset=us-ascii Content-language: en Content-disposition: inline X-Accept-Language: en Priority: normal X-archive-position: 30735 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mark.brinsmead@shaw.ca Precedence: normal Reply-To: mark.brinsmead@shaw.ca X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net Yes, it probably would. But depending on the circumstances, it might be an even better job for the initialisation parameter db_file_name_convert. Depending on what the original poster is trying to achieve (e.g., clone a devel database from production) proper use of this parameter *might* make the SQL Question unnecessary... ----- Original Message ----- From: "Powell, Mark D" Date: Thursday, February 2, 2006 2:37 pm Subject: RE: SQL question > Wouldn't that be a job for the replace function? See SQL manual > > replace(column,'target','replacement') > > HTH -- Mark D Powell -- > > > > > ________________________________ > > From: oracle-l-bounce@freelists.org > [oracle-l-bounce@freelists.org] On Behalf Of Johan Muller > Sent: Thursday, February 02, 2006 3:46 PM > To: oracle-l@freelists.org > Subject: SQL question > > > How can I use sql (decode I think) to do the following; > > select file_name from dba_data_files; > > > /u02/oradata/elfd/arch_closet_idx_01.dbf > > 21 rows selected. > > SQL> l > > Now I want to change the /elfd/ (sid) to /elfp/ in each string. > > Any idea (decode). I tried translate and replace but only > manages to return the (new) sid value of elfp. > -- > Johan Muller > Oracle DBA > (214) 676 2147 anytime. > > "Democracy is two wolves and a lamb voting on what to have for > lunch. > Liberty is a well-armed lamb contesting the vote." --Benjamin > Franklin > > > > -- http://www.freelists.org/webpage/oracle-l