Can you parametise a script ? 1

[Sonix]

I'd like to parametise a sql script in this way ...

svrmgrl @createtablespaces.sql 'c:\oracle\data\'

So if the script connects say as system then issues ...

create tablespace t1 ... <path>\data01.dbf
/
create tablespace t2 ... <path>\index01.dbf
/

it will use the path in the parameter to tell it where to create the datafiles. Is this possible?

The alternative seems to be pass the path as a parameter to a batch file. The batch file generates a new script file which you under svmgrl/sql+. To do this though, I'd have a base script which has a path placeholder in it. But does anyone know how to create a windows batch file to replace an occurance of the placeholder string with a new value?

 

[kindus]

This should do for you,

svrmgrl <!
your statements;
.....
!
All statement between ! and ! will be executed under svrmgrl.

 

[Sonix]

I want to essentially pass a parameter (the path to the datafiles) to my createtablespaces.sql script. Could you explain how I may use this feature to solve my problem?

Thanks in advance

 

[rcurva]

Yes you can pass parameters in an SQL script but usually in selects, for example:

sqlplus @test.sql 'C:\TEMP\'

Inside test.sql you can use the parameter passed as;

SELECT '&1'||'mytestfile.txt'
FROM sys.dual;

I have not done it with DDL statement so far, but there is a possibility like when you want to create a tablespace, you'll have to pass the entire dir/filename of the datafile.

example:

sqlplus @createtemptsp.sql 'C:\TEMP\temp01.dbf'

Inside createtemptsp.sql;

CREATE TABLESPACE temp
DATAFILE '&1' SIZE 100M;

 

[kindus]

Hi Sonix,
I misinterpreted you question. I thought you wanted to pass sql statements to svrmgrl prompt. Now I think I got you. The answer what you are looking is procedure with dynamic sql.

create procedure create_ts (ts varchar2, df varchar2, size_in_meg number) is
begin
execute immediate 'create tablespace '||ts||' datafile '||df||' size '||size_in_meg||'M';
end;

Hope it runs and I understood your question.