RE: help dynamically adding a 'datafile' to a script

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 20 Feb 2004 13:23:56 -0500
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AA03@USAHM018.amer.corp.eds.com>

I only partially understand what you are trying to do but the string concatenation is wrong. You need two separate quoted strings:

SQL> select '&cPath'||' mytablespace.dbf' 2 from dual
3 /
Enter value for cpath: xxx
old 1: select '&cPath'||' mytablespace.dbf' new 1: select 'xxx'||' mytablespace.dbf'

'XXX'||'MYTABLESPACE

xxx mytablespace.dbf

I think what you really want to do is write a select to generate the create tablespace and spool that to a file where it can be called passing positional parameters

SQL> set define off
SQL> 
SQL> select 'create tablespace &1 '||
  2         'datafile '||'&2'||'&1'||' size 5M;'

3 from dual
4 /

'CREATETABLESPACE&1'||'DATAFILE'||'&2'||'&1

create tablespace &1 datafile &2&1 size 5M;

then you would issue start cretblspc mytablespace 'C:\\xxxxxxx\'. Some minor coding may be required along with a fair number of SQLPlus set statements: feedback, linesize, pagesize, etc...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of ryan.gaffuri_at_cox.net Sent: Friday, February 20, 2004 12:30 PM To: oracle-l_at_freelists.org
Subject: help dynamically adding a 'datafile' to a script

I am trying to do the following and I cant get it to take(I would prefer not to use dynamic sql and write my create tablespace scripts to a second file).

Prompt Please enter a datafile path
Accept cPath

create tablespace myTablespace datafile '&cPath ||mytablespace.dbf' size 5m;

I've tried lots of methods including bind variables and appending before the create tablespace statement. Any way to do this?

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

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


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Feb 20 2004 - 15:18:00 CST