Hello,
Please I need your help to generate an XML file from PL/SQL script:
I have many SQL queries to execute... I would like to have all the results in the same XML file...
This my pl/sql code:
declare
xml_file utl_file.file_type;
ctx DBMS_XMLGEN.CTXHANDLE;
ctx_param DBMS_XMLGEN.CTXHANDLE;
ctx_line clob;
ctx_param_line clob;
begin
xml_file:=utl_file.fopen ('T_DIR', 'T_db.xml', 'W');
ctx := dbms_xmlgen.newContext (
'select cursor(select min(sga_size_factor) "sga_factor" ,min(sga_size)"sga_size" from v$sga_target_advice where estd_db_time =(select min(estd_db_time)
from v$sga_target_advice))as SGA,cursor(select min(pga_target_factor) "pga_factor" , min(pga_target_for_estimate/1024/1024) "pga_size" from v$pga_target_advice where estd_overalloc_count=0) as PGA from dual');
dbms_xmlgen.setrowsettag(ctx, 'Memory');
dbms_xmlgen.setrowtag(ctx, 'Advisor_Info');
ctx_line :=dbms_xmlgen.getXML (ctx);
utl_file.put_line(xml_file,ctx_line );
ctx_param := dbms_xmlgen.newContext (
'select cursor(select nvl(value,default_value) as "spfile_director" from v$parameter where name = '||chr(39)||'spfile'||chr(39)||') as "spfile",
cursor(select nvl(value,default_value) as "control_files_director" from v$parameter where name ='||chr(39)||'control_files'||chr(39)||') as "control_files",
cursor(select nvl(value,default_value) as "local_listener_director" from v$parameter where name ='||chr(39)||'local_listener'||chr(39)||') as "local_listener",
cursor(select nvl(value,default_value) as "processes_value" from v$parameter where name ='||chr(39)||'processes'||chr(39)||' ) as "processes"
from dual');
dbms_xmlgen.setrowsettag(ctx_param, 'Database');
dbms_xmlgen.setrowtag(ctx_param, 'Parameters');
resposta_param :=dbms_xmlgen.getXML (ctx_param_line );
utl_file.put_line(xml_file,ctx_param_line );
dbms_xmlgen.closeContext(ctx);
dbms_xmlgen.closeContext(ctx_param);
UTL_FILE.fclose(xml_file);
end;
I have this result :
<?xml version="1.0"?>
<Memory>
<Advisor_Info>
.....
</Advisor_Info>
</Memory>
<?xml version="1.0"?>
<Database>
<Parameters>
.....
</Parameters>
</Database>
As you see, the clause <?xml version="1.0"?> is repeted both of time in the result file...
How can I do to have just only one clause <?xml version="1.0"?> ?
Thank you