Using PL/SQL to generate Create controlfile command

Create control file script for the database is generated by the following command

SQL>alter database backup controlfile to trace;

But let us use a small PL/SQL code to generate the output of the above command . By doing this we can run the sql script from client machine and get the output without logging into the unix server.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
declare
cursor c1 is
select file_name from dba_data_files;
cursor c2 is
select a.group# grp,a.member mem,b.bytes/1024/1024 byt
from V$logfile a ,V$log b
where a.group#=b.group#
order by a.group#;
cursor c3 is
select tablespace_name,file_name,bytes/1024/1024 as tbyt
from dba_temp_files;
max_grp pls_integer;
cnt PLS_INTEGER;
val1 PLS_INTEGER;
val2 PLS_INTEGER;
val3 PLS_INTEGER;
pval varchar2(100);
name varchar2(50);
log_mode varchar2(12);
begin
select name,log_mode into name,log_mode from V$database;
select PROPERTY_VALUE into pval from database_properties
where PROPERTY_NAME='NLS_CHARACTERSET';
DBMS_OUTPUT.PUT_LINE('CREATE CONTROLFILE SET DATABASE 'name' RESETLOGS 'log_mode);
select records_total into val1 from V$controlfile_record_section where type='REDO LOG';
select records_total into val2 from V$controlfile_record_section where type='DATAFILE';
select records_total into val3 from V$controlfile_record_section where type='LOG HISTORY';
DBMS_OUTPUT.PUT_LINE('MAXLOGFILES 'val1);
DBMS_OUTPUT.PUT_LINE('MAXDATAFILES 'val2);
DBMS_OUTPUT.PUT_LINE('MAXLOGHISTORY 'val3);
DBMS_OUTPUT.PUT_LINE('LOGFILE');
select max(group#) into max_grp from V$logfile;
select count(1) into cnt from DBA_DATA_FILES;
for rec1 in c2
loop
if rec1.grp!=max_grp then
DBMS_OUTPUT.PUT_LINE('GROUP 'rec1.grp' '''rec1.mem''''' size 'rec1.byt'M,');
else
DBMS_OUTPUT.PUT_LINE('GROUP 'rec1.grp' '''rec1.mem''''' size 'rec1.byt'M');
DBMS_OUTPUT.PUT_LINE('DATAFILE');end if;end loop;
for rec in c1
loop
if c1%rowcount!=cnt then
DBMS_OUTPUT.PUT_LINE(''''rec.file_name''',');
else
DBMS_OUTPUT.PUT_LINE(''''rec.file_name'''');
end if;
end loop;
DBMS_OUTPUT.PUT_LINE('CHARACTER SET 'pval';');
for rec2 in c3
loop
dbms_output.put_line('alter tablespace ' rec2.tablespace_name' add tempfile ');
DBMS_OUTPUT.PUT_LINE(''''rec2.file_name''''' SIZE 'rec2.tbyt'M REUSE AUTOEXTEND OFF;');
end loop;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>